C_OBJ#_INTCOL#

C_OBJ#_INTCOL#对象占用几个G的空间，该系统是个ODS中间库，其中有大量临时创建的表，导致柱状图数据非常多。由于是OLAP，还是要保留柱图信息，而且每天都在导入/trunc数据，统计周期也不能加大。毕竟是个非关键中间库，可以考虑38003 event或者启动到migrate状态来处理：

[oracle@ORA-TEST-03 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 20 16:47:30 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> truncate cluster c_obj#_intcol#;
truncate cluster c_obj#_intcol#
                 *
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

SQL> select obj# from obj$ where name='C_OBJ#_INTCOL#';

      OBJ#
----------
       421

对象ID大于59，可以通过设置EVENT 38003来处理：

[oracle@ORA-TEST-03 ~]$ oerr ora 38003
38003, 00000, "CBO Disable column stats for the dictionary objects in recursive SQL"
// *Cause:
// *Action:

SQL> show parameter event;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string

SQL> alter system set EVENT="38003 trace name context forever, level 10" SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2231088 bytes
Variable Size            2499806416 bytes
Database Buffers          822083584 bytes
Redo Buffers               16330752 bytes
Database mounted.
Database opened.
SQL> show parameter event

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      38003 trace name context forev
                                                 er, level 10
SQL> create table sys.histgrm_tmp as select * from sys.histgrm$;

Table created.

SQL> truncate cluster c_obj#_intcol#;

Cluster truncated.

SQL> alter system reset EVENT;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

启动到migrage状态

SQL> startup migrate
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2231088 bytes
Variable Size            2499806416 bytes
Database Buffers          822083584 bytes
Redo Buffers               16330752 bytes
Database mounted.
Database opened.
SQL> show parameter event

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string
xml_db_events                        string      enable
SQL> truncate cluster c_obj#_intcol#;

Cluster truncated.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2231088 bytes
Variable Size            2499806416 bytes
Database Buffers          822083584 bytes
Redo Buffers               16330752 bytes
Database mounted.
Database opened.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-715154/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-715154/