首页 » ORACLE 9i-23ai » 恢复表设置为unused的字段
恢复表设置为unused的字段
Unused column in the table is set to reuse
先说说unused的用处，当生产库中有一张特大的表，如果像删除一个字段drop column命令因是ddl，会给表级增加排它锁，所有用到该表的应用都无法查询，同样表很大，就会等待很长的时间，所以ORACLE推出了unuse,先把表的该字段设为unuse,等到一个闲的时间再去真正物理的删除，但如果你标为unused了，后悔了咋办？没用，找不回来了，呵呵，有个方法可以找回但操作基表，不到迫不得已不要这么做会有一定风险！
–生产库不要操作
整个操作如下
SQL> conn system/oracle Connected. SQL> create user test identified by test; User created. SQL> grant connect to test; Grant succeeded. SQL> grant resource to test; Grant succeeded. SQL> create table test.test_unuse as 2 select * from dba_objects; Table created. SQL> conn test/test Connected. SQL> select count(*) from test_unuse; COUNT(*) ---------- 50706 SQL> desc test_unuse; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> alter table test_unuse set unused column object_id; Table altered. SQL> desc test_unuse; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1)
开始恢复 基表的owner 为sys
SQL> conn sys/oracle as sysdba; Connected. SQL> select obj# from obj$ where name='test_unuse'; no rows selected SQL> select obj# from obj$ where name='TEST_UNUSE'; OBJ# ---------- 66274 SQL> col name for a30 SQL> select col#,intcol#,name from col$ where obj#=66274 COL# INTCOL# NAME ---------- ---------- ------------------------------ 1 1 OWNER 2 2 OBJECT_NAME 3 3 SUBOBJECT_NAME 0 4 SYS_C00004_11040814:07:01$ 4 5 DATA_OBJECT_ID 5 6 OBJECT_TYPE 6 7 CREATED 7 8 LAST_DDL_TIME 8 9 TIMESTAMP 9 10 STATUS 10 11 TEMPORARY 11 12 GENERATED 12 13 SECONDARY 13 rows selected. SQL> select cols from tab$ where obj#=66274; COLS ---------- 12 SQL> update col$ set col#=intcol# where obj#=66274; 13 rows updated. SQL> update tab$ set cols=13 where obj#=66274; 1 row updated. SQL> update col$ set name='OBJECT_ID' WHERE obj#=66274 and col#=4; 1 row updated. SQL> select obj#,col#,name,property 2 from col$ 3 where obj#=66274; OBJ# COL# NAME PROPERTY ---------- ---------- ------------------------------ ---------- 66274 1 OWNER 14336 66274 2 OBJECT_NAME 14336 66274 3 SUBOBJECT_NAME 14336 66274 4 OBJECT_ID 47136 66274 5 DATA_OBJECT_ID 14336 66274 6 OBJECT_TYPE 14336 66274 7 CREATED 14336 66274 8 LAST_DDL_TIME 14336 66274 9 TIMESTAMP 14336 66274 10 STATUS 14336 66274 11 TEMPORARY 14336 66274 12 GENERATED 14336 66274 13 SECONDARY 14336 13 rows selected. SQL> update col$ set property=0 where obj#=66274; 13 rows updated. SQL> commit; Commit complete. SQL> CONN TEST/TEST SQL> desc test_unuse; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL>CONN SYSTEM/ORACLE SQL>ALTER SYSTEM flush shared_pool; SQL>CONN TEST/TEST SQL> desc test_unuse; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> COL OBJECT_NAME FOR A10 SQL> select object_name,object_id from test_unuse WHERE ROWNUM<3; OBJECT_NAM OBJECT_ID ---------- ---------- ICOL$ 20 I_USER1 44 SQL> show user USER is "TEST" SQL> alter table test_unuse set unused column object_name; Table altered. SQL> alter table test_unuse drop unused column; Table altered. SQL> conn sys/oracle as sysdba Connected. SQL> select col#,intcol#,name from col$ where obj#=66274; COL# INTCOL# NAME ---------- ---------- ------------------------------ 1 1 OWNER 2 2 SUBOBJECT_NAME 3 3 OBJECT_ID 4 4 DATA_OBJECT_ID 5 5 OBJECT_TYPE 6 6 CREATED 7 7 LAST_DDL_TIME 8 8 TIMESTAMP 9 9 STATUS 10 10 TEMPORARY 11 11 GENERATED 12 12 SECONDARY 12 rows selected. SQL>
Does GoldenGate Support Tables with an UNUSED Column? (Doc ID 968622.1)
Yes.
Is it Possible to Speed Up the Drop of Unused Column That is GBs in Size? (Doc ID 358269.1)
No, there is no other way to drop the column.
ALTER TABLE <table_name> DROP UNUSED COLUMNS –> data will be rewritten. Lots of redo.
微信扫一扫，打赏作者吧～上一篇： 修正ORACLE表的高水位线HWM
目前这篇文章有1条评论(Rss)评论关闭。