恢复sys.IDL_UB1$被rename了

《如何恢复Truncate sys.IDL_UB1$？》之前分享过这个对象被清空时的恢复，近期又有用户发现system表空间占用较大，发现IDL_UB1$是top对象，于是乎采用取表DDL，rename原表名，新建该表，数据导出导入方式重建该表。但是发现rename IDL_UB1$表，新创建IDL_UB1$后，exp无法导出，所有DDL无法执行, 包括无法rename回退，庆幸的是当前数据库还没有重启，否则就无法正常启动了，恢复更加复杂。

测试

SQL> @ddl IDL_UB1$
PL/SQL procedure successfully completed.


DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
------------------------------------------------------------------------------------------------------
  CREATE TABLE "SYS"."IDL_UB1$"
   (    "OBJ#" NUMBER NOT NULL ENABLE,
        "PART" NUMBER NOT NULL ENABLE,
        "VERSION" NUMBER,
        "PIECE#" NUMBER NOT NULL ENABLE,
        "LENGTH" NUMBER NOT NULL ENABLE,
        "PIECE" LONG RAW NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;

SQL> rename IDL_UB1$ to IDL_UB1$_bak;
Table renamed.

SQL>   CREATE TABLE "SYS"."IDL_UB1$"
   (    "OBJ#" NUMBER NOT NULL ENABLE,
        "PART" NUMBER NOT NULL ENABLE,
        "VERSION" NUMBER,
        "PIECE#" NUMBER NOT NULL ENABLE,
        "LENGTH" NUMBER NOT NULL ENABLE,
        "PIECE" LONG RAW NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
  2    3    4    5    6    7    8    9   10   11   12   13
Table created.

SQL> insert into IDL_UB1$_NEW select * from IDL_UB1$;
insert into IDL_UB1$_NEW select * from IDL_UB1$
                                *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL> drop table test;
^C
drop table test
             *
ERROR at line 1:
ORA-12152: TNS:unable to send break message

SQL> @ddl IDL_UB1$
BEGIN dbms_metadata.set_transform_param( dbms_metadata.session_transform,'SQLTERMINATOR', TRUE); END;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Note：
rename回退递归SQL会用到IDL_UB1$， 所以rename IDL_UB1$是单向操作，IDL_UB1$变化后数据库DDL无法操作，如果是RAC 还可能出现message无法同步，更复杂的情况，以下的恢复还可能导致sqlplus hang无法连接数据库。

注意IDL_UB1$有long raw数据类型
Long raw t> 32k can’t be inserted via SQL or via PL/SQL. You must use C, PRO*C, VB, .NET, Java ,… for that. Or you can use SQL*PLus copy command (deprecated but supported so far).

庆幸是数据库当前没有重启

恢复

SQL> update obj$ set name='IDL_UB1$_NEW'  where name='IDL_UB1$';
SQL> update obj$ set name='IDL_UB1$'  where name='IDL_UB1$_BAK';

Note:
操作数据字典表有风险，不建议生产库在没有专家评估情况下直接操作。联系www.anbob.com