AI 助理
备案控制台
开发者社区 数据库 文章 正文

SET UNUSED列可以恢复吗？

2017-06-12 1162
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
传统型负载均衡 CLB，每月750个小时 15LCU
推荐场景：
搭建高可用的微信/支付宝小程序服务
EMR Serverless StarRocks，5000CU*H 48000GB*H
应用型负载均衡 ALB，每月750个小时 15LCU
简介： SET UNUSED列可以恢复吗？  问： 使用 SET UNUSED 选项可以标记一列或者多列不可用，对于SET UNUSED列可以恢复吗？ 如果可以，如何恢复？ 答： 首先我们了解一下SET UNUSED选项的功能和语法。

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));

  4. Table created.

  6. SCOTT@HOEGH> insert into hoegh values(1,'xishi','chunqiu');

  8. 1 row created.

  10. SCOTT@HOEGH> insert into hoegh values(2,'wangzhaojun','xihan');

  12. 1 row created.

  14. SCOTT@HOEGH> insert into hoegh values(3,'diaochan','donghan');

  16. 1 row created.

  18. SCOTT@HOEGH> insert into hoegh values(4,'yangyuhuan','tang');

  20. 1 row created.

  22. SCOTT@HOEGH> commit;

  24. Commit complete.

  26. SCOTT@HOEGH> desc hoegh
  27.  Name Null? Type
  28.  ----------------------------------------- -------- ----------------------------
  29.  ID NUMBER
  30.  NAME VARCHAR2(20)
  31.  AGE VARCHAR2(10)

  33. SCOTT@HOEGH> select * from hoegh;

  35.         ID NAME DYNASTY
  36. ---------- -------------------- ----------
  37.          1 xishi chunqiu
  38.          2 wangzhaojun xihan
  39.          3 diaochan donghan
  40.          4 yangyuhuan tang

  42. SCOTT@HOEGH>

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

点击(此处)折叠或打开

  1. SCOTT@HOEGH> 
  2. SCOTT@HOEGH> alter table hoegh set unused column DYNASTY;

  4. Table altered.

  6. SCOTT@HOEGH> desc hoegh
  7.  Name Null? Type
  8.  ----------------------------------------- -------- ----------------------------
  9.  ID NUMBER
  10.  NAME VARCHAR2(20)

  12. SCOTT@HOEGH> 
  13. SCOTT@HOEGH> select * from hoegh;

  15.         ID NAME
  16. ---------- --------------------
  17.          1 xishi
  18.          2 wangzhaojun
  19.          3 diaochan
  20.          4 yangyuhuan

  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 

  5.  OBJECT_ID
  6. ----------
  7.     102893

  9. SCOTT@HOEGH> 
  10. SCOTT@HOEGH> conn / as sysdba
  11. Connected.
  12. SYS@HOEGH> select cols from tab$ where obj#=102893;

  14.       COLS
  15. ----------
  16.          2

  18. SYS@HOEGH> update col$ set col#=intcol# where obj#=102893;

  20. 3 rows updated.

  22. SYS@HOEGH> 
  23. SYS@HOEGH> update tab$ set cols=cols+1 where obj#=102893;

  25. 1 row updated.

  27. SYS@HOEGH> update col$ set name='DYNASTY' where obj#=102893 and col#=3;

  29. 1 row updated.

  31. SYS@HOEGH> update col$ set property=0 where obj#=102893;

  33. 3 rows updated.

  35. SYS@HOEGH> commit;

  37. Commit complete.

  39. SYS@HOEGH> 
  40. SYS@HOEGH> desc scott.hoegh
  41.  Name Null? Type
  42.  ----------------------------------------- -------- ----------------------------
  43.  ID NUMBER
  44.  NAME VARCHAR2(20)

  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.

  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)

  23. SYS@HOEGH> 
  24. SYS@HOEGH> select * from scott.hoegh;

  26.         ID NAME DYNASTY
  27. ---------- -------------------- ----------
  28.          1 xishi chunqiu
  29.          2 wangzhaojun xihan
  30.          3 diaochan donghan
  31.          4 yangyuhuan tang

  33. SYS@HOEGH> 
  34. SYS@HOEGH>
至此，恢复完成。

标记列为未使用 (Marking Columns Unused)

作者：dbtan |【转载时请以超链接形式标明文章出处和作者信息】





Marking Columns Unused 基本操作步骤

