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.