這個小文兒是對《【實戰】使用“基表修改法”恢復被“set unused”方式誤刪除的列》的補充
文章地址：http://space.itpub.net/?uid-519536-action-viewspace-itemid-616174

因為在實驗中我們是模擬的故障場景，並且提前記錄了幾張核心表中的資料，也可以使用下面這種“簡單粗暴”的方法進行恢復

1.檢視實驗表資料
sec@ora10g> select * from t;

         X          Y
---------- ----------
         1          1
         2          2
         3          3

2.刪除x列
sec@ora10g> alter table sec.t set unused column x cascade constraints;

Table altered.

3.刪除x列後的效果
sec@ora10g> select * from t;

         Y
----------
         1
         2
         3

4.在SYS使用者下使用事先準備的指令碼重新初始化obj$基表（這一步驟不是必須的）
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> delete from obj$ where OBJ#=16416;

1 row deleted.

sys@ora10g> Insert into SYS.OBJ$
2     (OBJ#, DATAOBJ#, OWNER#, NAME, NAMESPACE,
3      SUBNAME, TYPE#, CTIME, MTIME, STIME,
4      STATUS, REMOTEOWNER, LINKNAME, FLAGS, OID$,
5      SPARE1, SPARE2, SPARE3, SPARE4, SPARE5,
6      SPARE6)
7   Values
8     (16416, 16416, 40, 'T', 1,
9      NULL, 2, TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'),
10      1, NULL, NULL, 0, NULL,
11      6, 1, NULL, NULL, NULL,
12      NULL);

1 row created.

5.在SYS使用者下使用事先準備的指令碼重新初始化tab$基表
sys@ora10g> delete from TAB$ where OBJ#=16416;

1 row deleted.

sys@ora10g> Insert into SYS.TAB$
2     (OBJ#, DATAOBJ#, TS#, FILE#, BLOCK#,
3      BOBJ#, TAB#, COLS, CLUCOLS, PCTFREE$,
4      PCTUSED$, INITRANS, MAXTRANS, FLAGS, AUDIT$,
5      ROWCNT, BLKCNT, EMPCNT, AVGSPC, CHNCNT,
6      AVGRLN, AVGSPC_FLB, FLBCNT, ANALYZETIME, SAMPLESIZE,
7      DEGREE, INSTANCES, INTCOLS, KERNELCOLS, PROPERTY,
8      TRIGFLAG, SPARE1, SPARE2, SPARE3, SPARE4,
9      SPARE5, SPARE6)
10   Values
11     (16416, 16416, 5, 5, 11,
12      NULL, NULL, 2, NULL, 10,
13      40, 1, 255, 1073741825, '--------------------------------------',
14      NULL, NULL, NULL, NULL, NULL,
15      NULL, NULL, NULL, NULL, NULL,
16      NULL, NULL, 2, 2, 536870912,
17      0, 736, NULL, NULL, NULL,
18      NULL, TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

6.在SYS使用者下使用事先準備的指令碼重新初始化obj$基表
sys@ora10g> delete from COL$ where OBJ#=16416;

2 rows deleted.

sys@ora10g> Insert into SYS.COL$
2     (OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
3      NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
4      SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
5      PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
6      SPARE3, SPARE4, SPARE5, SPARE6)
7   Values
8     (16416, 1, 1, 22, 0,
9      'X', 2, 22, 0, NULL,
10      NULL, 0, NULL, NULL, 1,
11      0, 0, 0, 0, 0,
12      0, NULL, NULL, NULL);

1 row created.

sys@ora10g> Insert into SYS.COL$
2     (OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
3      NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
4      SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
5      PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
6      SPARE3, SPARE4, SPARE5, SPARE6)
7   Values
8     (16416, 2, 2, 22, 0,
9      'Y', 2, 22, 0, NULL,
10      NULL, 0, NULL, NULL, 2,
11      0, 0, 0, 0, 0,
12      0, NULL, NULL, NULL);

1 row created.

sys@ora10g> commit;

Commit complete.

7.重新啟動資料庫
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> startup force;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2074152 bytes
Variable Size             486541784 bytes
Database Buffers         1644167168 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

8.重現表t的原有風貌
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select * from t;

         X          Y
---------- ----------
         1          1
         2          2
         3          3

9.恢復完成，這個小文兒只是一個補充，詳細的常規恢復內容敬請參見《【實戰】使用“基表修改法”恢復被“set unused”方式誤刪除的列》http://space.itpub.net/?uid-519536-action-viewspace-itemid-616174

-- The End --

【實戰】使用“基表修改法”恢復被“set unused”方式誤刪除的列（續）

相關文章