本文共 3225 字，大约阅读时间需要 10 分钟。
[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 ProductionSCOTT@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 2update 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--注意,千万不要在生产系统做这个测试!!!!
转载地址：http://uxqya.baihongyu.com/