【unused】恢复unused的字段
我们知道 unused 是一个不可逆删除字段的一个动作，原理是先在数据字典中标识某字段为 unused，当系统空闲时，再执行彻底删除字段的动作，由于这个 unused 的动作在设计时就没打算让你恢复，所以，Oracle 这个做的也挺绝的，只有一个视图可以查看某张表有几个字段修改为 unused，剩下的动作就是什么时候在空闲时，将 unused 的字段彻底清除。
既然 unused 的字段没有删除，只是修改了字典表，那么，我们恢复把它修改回来，那么 unused 的字段是否可以恢复呢?带着这个疑问，做了如下测试：
1、在scott用户下创建两张一样的表T1,T2,并插入部分测试数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
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到底是个什么“东西”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
--我们从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、我们之前创建了两张表，我们可以通过对比两张表的字典表有什么变化，然后将其更新回去，应该就能恢复
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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、按照之前的思路，对基表进行更新（虚拟机测试环境，毫无问题）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
--更新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成功
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
--我们直接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自己用的相对比较多，我们作为管理、开发中几乎用不上这个函数，后续会给出这个函数的具体含义