[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

--注意,千万不要在生产系统做这个测试!!!!