38003

如何绕过ORA-701错误来实施对数据库自举对象bootstrap object的一些修改呢？

[oracle@mlab1 ~]$ oerr ora 701
00701, 00000, "object necessary for warmstarting database cannot be altered"
// *Cause:  Attempt to alter or drop a database object (table, cluster, or
//          index) which are needed for warmstarting the database.
// *Action: None.

首先需要说明的是，这纯粹为了技术教学，在实际的产品环境中不要使用如下手段！！！

SQL> alter index SYS.I_H_OBJ#_COL# rebuild;
alter index SYS.I_H_OBJ#_COL# rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

SQL> ! oerr ora 38003
38003, 00000, "CBO Disable column stats for the dictionary objects in recursive SQL"
// *Cause:
// *Action:

1. 可以通过38003 event禁用递归SQL中队数据字典对象字段信息的核实，但是注意设置该38003 event需要重启实例：

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 1653518336 bytes
Fixed Size                  2228904 bytes
Variable Size             536874328 bytes
Database Buffers         1107296256 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.

SQL>  alter index SYS.I_H_OBJ#_COL# rebuild;

Index altered.

以上38003 event对于alter index rebuild这种操作绝大多数场景都适用，但是如果你想MOVE/SHRINK OBJ$表则做不到：

2. 使用第二种方法必须将数据库置于MIGRATE/UPDATE模式，这要求停机时间:

create table tab$                                             /* table table */
( obj#          number not null,                            /* object number */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
   * TRANSACTION DURING TRUNCATE */

cluster c_obj#(obj#)
/

shutdown immediate;
startup upgrade;

SQL> alter table c_obj# enable row movement;

Table altered.

SQL> alter table c_obj# shrink space;

Table altered.

由于tab$其实是c_obj的cluster table所以，只需要SHRINK C_OBJ#即可回收空间。但是请注意如果为了回收几百兆的空间去操作这些关键性的自举对象是没有意义的，千万不要在产品环境中这样操作！！

如何绕过ORA-00701错误和降低bootstrap对象的高水位