1. 标记列为未使用

SQL 
ALTER TABLE <table_name> SET UNUSED (<column_name>);

2. 然后在数据库空闲时，再删除列

SQL 
ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>;

-- CHECKPOINT <n>

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

参见官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11662

Marking Columns Unused

将列设为UNUSED时的系统行为

0. 实验环境 Oracle 11.2.0.4

SQL 
17:03:36 sys@TQ(tq-78)> select * from v$version;

BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Elapsed: 00:00:00.00

1. 创建测试表 t18

SQL 
17:04:18 tq@TQ(tq-78)> -- 创建表 t18 17:04:19 tq@TQ(tq-78)> create table t18 (a number, b number, c number, d number);

Table created.

Elapsed: 00:00:00.06

2. 查看表 t18 在数据库中分配的编号 object_id 为 152037

SQL 
17:05:02 tq@TQ(tq-78)> -- 17:05:03 tq@TQ(tq-78)> SELECT object_id 17:05:03 2 FROM dba_objects 17:05:03 3 WHERE object_name = 'T18' 17:05:03 4 AND owner = 'TQ';

 OBJECT_ID ---------- 152037

Elapsed: 00:00:00.01

3. 查看表 t18 在 col$ 数据字典中 col#, segcol#, intcol# 的值

SQL 
17:05:48 tq@TQ(tq-78)> -- 17:05:49 tq@TQ(tq-78)> col name for a30;
17:05:49 tq@TQ(tq-78)>
17:05:49 tq@TQ(tq-78)> SELECT col#, segcol#, name, intcol#, type# 17:05:49 2 FROM sys.col$ 17:05:49 3 WHERE obj# IN (SELECT object_id 17:05:49 4 FROM dba_objects 17:05:49 5 WHERE object_name = 'T18' 17:05:49 6 AND owner = 'TQ');

      COL#    SEGCOL# NAME                              INTCOL#      TYPE# ---------- ---------- ------------------------------ ---------- ---------- 1          1 A                                       1          2
         2          2 B                                       2          2
         3          3 C                                       3          2
         4          4 D                                       4          2

Elapsed: 00:00:00.01
SQL 
17:06:38 tq@TQ(tq-78)> -- 17:06:39 tq@TQ(tq-78)> col column_name for a20;
17:06:39 tq@TQ(tq-78)> col data_type for a20;
17:06:39 tq@TQ(tq-78)>
17:06:39 tq@TQ(tq-78)> select column_name, 17:06:39 2 data_type, 17:06:39 3 column_id, 17:06:39 4 hidden_column, 17:06:39 5 segment_column_id  seg_cid, 17:06:39 6 internal_column_id internal_cid 17:06:39 7 from dba_tab_cols 17:06:39 8 where owner = 'TQ' 17:06:39 9 and table_name = 'T18';

COLUMN_NAME          DATA_TYPE             COLUMN_ID HID    SEG_CID INTERNAL_CID -------------------- -------------------- ---------- --- ---------- ------------ A                    NUMBER                        1 NO           1            1
B                    NUMBER                        2 NO           2            2
C                    NUMBER                        3 NO           3            3
D                    NUMBER                        4 NO           4            4

Elapsed: 00:00:00.00

说明：col$ 数据字典中 col#, segcol#, intcol# 的意义：

COL# 可以表示该列是否在用（ 0 为 UNUSED ），

SEGCOL# 表示各列在数据块上存储时的顺序，

INTCOL# 表示创建表时各列的定义顺序。

4. 标记列 b,d 未使用，并查看表 t18 在 col$ 数据字典中 col#, segcol#, intcol# 的值

SQL 
17:08:31 tq@TQ(tq-78)> -- 标记字段b,d 未使用 17:08:32 tq@TQ(tq-78)> alter table t18 set unused (b, d);

Table altered.

Elapsed: 00:00:00.17
17:08:35 tq@TQ(tq-78)> desc t18;
 Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- A                                                  NUMBER
 C                                                  NUMBER
