问:
使用 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博客 ” ,链接:http://blog.itpub.net/30162081/viewspace-2126980/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30162081/viewspace-2126980/