日期:2014-05-16  浏览次数:20457 次

如何处理undo tablespace 表空间太大的问题
如何处理undo tablespace 表空间太大的问题
 (1)-- 创建一个新的小空间的undo tablespace
 create undo tablespace undotBS4 datafile 'C:\oracle\oradata\dzq\eoffice\UNDOTBS4.DBF' size 500m;
 (2)-- 设置新的表空间为系统undo_tablespace
 alter system set undo_tablespace=undotBS4;
 (3)-- Drop 旧的表空间
 drop tablespace undotbs3 including contents;
==================================================================
-查看表空间明称
select name from v$tablespace;
- 检查数据库UNDO表空间占用空间情况以及数据文件存放位置
select file_name,bytes/1024/1024 from dba_data_files  where tablespace_name like 'UNDOTBS2';
-查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)
select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;
-创建新的UNDO表空间,并设置自动扩展参数
create undo tablespace undotbs1 datafile '/oradata/oradata/ddptest/UNDOTBS1.dbf' size 1000m reuse autoextend on next 800m maxsize unlimited;
-查看每十分钟记录一次这段时间内使用的undo block数量
select begin_time,end_time, undoblks from v$undosta
 
 
 
http://blog.csdn.net/robinson_0612/article/details/6041207
 
 
 

Oracle审计表AUD$数据过大问题

How to truncate or delete rows from audit trail table sys.aud$

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.
-SYS user.
-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)
-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.

2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.
SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$;
Now export the table as,
SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp

3)To delete all records from audit trail table SYS.AUD$ issue,
SQL>DELETE FROM SYS.AUD$;

To delete all records of particular audited table from the audit trail issue,
SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae';

But deleting in this way will not reduce size on the system tablespace or aud$ table. In order t