SQL 
17:12:23 tq@TQ(tq-78)> -- 17:12:24 tq@TQ(tq-78)> col name for a30;
17:12:24 tq@TQ(tq-78)>
17:12:24 tq@TQ(tq-78)> SELECT col#, segcol#, name, intcol#, type# 17:12:24 2 FROM sys.col$ 17:12:24 3 WHERE obj# IN (SELECT object_id 17:12:24 4 FROM dba_objects 17:12:24 5 WHERE object_name = 'T18' 17:12:24 6 AND owner = 'TQ');

      COL#    SEGCOL# NAME                              INTCOL#      TYPE# ---------- ---------- ------------------------------ ---------- ---------- 1          1 A                                       1          2
         0          2 SYS_C00002_16121517:08:35$              2          2
         2          3 C                                       3          2
         0          4 SYS_C00004_16121517:08:35$              4          2

Elapsed: 00:00:00.00
17:12:24 tq@TQ(tq-78)>
17:12:24 tq@TQ(tq-78)> -- 17:12:24 tq@TQ(tq-78)> col column_name for a30;
17:12:24 tq@TQ(tq-78)> col data_type for a10;
17:12:24 tq@TQ(tq-78)>
17:12:24 tq@TQ(tq-78)> select column_name, 17:12:24 2 data_type, 17:12:24 3 column_id, 17:12:24 4 hidden_column, 17:12:24 5 segment_column_id  seg_cid, 17:12:24 6 internal_column_id internal_cid 17:12:24 7 from dba_tab_cols 17:12:24 8 where owner = 'TQ' 17:12:24 9 and table_name = 'T18';

COLUMN_NAME                    DATA_TYPE   COLUMN_ID HID    SEG_CID INTERNAL_CID ------------------------------ ---------- ---------- --- ---------- ------------  A                              NUMBER              1 NO           1            1
SYS_C00002_16121517:08:35$     NUMBER                YES          2            2
C                              NUMBER              2 NO           3            3
SYS_C00004_16121517:08:35$     NUMBER                YES          4            4

Elapsed: 00:00:00.00

这里可以看到，在将列设为 UNUSED 之后，COL# 变为 0，其余的列的 COL# 重新排序。而此时该列在数据段上并没有被删除掉，因此其 SEGCOL# 列仍然保持原来的值。

这里原来的B,D列，其名字为系统自动生成的一列，命名形式为 SYS_CNNNNN_YYMMDDHH24:MI:SS$ ，NNNNN 为原来的COLUMN_ID，前面补0补足成5位数。hidden已经变为YES，COLUMN_ID为NULL。其他两列A,C的COLUMN_ID顺序作了调整。这三列的SEGMENT_COLUMN_ID和INTERNAL_COLUMN_ID没有变化。

SQL 
17:20:47 tq@TQ(tq-78)> -- 在DBA_TAB_COLUMNS视图中，B,D列已经没有显示出来。 17:20:48 tq@TQ(tq-78)> select column_name, data_type, column_id 17:20:48 2 from dba_tab_columns 17:20:48 3 where owner = 'TQ' 17:20:48 4 and table_name = 'T18';

COLUMN_NAME                    DATA_TYPE   COLUMN_ID ------------------------------ ---------- ---------- A                              NUMBER              1
C                              NUMBER              2

Elapsed: 00:00:00.00

5. 插入2条测试数据，并 DUMP 数据块

SQL 
17:25:50 tq@TQ(tq-78)> insert into t18 values (1, 1);

1 row created.

Elapsed: 00:00:00.02
17:25:52 tq@TQ(tq-78)> insert into t18 values (2, 2);

1 row created.

Elapsed: 00:00:00.00
17:25:59 tq@TQ(tq-78)> commit; Commit complete.

Elapsed: 00:00:00.00 17:26:01 tq@TQ(tq-78)> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,a,c from t18;

     FILE#     BLOCK#          A          C ---------- ---------- ---------- ---------- 35     133443          1          1
        35     133443          2          2

Elapsed: 00:00:00.04
17:26:17 tq@TQ(tq-78)> alter system checkpoint;

System altered.

Elapsed: 00:00:00.08
17:29:26 tq@TQ(tq-78)> alter system dump datafile 35 block 133443;

System altered.

Elapsed: 00:00:00.09
17:29:47 tq@TQ(tq-78)> select value from v$diag_info where name like 'De%';

VALUE ------------------------------------------------------------ /app/oracle/diag/rdbms/tq/tq/trace/tq_ora_25965.trc

Elapsed: 00:00:00.08
block_row_dump:
tab 0, row 0, @0x3f8e
tl: 10 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 02
col  1: *NULL*
col  2: [ 2]  c1 02
tab 0, row 1, @0x3f84
tl: 10 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 03
col  1: *NULL*
col  2: [ 2]  c1 03
end_of_block_dump

