我们知道unused是一个不可逆删除字段的一个动作，原理是先在数据字典中标识某字段为unused，当系统空闲时，再执行彻底删除字段的动作，由于这个unused的动作在设计时就没打算让你恢复，所以，Oracle这个做的也挺绝的，只有一个视图可以查看某张表有几个字段修改为unused，剩下的动作就是什么时候在空闲时，将unused的字段彻底清除。

既然unused的字段没有删除，只是修改了字典表，那么，我们恢复把它修改回来，那么unused的字段是否可以恢复呢?带着这个疑问，做了如下测试

1）在scott用户下创建两张一样的表T1,T2,并插入部分测试数据

SCOTT@ORA11GR2>create table t1(x int,y int,z int);

Table created.

SCOTT@ORA11GR2>insert into t1 select rownum-1,rownum,rownum+1 from dual connect by rownum<=10;

10 rows created.

SCOTT@ORA11GR2>commit;

Commit complete.

SCOTT@ORA11GR2>create table t2 as select * from t1;

Table created.

SCOTT@ORA11GR2>

SCOTT@ORA11GR2>select * from t1;

X Y Z

---------- ---------- ----------

0 1 2

1 2 3

2 3 4

3 4 5

4 5 6

5 6 7

6 7 8

7 8 9

8 9 10

9 10 11

10 rows selected.

SCOTT@ORA11GR2>select * from t2;

X Y Z

---------- ---------- ----------

0 1 2

1 2 3

2 3 4

3 4 5

4 5 6

5 6 7

6 7 8

7 8 9

8 9 10

9 10 11

10 rows selected.

SCOTT@ORA11GR2>

2）将T1表的z字段修改为unused

SCOTT@ORA11GR2>alter table t1 set unused (z);

Table altered.

SCOTT@ORA11GR2>

--经查询验证，z字段确实已经不存在了

SCOTT@ORA11GR2>select * from t1;

X Y

---------- ----------

0 1

1 2

2 3

3 4

4 5

5 6

6 7

7 8

8 9

9 10

10 rows selected.

SCOTT@ORA11GR2>

--查看user_tab_cols视图，我们会发现，其实字段也还在，只是改了个名字，COLUMN_ID为空了

SCOTT@ORA11GR2>select table_name,column_name,data_type,column_id from user_tab_cols where table_name='T1';

TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID

------------ -------------------------- ------------ ---------

T1 SYS_C00003_12122100:34:14$ NUMBER

T1 Y NUMBER 2

T1 X NUMBER 1

SCOTT@ORA11GR2>

--查看user_unused_col_tabs视图，我们会发现有一条记录，两个字段，表名和统计的数量，意思是T1表有一个字段为unused（可见，Oracle在做这个功能的时候，就是不打算让你恢复回来，其实仔细想想不让恢复也是合理的，一是所有涉及到这个字段的相关SQL都需要调整，二是，数据频繁的DML，unused字段一会儿有一会儿没有，数据也就乱了，索性，就不让你恢复了，呵呵）

SCOTT@ORA11GR2>select * from user_unused_col_tabs;

TABLE_NAME COUNT

------------------------------ ----------

T1 1

SCOTT@ORA11GR2>

3）既然user_unused_col_tabs视图记录的是一个统计值，那么我们看看它是从那张表统计的，我们首先看看user_unused_col_tabs到底是个什么“东西”

--我们从all_objects中可以看到，USER_UNUSED_COL_TABS这个对象有两个，一个是视图，一个是同义词

SCOTT@ORA11GR2>col owner for a6

SCOTT@ORA11GR2>col object_name for a22

SCOTT@ORA11GR2>col object_type for a12

SCOTT@ORA11GR2>SELECT owner,object_type,object_name FROM all_objects where object_name='USER_UNUSED_COL_TABS';

OWNER OBJECT_TYPE OBJECT_NAME

------ ------------ ----------------------

SYS VIEW USER_UNUSED_COL_TABS

PUBLIC SYNONYM USER_UNUSED_COL_TABS

SCOTT@ORA11GR2>

--我们不用考虑同义词，直接获取视图的脚本，我们从视图的脚本中可以看到，这个视图就是从基表中直接统计的结果，视图中有几个备注，其中给一个是“is unused columns”，说明，那个条件是判断是否是unused的

SCOTT@ORA11GR2>set long 99999

SCOTT@ORA11GR2>SELECT text FROM all_views where view_name='USER_UNUSED_COL_TABS';

TEXT

--------------------------------------------------------------------------

select o.name, count(*)

from sys.col$ c, sys.obj$ o

