[20150430]列删除的简单恢复.txt
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table tt as select * from dept ;
Table created.
SCOTT@test> @desc tt
Name Null? Type
-------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SCOTT@test> alter table tt set unused column dname;
Table altered.
SCOTT@test> column name format a30
SCOTT@test> select obj#,col#,segcol#,name,intcol#,property from sys.col$ where obj# in (select object_id from dba_objects where owner=user and object_name='TT');
OBJ# COL# SEGCOL# NAME INTCOL# PROPERTY
---------- ---------- ---------- ------------------------------ ---------- ----------
302306 1 1 DEPTNO 1 0
302306 0 2 SYS_C00002_15043010:08:49$ 2 32800
302306 2 3 LOC 3 0
--可以发现SEGCOL#还存在,说明真正的数据并没有删除.
SCOTT@test> select DEPTNO,"SYS_C00002_15043010:08:49$",loc from tt;
select DEPTNO,"SYS_C00002_15043010:08:49$",loc from tt
*
ERROR at line 1:
ORA-00904: "SYS_C00002_15043010:08:49$": invalid identifier
--如果这时要对出来如何处理呢?
SCOTT@test> select rowid,obj#,col#,segcol#,name,intcol#,property from sys.col$ where obj# in (select object_id from dba_objects where owner=user and object_name='TT');
ROWID OBJ# COL# SEGCOL# NAME INTCOL# PROPERTY
------------------ ---------- ---------- ---------- ------------------------------ ---------- ----------
AAAAACAABAAAXJFAAQ 302306 1 1 DEPTNO 1 0
AAAAACAABAAAXJFAAR 302306 0 2 SYS_C00002_15043010:08:49$ 2 32800
AAAAACAABAAAXJFAAS 302306 2 3 LOC 3 0
--
alter system flush shared_pool;
update sys.col$ set col#=2 ,property=0 where rowid='AAAAACAABAAAXJFAAR';
update sys.col$ set col#=3 ,property=0 where rowid='AAAAACAABAAAXJFAAS';
SYS@test> column name format a30
SYS@test> select rowid,obj#,col#,segcol#,name,intcol#,property from sys.col$ where obj# in (select object_id from dba_objects where owner='SCOTT' and object_name='TT');
ROWID OBJ# COL# SEGCOL# NAME INTCOL# PROPERTY
------------------ ---------- ---------- ---------- ------------------------------ ---------- ----------
AAAAACAABAAAXJFAAQ 302306 1 1 DEPTNO 1 0
AAAAACAABAAAXJFAAR 302306 2 2 SYS_C00002_15043010:08:49$ 2 0
AAAAACAABAAAXJFAAS 302306 3 3 LOC 3 0
commit;
SYS@test> select * from scott.tt;
select * from scott.tt
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4299
Session ID: 580 Serial number: 11
--还不行.
SYS@test> select rowid,obj#,cols from sys.tab$ where obj# in (select object_id from dba_objects where owner='SCOTT' and object_name='TT');
ROWID OBJ# COLS
------------------ ---------- ----------
AAAAACAABAAAXJFAAJ 302306 2
update sys.tab$ set cols=3 where rowid='AAAAACAABAAAXJFAAJ';
commit ;
update sys.col$ set col#=2 ,property=0,name='DNAME' where rowid='AAAAACAABAAAXJFAAR';
commit ;
alter system flush shared_pool;
--补充1点,刷新无效,我重启才ok的.看来要一次改对,也许有一些东西在共享池子没有清楚干净.
SYS@test> select * from scott.tt;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS1
30 SALES CHICAGO
40 OPERATIONS BOSTON
--注意,千万不要在生产系统做这个测试!!!!