6. 增加字段e，并插入数据，再次 DUMP 数据块

SQL 
17:46:38 tq@TQ(tq-78)> alter table t18 add (e number);

Table altered.

Elapsed: 00:00:00.03
17:46:40 tq@TQ(tq-78)> desc t18;
 Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- A                                                  NUMBER
 C                                                  NUMBER
 E                                                  NUMBER

17:46:45 tq@TQ(tq-78)> -- 17:47:05 tq@TQ(tq-78)> col name for a30;
17:47:05 tq@TQ(tq-78)>
17:47:05 tq@TQ(tq-78)> SELECT col#, segcol#, name, intcol#, type# 17:47:05 2 FROM sys.col$ 17:47:05 3 WHERE obj# IN (SELECT object_id 17:47:05 4 FROM dba_objects 17:47:05 5 WHERE object_name = 'T18' 17:47:05 6 AND owner = 'TQ');

      COL#    SEGCOL# NAME                              INTCOL#      TYPE# ---------- ---------- ------------------------------ ---------- ---------- 1          1 A                                       1          2
         0          2 SYS_C00002_16121517:08:35$              2          2
         2          3 C                                       3          2
         0          4 SYS_C00004_16121517:08:35$              4          2
         3          5 E                                       5          2

Elapsed: 00:00:00.00
17:47:05 tq@TQ(tq-78)>
17:47:05 tq@TQ(tq-78)> -- 17:47:05 tq@TQ(tq-78)> col column_name for a30;
17:47:05 tq@TQ(tq-78)> col data_type for a10;
17:47:05 tq@TQ(tq-78)>
17:47:05 tq@TQ(tq-78)> select column_name, 17:47:05 2 data_type, 17:47:05 3 column_id, 17:47:05 4 hidden_column, 17:47:05 5 segment_column_id  seg_cid, 17:47:05 6 internal_column_id internal_cid 17:47:05 7 from dba_tab_cols 17:47:05 8 where owner = 'TQ' 17:47:05 9 and table_name = 'T18';

COLUMN_NAME                    DATA_TYPE   COLUMN_ID HID    SEG_CID INTERNAL_CID ------------------------------ ---------- ---------- --- ---------- ------------ A                              NUMBER              1 NO           1            1
SYS_C00002_16121517:08:35$     NUMBER                YES          2            2
C                              NUMBER              2 NO           3            3
SYS_C00004_16121517:08:35$     NUMBER                YES          4            4
E                              NUMBER              3 NO           5            5

Elapsed: 00:00:00.00
17:48:24 tq@TQ(tq-78)> update t18 set e = 1 where a = 1;

1 row updated.

Elapsed: 00:00:00.01
17:48:26 tq@TQ(tq-78)> update t18 set e = 2 where a = 2;

1 row updated.

Elapsed: 00:00:00.00
17:48:33 tq@TQ(tq-78)> commit; Commit complete.

Elapsed: 00:00:00.00 17:51:32 tq@TQ(tq-78)> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,a,c,e from t18;

     FILE#     BLOCK#          A          C          E ---------- ---------- ---------- ---------- ----------  35     133443          1          1          1
        35     133443          2          2          2

Elapsed: 00:00:00.01
17:51:43 tq@TQ(tq-78)> alter system checkpoint;

System altered.

Elapsed: 00:00:00.02
17:51:58 tq@TQ(tq-78)> alter system dump datafile 35 block 133443;

System altered.

Elapsed: 00:00:00.01
17:52:19 tq@TQ(tq-78)> alter system checkpoint;

System altered.

Elapsed: 00:00:00.02
17:52:57 tq@TQ(tq-78)> select value from v$diag_info where name like 'De%';

VALUE ------------------------------------------------------------ /app/oracle/diag/rdbms/tq/tq/trace/tq_ora_25965.trc

Elapsed: 00:00:00.03
block_row_dump:
tab 0, row 0, @0x3f76
tl: 14 fb: --H-FL-- lb: 0x2  cc: 5
col  0: [ 2]  c1 02
col  1: *NULL*
col  2: [ 2]  c1 02
col  3: *NULL*
col  4: [ 2]  c1 02
tab 0, row 1, @0x3f68
tl: 14 fb: --H-FL-- lb: 0x2  cc: 5
col  0: [ 2]  c1 03
col  1: *NULL*
col  2: [ 2]  c1 03
col  3: *NULL*
col  4: [ 2]  c1 03
end_of_block_dump

