问：
使用 SET UNUSED 选项可以标记一列或者多列不可用，对于SET UNUSED列可以恢复吗？
如果可以，如何恢复？
答：
首先我们了解一下SET UNUSED选项的功能和语法。功能
使用 SET UNUSED 选项标记一列或者多列不可用，设置UNUSED的作用是为了在cpu、内存等资源不充足的时候，先做上UNUSED标记再等数据库资源空闲的时候用DROP SET UNUSED删除。语法
ALTER TABLE table SET UNUSED (多个列名，使用逗号隔开) 或 ALTER TABLE table SET UNUSED COLUMN colName;ALTER TABLE table DROP UNUSED COLUMNS;
SET UNUSED列能否恢复？
设置UNUSED 列之后，并不是将该列数据立即删除，而是被隐藏起来，物理上还是存在的，因此可以恢复，但是恢复过程需要修改底层的数据字典并重启数据库，因此在执行SET UNUSED操作时务必慎重！
恢复步骤
1.创建测试表
我们在scott用户下创建测试表hoegh，并插入4条数据。点击(此处)折叠或打开
-
SCOTT@HOEGH>
-
SCOTT@HOEGH> create table hoegh(id number,name varchar2(20),dynasty varchar2(10));
-
-
Table created.
-
-
SCOTT@HOEGH> insert into hoegh values(1,'xishi','chunqiu');
-
-
1 row created.
-
-
SCOTT@HOEGH> insert into hoegh values(2,'wangzhaojun','xihan');
-
-
1 row created.
-
-
SCOTT@HOEGH> insert into hoegh values(3,'diaochan','donghan');
-
-
1 row created.
-
-
SCOTT@HOEGH> insert into hoegh values(4,'yangyuhuan','tang');
-
-
1 row created.
-
-
SCOTT@HOEGH> commit;
-
-
Commit complete.
-
-
SCOTT@HOEGH> desc hoegh
-
Name Null? Type
-
----------------------------------------- -------- ----------------------------
-
ID NUMBER
-
NAME VARCHAR2(20)
-
AGE VARCHAR2(10)
-
-
SCOTT@HOEGH> select * from hoegh;
-
-
ID NAME DYNASTY
-
---------- -------------------- ----------
-
1 xishi chunqiu
-
2 wangzhaojun xihan
-
3 diaochan donghan
-
4 yangyuhuan tang
-
- SCOTT@HOEGH>
2.设置并删除UNUSED列
我们选择hoegh表的第3列DYNASTY进行测试。
点击(此处)折叠或打开
-
SCOTT@HOEGH>
-
SCOTT@HOEGH> alter table hoegh set unused column DYNASTY;
-
-
Table altered.
-
-
SCOTT@HOEGH> desc hoegh
-
Name Null? Type
-
----------------------------------------- -------- ----------------------------
-
ID NUMBER
-
NAME VARCHAR2(20)
-
-
SCOTT@HOEGH>
-
SCOTT@HOEGH> select * from hoegh;
-
-
ID NAME
-
---------- --------------------
-
1 xishi
-
2 wangzhaojun
-
3 diaochan
-
4 yangyuhuan
-
- SCOTT@HOEGH>
3.根据测试表的OBJECT_ID修改数据字典信息
首先我们查询hoegh表的对象编号，然后切换到sys用户下，根据对象编号修改底层的数据字典信息。点击(此处)折叠或打开
-
SCOTT@HOEGH>
-
SCOTT@HOEGH> SELECT OBJECT_ID FROM USER_OBJECTS
-
where OBJECT_NAME= 'HOEGH'; 2
-
-
OBJECT_ID
-
----------
-
102893
-
-
SCOTT@HOEGH>
-
SCOTT@HOEGH> conn / as sysdba
-
Connected.
-
SYS@HOEGH> select cols from tab$ where obj#=102893;
-
-
COLS
-
----------
-
2
-
-
SYS@HOEGH> update col$ set col#=intcol# where obj#=102893;
-
-
3 rows updated.
-
-
SYS@HOEGH>
-
SYS@HOEGH> update tab$ set cols=cols+1 where obj#=102893;
-
-
1 row updated.
-
-
SYS@HOEGH> update col$ set name='DYNASTY' where obj#=102893 and col#=3;
-
-
1 row updated.
-
-
SYS@HOEGH> update col$ set property=0 where obj#=102893;
-
-
3 rows updated.
-
-
SYS@HOEGH> commit;
-
-
Commit complete.
-
-
SYS@HOEGH>
-
SYS@HOEGH> desc scott.hoegh
-
Name Null? Type
-
----------------------------------------- -------- ----------------------------
-
ID NUMBER
-
NAME VARCHAR2(20)
-
- SYS@HOEGH>
4.重启数据库，确实测试表是否恢复
我们发现hoegh表的DYNASTY列仍然看不到，此时我们需要重启数据库。点击(此处)折叠或打开
-
SYS@HOEGH>
-
SYS@HOEGH> shu immediate
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SYS@HOEGH> startup
-
ORACLE instance started.
-
-
Total System Global Area 941600768 bytes
-
Fixed Size 1348860 bytes
-
Variable Size 717228804 bytes
-
Database Buffers 218103808 bytes
-
Redo Buffers 4919296 bytes
-
Database mounted.
-
Database opened.
-
SYS@HOEGH> desc scott.hoegh
-
Name Null? Type
-
----------------------------------------- -------- ----------------------------
-
ID NUMBER
-
NAME VARCHAR2(20)
-
DYNASTY VARCHAR2(10)
-
-
SYS@HOEGH>
-
SYS@HOEGH> select * from scott.hoegh;
-
-
ID NAME DYNASTY
-
---------- -------------------- ----------
-
1 xishi chunqiu
-
2 wangzhaojun xihan
-
3 diaochan donghan
-
4 yangyuhuan tang
-
-
SYS@HOEGH>
- SYS@HOEGH>
~~~~~~~ the end~~~~~~~~~
hoegh
2016.10.24
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30162081/viewspace-2126980/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
