示例
1. 创建示例表
SQL> create table test.objects as select * from dba_objects;Table created.SQL> select count(*) from test.objects; COUNT(*)---------- 86984SQL> desc test.objects 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) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30)
2. 删除字段
在逻辑上删除OBJECT_ID字段：
SQL> alter table test.objects set unused column OBJECT_ID;Table altered.
一旦执行该语句，OBJECT_ID字段就不再可见。
SQL> desc test.objects 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) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30)SQL> select OBJECT_ID from test.objects ;select OBJECT_ID from test.objects *ERROR at line 1:ORA-00904: "OBJECT_ID": invalid identifier
可以从dba_unused_col_tabs视图查看表中unused的字段数：
SQL> select * from dba_unused_col_tabs;OWNER TABLE_NAME COUNT------------------------------ ------------------------------ ----------TEST OBJECTS 1
使用以下语句删除表中所有unused的字段：
SQL> alter table test.objects drop unused columns;Table altered.
如果要减少产生的undo日志数量，可以使用在处理了指定的行数之后强制执行检查点的选项：
SQL> alter table test.objects drop unused columns checkpoint 250;Table altered.
3. 字段恢复
由于设置unused之后，并不是将该列数据立即删除，而是被隐藏起来，物理上还是存在的，因此可以通过修改数据字典的方法进行恢复。
先设置OBJECT_NAME列为unused：
SQL> desc test.objects; 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) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30)SQL> alter table test.objects set unused column OBJECT_NAME;Table altered.SQL> desc test.objects; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) 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) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30)
查看test.objects表的对象号：
SQL> select OBJ# from OBJ$ where name='OBJECTS'; OBJ#---------- 88997
对象号为88997
查看test.objects表的字段号、初始字段号、字段名：
SQL> select COL#,INTCOL#,NAME from COL$ where OBJ#=88997; COL# INTCOL# NAME---------- ---------- ------------------------------ 1 1 OWNER 0 2 SYS_C00002_21052517:15:22$ 2 3 SUBOBJECT_NAME 3 4 DATA_OBJECT_ID 4 5 OBJECT_TYPE 5 6 CREATED 6 7 LAST_DDL_TIME 7 8 TIMESTAMP 8 9 STATUS 9 10 TEMPORARY 10 11 GENERATED 11 12 SECONDARY 12 13 NAMESPACE 13 14 EDITION_NAME14 rows selected.
可以看到，原OBJECT_NAME列的字段号已被置为0，OBJECT_NAME后面列的字段号依次减1，OBJECT_NAME列的列名已被重置为SYS_C00002_21052517:15:22$。
查看test.objects表的字段数量：
SQL> select COLS from TAB$ where OBJ#=88997; COLS---------- 13
字段数量已由14个变为13个。
将test.objects表的字段号重新设置为初始字段号：
SQL> update COL$ set COL#=INTCOL# where OBJ#=88997;14 rows updated.SQL> commit;Commit complete.SQL> select COL#,INTCOL#,NAME from COL$ where OBJ#=88997; COL# INTCOL# NAME---------- ---------- ------------------------------ 1 1 OWNER 2 2 SYS_C00002_21052517:15:22$ 3 3 SUBOBJECT_NAME 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 13 13 NAMESPACE 14 14 EDITION_NAME14 rows selected.
将字段数恢复为14个：
SQL> update TAB$ set COLS=COLS+1 where OBJ#=88997;1 row updated.SQL> commit;Commit complete.SQL> select COLS from TAB$ where OBJ#=88997; COLS---------- 14
将第二个字段的字段名设置成与之前相同：
update COL$ set NAME='OBJECT_NAME' where OBJ#=88997 and COL#=2;1 row updated.SQL> commit;Commit complete.SQL> select COL#,INTCOL#,NAME from COL$ where OBJ#=88997; COL# INTCOL# NAME---------- ---------- ------------------------------ 1 1 OWNER 2 2 OBJECT_NAME 3 3 SUBOBJECT_NAME 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 13 13 NAMESPACE 14 14 EDITION_NAME14 rows selected.
SQL> update COL$ set PROPERTY=0 where OBJ#=88997;14 rows updated.SQL> commit;Commit complete.
至此数据字典已全部改回，重启数据库生效。
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 1486495744 bytesFixed Size 2253384 bytesVariable Size 1375735224 bytesDatabase Buffers 100663296 bytesRedo Buffers 7843840 bytesDatabase mounted.Database opened.SQL> desc test.objects 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) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30)SQL> select OBJECT_NAME from test.objects where rownum<10;OBJECT_NAME--------------------------------------------------------------------------------ICOL$I_USER1CON$UNDO$C_COBJ#I_OBJ#PROXY_ROLE_DATA$I_IND1I_CDEF29 rows selected.
恢复完成。
更多精彩干货分享
点击下方名片关注
IT那活儿
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
