Ask Hoegh(9)——SET UNUSED列可以恢复吗？

问：

使用 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条数据。

点击(此处)折叠或打开

1. SCOTT@HOEGH>
   
2. SCOTT@HOEGH> create table hoegh(id number,name varchar2(20),dynasty varchar2(10));
   
3. 
   
4. Table created.
   
5. 
   
6. SCOTT@HOEGH> insert into hoegh values(1,'xishi','chunqiu');
   
7. 
   
8. 1 row created.
   
9. 
   
10. SCOTT@HOEGH> insert into hoegh values(2,'wangzhaojun','xihan');
    
11. 
    
12. 1 row created.
    
13. 
    
14. SCOTT@HOEGH> insert into hoegh values(3,'diaochan','donghan');
    
15. 
    
16. 1 row created.
    
17. 
    
18. SCOTT@HOEGH> insert into hoegh values(4,'yangyuhuan','tang');
    
19. 
    
20. 1 row created.
    
21. 
    
22. SCOTT@HOEGH> commit;
    
23. 
    
24. Commit complete.
    
25. 
    
26. SCOTT@HOEGH> desc hoegh
    
27.  Name Null? Type
    
28.  ----------------------------------------- -------- ----------------------------
    
29.  ID NUMBER
    
30.  NAME VARCHAR2(20)
    
31.  AGE VARCHAR2(10)
    
32. 
    
33. SCOTT@HOEGH> select * from hoegh;
    
34. 
    
35.         ID NAME DYNASTY
    
36. ---------- -------------------- ----------
    
37.          1 xishi chunqiu
    
38.          2 wangzhaojun xihan
    
39.          3 diaochan donghan
    
40.          4 yangyuhuan tang
    
41. 
    
42. SCOTT@HOEGH>

2.设置并删除UNUSED列
我们选择hoegh表的第3列DYNASTY进行测试。

点击(此处)折叠或打开

1. SCOTT@HOEGH>
   
2. SCOTT@HOEGH> alter table hoegh set unused column DYNASTY;
   
3. 
   
4. Table altered.
   
5. 
   
6. SCOTT@HOEGH> desc hoegh
   
7.  Name Null? Type
   
8.  ----------------------------------------- -------- ----------------------------
   
9.  ID NUMBER
   
10.  NAME VARCHAR2(20)
    
11. 
    
12. SCOTT@HOEGH>
    
13. SCOTT@HOEGH> select * from hoegh;
    
14. 
    
15.         ID NAME
    
16. ---------- --------------------
    
17.          1 xishi
    
18.          2 wangzhaojun
    
19.          3 diaochan
    
20.          4 yangyuhuan
    
21. 
    
22. SCOTT@HOEGH>

3.根据测试表的OBJECT_ID修改数据字典信息

首先我们查询hoegh表的对象编号，然后切换到sys用户下，根据对象编号修改底层的数据字典信息。

点击(此处)折叠或打开

1. SCOTT@HOEGH>
   
2. SCOTT@HOEGH> SELECT OBJECT_ID FROM USER_OBJECTS
   
3. where OBJECT_NAME= 'HOEGH'; 2
   
4. 
   
5.  OBJECT_ID
   
6. ----------
   
7.     102893
   
8. 
   
9. SCOTT@HOEGH>
   
10. SCOTT@HOEGH> conn / as sysdba
    
11. Connected.
    
12. SYS@HOEGH> select cols from tab$ where obj#=102893;
    
13. 
    
14.       COLS
    
15. ----------
    
16.          2
    
17. 
    
18. SYS@HOEGH> update col$ set col#=intcol# where obj#=102893;
    
19. 
    
20. 3 rows updated.
    
21. 
    
22. SYS@HOEGH>
    
23. SYS@HOEGH> update tab$ set cols=cols+1 where obj#=102893;
    
24. 
    
25. 1 row updated.
    
26. 
    
27. SYS@HOEGH> update col$ set name='DYNASTY' where obj#=102893 and col#=3;
    
28. 
    
29. 1 row updated.
    
30. 
    
31. SYS@HOEGH> update col$ set property=0 where obj#=102893;
    
32. 
    
33. 3 rows updated.
    
34. 
    
35. SYS@HOEGH> commit;
    
36. 
    
37. Commit complete.
    
38. 
    
39. SYS@HOEGH>
    
40. SYS@HOEGH> desc scott.hoegh
    
41.  Name Null? Type
    
42.  ----------------------------------------- -------- ----------------------------
    
43.  ID NUMBER
    
44.  NAME VARCHAR2(20)
    
45. 
    
46. SYS@HOEGH>

4.重启数据库，确实测试表是否恢复

我们发现hoegh表的DYNASTY列仍然看不到，此时我们需要重启数据库。

点击(此处)折叠或打开

1. SYS@HOEGH>
   
2. SYS@HOEGH> shu immediate
   
3. Database closed.
   
4. Database dismounted.
   
5. ORACLE instance shut down.
   
6. SYS@HOEGH> startup
   
7. ORACLE instance started.
   
8. 
   
9. Total System Global Area 941600768 bytes
   
10. Fixed Size 1348860 bytes
    
11. Variable Size 717228804 bytes
    
12. Database Buffers 218103808 bytes
    
13. Redo Buffers 4919296 bytes
    
14. Database mounted.
    
15. Database opened.
    
16. SYS@HOEGH> desc scott.hoegh
    
17.  Name Null? Type
    
18.  ----------------------------------------- -------- ----------------------------
    
19.  ID NUMBER
    
20.  NAME VARCHAR2(20)
    
21.  DYNASTY VARCHAR2(10)
    
22. 
    
23. SYS@HOEGH>
    
24. SYS@HOEGH> select * from scott.hoegh;
    
25. 
    
26.         ID NAME DYNASTY
    
27. ---------- -------------------- ----------
    
28.          1 xishi chunqiu
    
29.          2 wangzhaojun xihan
    
30.          3 diaochan donghan
    
31.          4 yangyuhuan tang
    
32. 
    
33. SYS@HOEGH>
    
34. SYS@HOEGH>

至此，恢复完成。
                                                                                                                                                                                            ~~~~~~~ the end~~~~~~~~~
                                                                                                                                                                                                               hoegh
                                                                                                                                                                                                           2016.10.24

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30162081/viewspace-2126980/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30162081/viewspace-2126980/