【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自己用的相对比较多,我们作为管理、开发中几乎用不上这个函数,后续会给出这个函数的具体含义