where o.obj# = c.obj#

and o.owner# = userenv('SCHEMAID')

and bitand(c.property, 32768) = 32768 -- is unused columns

and bitand(c.property, 1) != 1 -- not ADT attribute col

and bitand(c.property, 1024) != 1024 -- not NTAB's setid col

group by o.name

SCOTT@ORA11GR2>

4）我们之前创建了两张表，我们可以通过对比两张表的字典表有什么变化，然后将其更新回去，应该就能恢复

SCOTT@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>select c.obj#,c.col#,c.name,c.property

2 from sys.col$ c, sys.obj$ o

3 where o.obj# = c.obj#

4 and o.name in ('T1','T2')and o.owner#=84

5 order by 1

6 ;

OBJ# COL# NAME PROPERTY

---------- ---------- ------------------------------ ----------

74571 1 X 0

74571 2 Y 0

74571 0 SYS_C00003_12122023:16:44$ 32800

74572 1 X 0

74572 2 Y 0

74572 3 Z 0

6 rows selected.

SYS@ORA11GR2>

SYS@ORA11GR2>select obj#,cols from tab$ where obj# in (74571,74572);

OBJ# COLS

---------- ----------

74571 2

74572 3

SYS@ORA11GR2>

--经过对比，我们会发现col$表中 COL#、NAME、PROPERTY这三个字段有了些变化，还有就是tab$表中T1表的字段数量发生了变化

4）按照之前的思路，对基表进行更新（虚拟机测试环境，毫无问题）

--更新tab$表，将对象T1(即obj#=74571的对象）的字段数量恢复为3个

SYS@ORA11GR2>update tab$ set cols=3 where obj#=74571;

1 row updated.

SYS@ORA11GR2>commit;

Commit complete.

SYS@ORA11GR2>

--更新col$表，将其恢复到初始状态

SYS@ORA11GR2>update col$ set col#=3,name='Z',property=0 where obj#=74571 and col#=0;

1 row updated.

SYS@ORA11GR2>commit;

Commit complete.

SYS@ORA11GR2>

--验证更新基表后的效果

SYS@ORA11GR2>select c.obj#,c.col#,c.name,c.property

2 from sys.col$ c, sys.obj$ o

3 where o.obj# = c.obj#

4 and o.name in ('T1','T2')and o.owner#=84;

OBJ# COL# NAME PROPERTY

---------- ---------- ------------------------------ ----------

74571 1 X 0

74571 2 Y 0

74571 3 Z 0

74572 1 X 0

74572 2 Y 0

74572 3 Z 0

6 rows selected.

SYS@ORA11GR2>select obj#,cols from tab$ where obj# in (74571,74572);

OBJ# COLS

---------- ----------

74571 3

74572 3

SYS@ORA11GR2>

5）验证是否恢复unused成功

--我们直接desc验证，发现报错了

SYS@ORA11GR2>desc scott.t1

ERROR:

ORA-03113: end-of-file on communication channel

Process ID: 12628

Session ID: 33 Serial number: 3654

SYS@ORA11GR2>

--我们尝试重启数据库

SYS@ORA11GR2>startup force;

ORACLE instance started.

Total System Global Area 841162752 bytes

Fixed Size 1339768 bytes

Variable Size 541068936 bytes

Database Buffers 293601280 bytes

Redo Buffers 5152768 bytes

Database mounted.

Database opened.

SYS@ORA11GR2>

--再次验证，竟然可以了

SYS@ORA11GR2>desc scott.t1

Name Null? Type

------------------------- -------- -------------

X NUMBER(38)

Y NUMBER(38)

Z NUMBER(38)

SYS@ORA11GR2>desc scott.t2

Name Null? Type

------------------------- -------- -------------

X NUMBER(38)

Y NUMBER(38)

Z NUMBER(38)

SYS@ORA11GR2>select * from scott.t1;

X Y Z

---------- ---------- ----------

0 1 2

1 2 3

2 3 4

3 4 5

4 5 6

5 6 7

6 7 8

7 8 9

8 9 10

9 10 11

10 rows selected.

SYS@ORA11GR2>

6）小结

1:此种方法更新了基表，这是Oracle所不建议的，所以，大家在做unused的时候，一定要谨慎、小心，不要轻易的unused

2:修改基表后，不是立即生效的，需要重启数据库

3:我们在这个过程中，发现了bitand这个函数，这个函数Oracle自己用的相对比较多，我们作为管理、开发中几乎用不上这个函数，后续会给出这个函数的具体含义

ITPub博客

【unused】恢复unused的字段

最新文章