如何避免undo tablespace 表空间太大的有关问题

如何处理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   

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 to reduce size follow section 4.

4)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE SYS.AUD$

 

 在ITPUB上有朋友遇到SYSTEM表空间快速扩展的问题

系统表空间异常扩展的情况遇到过很多:
有的和用户表空间或对象分配不当有关
有的和高级复制的空间使用有关....

经过如下代码查询，可以找出系统表空间中占用空间最多的Top9对象：

col segment_name for a25col owner for a10SELECT *  FROM (SELECT   BYTES, segment_name, segment_type, owner            FROM dba_segments           WHERE tablespace_name = 'SYSTEM'        ORDER BY BYTES DESC) WHERE ROWNUM < 10/

这个朋友的Top9对象为:

1    3082174464  IDL_UB1$TABLESYS2    63979520      SOURCE$TABLESYS3    12075008      IDL_UB2$TABLESYS4    7749632      DEPENDENCY$TABLESYS5    7356416      I_DEPENDENCY2INDEXSYS6    6438912      I_DEPENDENCY1INDEXSYS7    5521408      I_IDL_UB11  INDEXSYS8    4341760      IDL_SB4$TABLESYS9    3555328      I_ACCESS1  INDEXSYS

我们注意到占用空间最大的对象是IDL_UB1$系统表，空间占用近3G，那么这个表是做什么用的呢?
从sql.bsq中我们可以找到这个表的创建语句:

create table idl_ub1$                            /* idl table for ub1 pieces */( obj#          number not null,                            /* object number */  part          number not null,         /* part: 0 = diana, 1 = portable pcode, 2 = machine-dependent pcode */  version       number,                                    /* version number */  piece#        number not null,                             /* piece number */  length        number not null,                             /* piece length */  piece         long raw not null)                              /* ub1 piece */  storage (initial 10k next 100k maxextents unlimited pctincrease 0)/

idl_ub1$表是用来存储PL/SQL的代码单元的，包括DIANA等，IDL在这里代表Interface Definition Language.
这个对象的含义可以从Ixora找到一点提示:

It is an intermediate language in which the structure of database tables and the logic of PL/SQL program units can be consistently represented as attributed trees. Oracle uses the DIANA IDL, which comes from compilers for the Ada programming language. DIANA stands for Descriptive Intermediate Attributed Notation for Ada. Anyway, this is one of four tables in the data dictionary used to store the DIANA for PL/SQL program units, and the database objects that they reference.

在高级复制中会用到这个表，所以可能导致这个表快速增长，在Oracle10g之前，高级复制需要考虑的事情的确很多。