7. 删除字段e，会同时删除标记为未使用的字段

SQL 
17:56:02 tq@TQ(tq-78)> alter table t18 drop (e);

Table altered.

Elapsed: 00:00:00.20
17:56:03 tq@TQ(tq-78)> desc t18;
 Name                                      Null?    Type ----------------------------------------- -------- ----------------------------  A                                                  NUMBER
 C                                                  NUMBER

17:56:10 tq@TQ(tq-78)> -- 17:56:26 tq@TQ(tq-78)> col name for a30;
17:56:26 tq@TQ(tq-78)>
17:56:26 tq@TQ(tq-78)> SELECT col#, segcol#, name, intcol#, type# 17:56:26 2 FROM sys.col$ 17:56:26 3 WHERE obj# IN (SELECT object_id 17:56:26 4 FROM dba_objects 17:56:26 5 WHERE object_name = 'T18' 17:56:26 6 AND owner = 'TQ');

      COL#    SEGCOL# NAME                              INTCOL#      TYPE# ---------- ---------- ------------------------------ ---------- ----------  1          1 A                                       1          2
         2          2 C                                       2          2

Elapsed: 00:00:00.00
17:56:26 tq@TQ(tq-78)>
17:56:26 tq@TQ(tq-78)> -- 17:56:26 tq@TQ(tq-78)> col column_name for a30;
17:56:26 tq@TQ(tq-78)> col data_type for a10;
17:56:26 tq@TQ(tq-78)>
17:56:26 tq@TQ(tq-78)> select column_name, 17:56:26 2 data_type, 17:56:26 3 column_id, 17:56:26 4 hidden_column, 17:56:26 5 segment_column_id  seg_cid, 17:56:26 6 internal_column_id internal_cid 17:56:26 7 from dba_tab_cols 17:56:26 8 where owner = 'TQ' 17:56:26 9 and table_name = 'T18';

COLUMN_NAME                    DATA_TYPE   COLUMN_ID HID    SEG_CID INTERNAL_CID ------------------------------ ---------- ---------- --- ---------- ------------ A                              NUMBER              1 NO           1            1
C                              NUMBER              2 NO           2            2

Elapsed: 00:00:00.01
17:56:27 tq@TQ(tq-78)> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,a,c from t18;

     FILE#     BLOCK#          A          C ---------- ---------- ---------- ---------- 35     133443          1          1
        35     133443          2          2

Elapsed: 00:00:00.01
17:56:47 tq@TQ(tq-78)> alter system checkpoint;

System altered.

Elapsed: 00:00:00.02
17:56:52 tq@TQ(tq-78)> alter system dump datafile 35 block 133443;

System altered.

Elapsed: 00:00:00.01
17:57:00 tq@TQ(tq-78)> alter system checkpoint;

System altered.

Elapsed: 00:00:00.02
17:57:05 tq@TQ(tq-78)> select value from v$diag_info where name like 'De%';

VALUE ------------------------------------------------------------ /app/oracle/diag/rdbms/tq/tq/trace/tq_ora_25965.trc

Elapsed: 00:00:00.00
block_row_dump:
tab 0, row 0, @0x3f76
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
tab 0, row 1, @0x3f68
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 2]  c1 03
end_of_block_dump

可以看出B,D列和E列已经被删除。从这个实验就可以看出，在删除E时会将UNUSED列一并删除。

DUMP出数据块可以发现，块中每一行只有2列(即：A,C)。

总结：

因此 SET UNUSED 只是标记该列未使用，只是修改了数据字典，速度较快。

SET UNUSED (<column_name>) 比 DROP (<column_name>) 可以节省时间，因为只需修改数据字典不用动到数据块。

而DROP (<column_name>)，不仅修改数据字典，而且修改实际的块数据。如果表比较大，会耗费比较长的时间，会阻止其他dml的，所以一般都是在系统空闲下来的时候才做drop的。


如何恢复 SET UNUSED 的 COLUMN

通过上篇文章，我们已经基本了解 标记列为未使用 (Marking Columns Unused) 了。

我们知道，在 SET UNUSED 后 其实数据并未真的被删除，若这时又想恢复该列，有办法吗？

