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


通过上篇文章,我们已经基本了解 标记列为未使用 (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 --