Oracle閃回刪除恢復誤刪資料

壹頁書發表於2013-11-19
Oracle
單位把程式做成了exe的安裝包。
安裝包在初始化資料庫的時候，會先drop所有的表，然後建表，最後插入資料。
在開發用的資料庫，一個同事加了一天班，給所有的表和欄位都加了註釋，估計是用於PD反向工程。
但是不湊巧，第二天另外一個同事執行安裝包直接把他的表覆蓋了...

大致過程如下，
同事A：
  1. create table a(a int);
  2. create table b(b int);
  3. create table c(c int);
  4. create table d(d int);
  5. create table e(e int);
  6. comment on column a.a is 'Test a';
  7. comment on column b.b is 'Test b';
  8. comment on column c.c is 'Test c';
  9. comment on column d.d is 'Test d';
  10. comment on column e.e is 'Test e';

第二天，同事B：
  1. drop table a;
  2. drop table b;
  3. drop table c;
  4. drop table d;
  5. drop table e;
  6. create table a(a int);
  7. create table b(b int);
  8. create table c(c int);
  9. create table d(d int);
  10. create table e(e int);
現在需要恢復同事A的註釋。

當時解決的過程如下
1.檢視回收站，發現表還在。

  1. SQL> select object_name,original_name,createtime from recyclebin;

  3. OBJECT_NAME ORIGINAL_NAME CREATETIME
  4. ------------------------------ -------------------------------- -------------------
  5. BIN$NmbxAasiR2CPSv0of/GyzA==$0 A 2013-11-19:21:19:28
  6. BIN$cyT3N5nJQMO85ho0DkpV9g==$0 B 2013-11-19:21:19:29
  7. BIN$fdrhQ5VtS0q7pZ5kT9tceA==$0 C 2013-11-19:21:19:29
  8. BIN$o8t+PJl3SpmRTh2gD1mUBQ==$0 D 2013-11-19:21:19:29
  9. BIN$RhLkOWtCTo+ls8QSwMxD9g==$0 E 2013-11-19:21:19:29
2.使用閃回刪除，恢復表。
在恢復的時候，將表加上字首。
（之所以沒有刪除當前的表，再將回收站的表恢復，是因為怕新刪除的表，直接將舊錶擠出回收站）
  1. SQL> select 'flashback table "'||object_name||'" to before drop rename to bak_'||original_name||';' from recyclebin;

  3. 'FLASHBACKTABLE"'||OBJECT_NAME||'"TOBEFOREDROPRENAMETOBAK_'||ORIGINAL_NAME||';'
  4. ---------------------------------------------------------------------------------------------------------------
  5. flashback table "BIN$NmbxAasiR2CPSv0of/GyzA==$0" to before drop rename to bak_A;
  6. flashback table "BIN$cyT3N5nJQMO85ho0DkpV9g==$0" to before drop rename to bak_B;
  7. flashback table "BIN$fdrhQ5VtS0q7pZ5kT9tceA==$0" to before drop rename to bak_C;
  8. flashback table "BIN$o8t+PJl3SpmRTh2gD1mUBQ==$0" to before drop rename to bak_D;
  9. flashback table "BIN$RhLkOWtCTo+ls8QSwMxD9g==$0" to before drop rename to bak_E;
3.刪除當前存在的表

  1. SQL> select 'drop table '||substr(table_name,5)||' purge;' from user_tables where table_name like 'BAK%';

  3. 'DROPTABLE'||SUBSTR(TABLE_NAME,5)||'PURGE;'
  4. ----------------------------------------------------------------------
  5. drop table A purge;
  6. drop table B purge;
  7. drop table C purge;
  8. drop table D purge;
  9. drop table E purge;
4.將恢復的表重新命名

  1. SQL> select 'alter table '|| table_name||' rename to '||substr(table_name,5)||';' from user_tables where table_name like 'BAK%';

  3. 'ALTERTABLE'||TABLE_NAME||'RENAMETO'||SUBSTR(TABLE_NAME,5)||';'
  4. ----------------------------------------------------------------------------------------------------------
  5. alter table BAK_A rename to A;
  6. alter table BAK_B rename to B;
  7. alter table BAK_C rename to C;
  8. alter table BAK_D rename to D;
  9. alter table BAK_E rename to E;
執行alter table命令之後，檢視註釋，已經恢復。

  1. SQL> select * from user_col_comments;

  3. TABLE_NAME COLUMN_NAME COMMENTS
  4. ------------------------------ ------------------------------ ----------
  5. A A Test a
  6. B B Test b
  7. C C Test c
  8. D D Test d
  9. E E Test e
後來發現還有一種更簡單的方法。
表被Drop之後，放入回收站，他的table_name被改名，
而註釋的table_name會和回收站的table_name保持一致。

  1. SQL> select * from user_col_comments;

  3. TABLE_NAME COLUMN_NAME COMMENTS
  4. ------------------------------ ------------------------------ ----------
  5. BIN$sKho5ULVTNOOG2eLJIufhA==$0 A Test a
  6. BIN$zxSn8/FpSZyllfe4evv3EQ==$0 B Test b
  7. BIN$Fc/XryYETrqEMfv0aMxY1g==$0 C Test c
  8. BIN$fVzUmlAeR72hOkHWH85z3g==$0 D Test d
  9. BIN$r5cZoCgSSxO4DLf8xrem/A==$0 E Test e

  11. SQL> select object_name,original_name,createtime from recyclebin;

  13. OBJECT_NAME ORIGINAL_NAME CREATETIME
  14. ------------------------------ -------------------------------- -------------------
  15. BIN$sKho5ULVTNOOG2eLJIufhA==$0 A 2013-11-19:21:19:28
  16. BIN$zxSn8/FpSZyllfe4evv3EQ==$0 B 2013-11-19:21:19:29
  17. BIN$Fc/XryYETrqEMfv0aMxY1g==$0 C 2013-11-19:21:19:29
  18. BIN$fVzUmlAeR72hOkHWH85z3g==$0 D 2013-11-19:21:19:29
  19. BIN$r5cZoCgSSxO4DLf8xrem/A==$0 E 2013-11-19:21:19:29
所以不用恢復回收站中的表，直接用回收站連線user_col_comments,生成註釋資訊即可。

  1. SQL> select 'comment on column '||recyclebin.original_name||'.'||user_col_comments.column_name||' is '''||user_col_comments.comments||''';' create_comments
  2.   2 from recyclebin
  3.   3 inner join user_col_comments
  4.   4 on user_col_comments.table_name=recyclebin.object_name
  5.   5 where recyclebin.type='TABLE';

  7. CREATE_COMMENTS
  8. ------------------------------------------------------------------------------------------------------------------------------------------------------------
  9. comment on column A.A is 'Test a';
  10. comment on column B.B is 'Test b';
  11. comment on column C.C is 'Test c';
  12. comment on column D.D is 'Test d';
  13. comment on column E.E is 'Test e';

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-777031/，如需轉載，請註明出處，否則將追究法律責任。

相關文章