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>
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>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