接下来，我们来实验一下。

如何修复被标记为 UNUSED 的字段

0. 实验环境 Oracle 11.2.0.4

SQL 
16:49:06 sys@TQ(tq-78)> select * from v$version;

BANNER --------------------------------------------------------------------------------  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Elapsed: 00:00:00.00

1. 创建实验表 T20 ，插入测试数据，并将字段C 标记列为未使用

SQL 
16:51:18 tq@TQ(tq-78)> -- 创建实验表 T20 16:51:18 tq@TQ(tq-78)> create table t20 (a number, b number, c varchar2(10), d number);

Table created.

Elapsed: 00:00:00.06
16:51:23 tq@TQ(tq-78)> -- 插入测试数据 16:51:24 tq@TQ(tq-78)> insert into t20 values (1, 2, '3', 4);

1 row created.

Elapsed: 00:00:00.02
16:53:12 tq@TQ(tq-78)> insert into t20 values (2, 3, 'a', 5);

1 row created.

Elapsed: 00:00:00.00
16:53:28 tq@TQ(tq-78)> insert into t20 values (3, 4, 'b', 6);

1 row created.

Elapsed: 00:00:00.01
16:53:39 tq@TQ(tq-78)> commit; Commit complete.

Elapsed: 00:00:00.00 16:58:35 tq@TQ(tq-78)> -- 将t20表中的C字段标记列为未使用 16:58:39 tq@TQ(tq-78)> ALTER TABLE t20 SET UNUSED COLUMN C;

Table altered.

Elapsed: 00:00:00.22
16:58:53 tq@TQ(tq-78)> desc t20;
 Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- A                                                  NUMBER
 B                                                  NUMBER
 D                                                  NUMBER

2. 以下进行恢复 以管理员的身份登陆

2.1 查看表 t20 在数据库中分配的编号 object_id 为 152097
SQL 
17:04:03 sys@TQ(tq-78)> -- 查看表 t20 在数据库中分配的编号 object_id 为 152097 17:04:03 sys@TQ(tq-78)> select object_id 17:04:03 2 from dba_objects 17:04:03 3 where object_name = 'T20' 17:04:03 4 and owner = 'TQ';

 OBJECT_ID ---------- 152097

Elapsed: 00:00:00.01
17:07:32 sys@TQ(tq-78)> col name for a30;
17:07:52 sys@TQ(tq-78)> -- 17:08:02 sys@TQ(tq-78)> SELECT col#, segcol#, name, intcol#, type# 17:08:02 2 FROM sys.col$ 17:08:02 3 WHERE obj# = 152097;

      COL#    SEGCOL# NAME                              INTCOL#      TYPE# ---------- ---------- ------------------------------ ---------- ---------- 1          1 A                                       1          2
         2          2 B                                       2          2
         0          3 SYS_C00003_16121916:58:53$              3          1
         3          4 D                                       4          2

Elapsed: 00:00:00.00
17:11:05 sys@TQ(tq-78)> -- 字段数变为3了 17:11:06 sys@TQ(tq-78)> SELECT COLS FROM sys.TAB$ WHERE OBJ# = 152097;

      COLS ---------- 3

Elapsed: 00:00:00.01
2.2 更新相应的数据字典 sys.col$ 和 sys.tab$ 的值，并刷新 SHARED_POOL
SQL 
17:20:51 sys@TQ(tq-78)> -- 17:20:59 sys@TQ(tq-78)> UPDATE sys.COL$ SET COL# = INTCOL# WHERE OBJ# = 152097;

4 rows updated.

Elapsed: 00:00:00.01
17:21:01 sys@TQ(tq-78)> -- 17:21:17 sys@TQ(tq-78)> UPDATE sys.TAB$ SET COLS = COLS + 1 WHERE OBJ# = 152097;

1 row updated.

Elapsed: 00:00:00.00
17:21:19 sys@TQ(tq-78)> -- 17:21:29 sys@TQ(tq-78)> UPDATE sys.COL$ 17:21:29 2 SET NAME = 'C' 17:21:29 3 WHERE OBJ# = 152097 17:21:29 4 AND COL# = 3;

1 row updated.

Elapsed: 00:00:00.00
17:21:30 sys@TQ(tq-78)> -- 17:21:40 sys@TQ(tq-78)> UPDATE sys.COL$ SET PROPERTY = 0 WHERE OBJ# = 152097;

4 rows updated.

Elapsed: 00:00:00.00
17:21:41 sys@TQ(tq-78)> commit; Commit complete.

Elapsed: 00:00:00.01 17:24:33 sys@TQ(tq-78)> -- 刷新 SHARED_POOL 17:26:26 sys@TQ(tq-78)> alter system flush SHARED_POOL;

System altered.

Elapsed: 00:00:00.11
2.3 验证 t20 表的字段C 已经恢复完成
SQL 
17:28:40 tq@TQ(tq-78)> desc t20;
 Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- A                                                  NUMBER
 B                                                  NUMBER
 C                                                  VARCHAR2(10)
 D                                                  NUMBER

17:29:07 tq@TQ(tq-78)> select * from t20;

          A          B C                   D ---------- ---------- ---------- ---------- 1          2 3                   4
          2          3 a                   5
          3          4 b                   6

 Elapsed: 00:00:00.00

-- The End --




About Me

...............................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599     微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。


DBA笔试面试讲解
 欢迎与我联系

文章标签：
负载均衡
SQL
数据库
关系型数据库
Oracle
相关实践学习
SLB负载均衡实践
本场景通过使用阿里云负载均衡 SLB 以及对负载均衡 SLB 后端服务器 ECS 的权重进行修改，快速解决服务器响应速度慢的问题
负载均衡入门与产品使用指南
负载均衡（Server Load Balancer）是对多台云服务器进行流量分发的负载均衡服务，可以通过流量分发扩展应用系统对外的服务能力，通过消除单点故障提升应用系统的可用性。 本课程主要介绍负载均衡的相关技术以及阿里云负载均衡产品的使用方法。
小麦苗
目录
相关文章
yuanzhengme
|
3月前
|
关系型数据库 数据库 PostgreSQL
pg下delete数据后。除了使用VACUUM FULL TABLE 才能释放磁盘空间外的方法。
【8月更文挑战第12天】pg下delete数据后。除了使用VACUUM FULL TABLE 才能释放磁盘空间外的方法。
yuanzhengme
184 1
讓丄帝愛伱
|
Oracle 关系型数据库
ORA-22859 无效的列修改
ORA-22859 无效的列修改
讓丄帝愛伱
394 0
yzshover
|
测试技术
pg_rewind实例--could not find previous WAL record at %X/%X
pg_rewind实例--could not find previous WAL record at %X/%X
yzshover
117 0
ryan_bai
|
Oracle 关系型数据库
SQLLDR——CTL文件：字段设置
position 关键字用来指定列的开始和结束位置 　 position(m:n)：指从第 m 个字符开始截止到第 n 个字符作为列值 　 position(*+2:15)：直接指定数值的方式叫做绝对偏移量，如果使用*号，则为相对偏移量，表示上一个字段哪里结束，这次就哪里开始，相对便宜量也可以再做运算。
ryan_bai
2083 0
vohelon
|
关系型数据库 MySQL
ALTER TABLE 对 Null 值和默认值的影响
ALTER TABLE 对 Null 值和默认值的影响
vohelon
377 0
一乐乐
|
关系型数据库 MySQL 数据库
mysql中的update(更新)与alter(更改)以及 change和modify的区别
mysql中的update(更新)与alter(更改)以及 change和modify的区别
一乐乐
1449 0
郭一军_guoyJoe
|
缓存 Oracle 关系型数据库
利用BBED恢复UPDATE修改前的值
BBED工具介绍： BBED stands for Block Browser and EDitor. BBED只是一款工具，类似于ultraEdit，单纯的会用BBED来修改数据没有任何意义！关键是要知道为什么要这么改！ 在充分了解Block格式和Oracle的各种机制的基础上广泛使用BBED, 用它来帮你构造测试案例，用它来帮你验证测试结果，用它来帮你深入理解Oracle！
郭一军_guoyJoe
1938 0
笱局长
|
关系型数据库 MySQL 数据库
检测不再使用的索引--check-unused-keys
笱局长
1678 0
ora_果酱o
|
SQL 存储 关系型数据库
LOB类型数据的MOVE
近日，新接手了一个数据库。检查某系统的时候发现，部分数据表存储在USERS表空间下了。我们怎么对LOB进行管理呢？
ora_果酱o
1579 0
余二五
|
SQL 关系型数据库
pt-online-schema-change 添加字段过程解析
余二五
1438 0