Oracle閃回誤刪的表
文章版權所有Jusin Hao（luckyfriends），支援原創，轉載請註明。
1.1. 閃回誤刪的表
開發誤刪除了可能還要用的表，想要找回該表；
1.1.1. 查詢刪除的表是否還在回收站SQL> conn LSMVAS/********
Connected.
SQL> set linesize 200
SQL> set feedback off pagesize 0 tab off
SQL> select * from tab;
LS_PAYCITY_INFO TABLE
ACCOUNT_USER_INFO TABLE
LS_VIRTUAL_ACCOUNT TABLE
LS_VIRTUAL_ACCOUNT_DETAIL TABLE
VA_BUSINESS_LOG TABLE
VA_FUND_FROZEN TABLE
VA_STATE_RECORD TABLE
VA_TRADE_ORDER TABLE
LS_ACCOUNT_DETAIL_201401151411 TABLE
LS_VIRTUAL_ACCOUNT_1401151508 TABLE
BIN$7ryO1NH1ffbgQKgKUQQoGQ==$0 TABLE
VA_TRADE_ORDER_CW TABLE
BIN$7ryO1NH4ffbgQKgKUQQoGQ==$0 TABLE
BIN$7ryO1NH6ffbgQKgKUQQoGQ==$0 TABLE
BIN$7ryO1NH7ffbgQKgKUQQoGQ==$0 TABLE
VA_ACCOUNT_INFO TABLE
KUAIQIAN_LOGS TABLE
BIN$8GQ12Ejk64fgQKgKUQQbvA==$0 TABLE
VA_TRADE_ORDER_MODIFY_LOG TABLE
KUAIQIAN_LOGS_BANK TABLE
BIN$7+t3EHNFWvHgQKgKUQQu8Q==$0 TABLE
LS_ACCOUNT_INFO TABLE
ITEMPAYIDNUMBER TABLE
VA_TRADE_ORDER_MODIFY_DATA TABLE
VA_TRADE_ORDER_LOG TABLE
CUX_ACCOUNT_LIST TABLE
ITEM_CONTRACT_NUMBER TABLE
V_PROCESS_FLAG_19 TABLE
BIN$7UBlrDGmucTgQKgKUQR4NQ==$0 TABLE
BIN$7UBlrDGlucTgQKgKUQR4NQ==$0 TABLE
BIN$7UBlrDGhucTgQKgKUQR4NQ==$0 TABLE
BIN$7U4oRKSRA1/gQKgKUQQRbQ==$0 TABLE
BIN$7VOTqsHUKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHTKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHWKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHVKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHXKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHYKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHZKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHaKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHcKYfgQKgKUQQYaA==$0 TABLE
BIN$7VOTqsHbKYfgQKgKUQQYaA==$0 TABLE
BIN$8KDhJE22qF7gQKgKUQRx5A==$0 TABLE
BIN$8KDhJE24qF7gQKgKUQRx5A==$0 TABLE
VA_TRADE_ORDER_040219 TABLE
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
LS_VIRTUAL_ACCOUNT_1401151508 BIN$89oFjnwg1JjgQKgKUQRnmg==$0 TABLE 2014-03-05:17:55:33
LS_ACCOUNT_DETAIL_201401151411 BIN$89oFjnwe1JjgQKgKUQRnmg==$0 TABLE 2014-03-05:17:55:33
ACCOUNT_NUM_SP_I BIN$7ryO1NH6ffbgQKgKUQQoGQ==$0 TABLE 2013-12-30:15:41:40
D
BALANCE_BY_ACOUN BIN$7ryO1NH7ffbgQKgKUQQoGQ==$0 TABLE 2013-12-30:15:41:40
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHcKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:25:06
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHbKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:17:39
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHaKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:13:47
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHZKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:12:07
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHYKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:11:02
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHXKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:10:24
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHWKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:09:07
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHVKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:02:54
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHUKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:01:29
TNUMBER
BALANCE_BY_ACOUN BIN$7UBlrDGmucTgQKgKUQR4NQ==$0 TABLE 2013-12-11:18:07:59
TNUMBER
BALANCE_BY_ACOUN BIN$7UBlrDGhucTgQKgKUQR4NQ==$0 TABLE 2013-12-11:18:07:31
TNUMBER
BALANCE_KW BIN$7VOTqsHTKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:00:27
BALANCE_KW BIN$7U4oRKSRA1/gQKgKUQQRbQ==$0 TABLE 2013-12-12:10:32:30
BALANCE_KW BIN$7UBlrDGlucTgQKgKUQR4NQ==$0 TABLE 2013-12-11:18:07:46
BALANCE_KW_20131 BIN$7ryO1NH1ffbgQKgKUQQoGQ==$0 TABLE 2013-12-30:15:40:29
219
JS_CIB_HISTORY BIN$7+t3EHNFWvHgQKgKUQQu8Q==$0 TABLE 2014-01-14:17:03:25
LS_VIRTUAL_ACCOU BIN$8KDhJE24qF7gQKgKUQRx5A==$0 TABLE 2014-01-23:17:29:57
NT_BACK
LS_VIRTUAL_ACCOU BIN$8KDhJE22qF7gQKgKUQRx5A==$0 TABLE 2014-01-23:17:29:34
NT_MODIFY_LOG
LVA1 BIN$7ryO1NH4ffbgQKgKUQQoGQ==$0 TABLE 2013-12-30:15:40:49
TEST BIN$8GQ12Ejk64fgQKgKUQQbvA==$0 TABLE 2014-01-20:17:06:42
SQL>select original_name,object_name,type,ts_name,droptime,related,space from user_recyclebin where can_undrop='YES';
注：以上查詢的要在刪除物件所屬使用者才能看到，如果是用system使用者連線則show recyclebin、user_recyclebin、select * from tab看不到相關資訊）
---檢視詳細的的物件回收資訊（如下是用system使用者連線）
select original_name,object_name,type,ts_name,droptime,related,space from dba_recyclebin where can_undrop='YES';
![clip_image002[4]](http://img.blog.itpub.net/blog/attachment/201403/7/14710393_1394164533LdtD.jpg "clip_image002[4]")
----根據上面的查詢意外刪除的表還在回收站，我們執行閃回
SQL> flashback table LS_ACCOUNT_DETAIL_201401151411 to before drop;
SQL> flashback table LS_VIRTUAL_ACCOUNT_1401151508 to before drop;
---檢視閃回後的回收站，發現兩張表已經不再回收站了
SQL> conn LSMVAS/********
SQL> select original_name,object_name,type,ts_name,droptime,related,space from user_recyclebin where can_undrop='YES';
BALANCE_BY_ACOUNTNUMBER BIN$7UBlrDGhucTgQKgKUQR4NQ==$0 TABLE USERS 2013-12-11:18:07:31 108052 768
BALANCE_KW BIN$7UBlrDGlucTgQKgKUQR4NQ==$0 TABLE USERS 2013-12-11:18:07:46 108051 12288
BALANCE_BY_ACOUNTNUMBER BIN$7UBlrDGmucTgQKgKUQR4NQ==$0 TABLE USERS 2013-12-11:18:07:59 108053 768
BALANCE_KW BIN$7U4oRKSRA1/gQKgKUQQRbQ==$0 TABLE USERS 2013-12-12:10:32:30 108054 12288
BALANCE_KW BIN$7VOTqsHTKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:00:27 108156 14336
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHUKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:01:29 108055 768
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHVKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:02:54 108167 768
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHWKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:09:07 108168 768
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHXKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:10:24 108169 768
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHYKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:11:02 108170 768
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHZKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:12:07 108171 768
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHaKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:13:47 108172 768
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHbKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:17:39 108177 768
BALANCE_BY_ACOUNTNUMBER BIN$7VOTqsHcKYfgQKgKUQQYaA==$0 TABLE USERS 2013-12-12:17:25:06 108178 768
BALANCE_KW_20131219 BIN$7ryO1NH1ffbgQKgKUQQoGQ==$0 TABLE USERS 2013-12-30:15:40:29 108166 13312
LVA1 BIN$7ryO1NH4ffbgQKgKUQQoGQ==$0 TABLE USERS 2013-12-30:15:40:49 101522 1536
ACCOUNT_NUM_SP_ID BIN$7ryO1NH6ffbgQKgKUQQoGQ==$0 TABLE USERS 2013-12-30:15:41:40 72257 512
BALANCE_BY_ACOUNTNUMBER BIN$7ryO1NH7ffbgQKgKUQQoGQ==$0 TABLE USERS 2013-12-30:15:41:40 108179 896
JS_CIB_HISTORY BIN$7+t3EHNFWvHgQKgKUQQu8Q==$0 TABLE USERS 2014-01-14:17:03:25 111024 8
TEST BIN$8GQ12Ejk64fgQKgKUQQbvA==$0 TABLE USERS 2014-01-20:17:06:42 111539 8
LS_VIRTUAL_ACCOUNT_MODIFY_LOG BIN$8KDhJE22qF7gQKgKUQRx5A==$0 TABLE USERS 2014-01-23:17:29:34 54887 8
LS_VIRTUAL_ACCOUNT_BACK BIN$8KDhJE24qF7gQKgKUQRx5A==$0 TABLE USERS 2014-01-23:17:29:57 64100 8
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
ACCOUNT_NUM_SP_I BIN$7ryO1NH6ffbgQKgKUQQoGQ==$0 TABLE 2013-12-30:15:41:40
D
BALANCE_BY_ACOUN BIN$7ryO1NH7ffbgQKgKUQQoGQ==$0 TABLE 2013-12-30:15:41:40
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHcKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:25:06
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHbKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:17:39
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHaKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:13:47
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHZKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:12:07
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHYKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:11:02
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHXKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:10:24
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHWKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:09:07
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHVKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:02:54
TNUMBER
BALANCE_BY_ACOUN BIN$7VOTqsHUKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:01:29
TNUMBER
BALANCE_BY_ACOUN BIN$7UBlrDGmucTgQKgKUQR4NQ==$0 TABLE 2013-12-11:18:07:59
TNUMBER
BALANCE_BY_ACOUN BIN$7UBlrDGhucTgQKgKUQR4NQ==$0 TABLE 2013-12-11:18:07:31
TNUMBER
BALANCE_KW BIN$7VOTqsHTKYfgQKgKUQQYaA==$0 TABLE 2013-12-12:17:00:27
BALANCE_KW BIN$7U4oRKSRA1/gQKgKUQQRbQ==$0 TABLE 2013-12-12:10:32:30
BALANCE_KW BIN$7UBlrDGlucTgQKgKUQR4NQ==$0 TABLE 2013-12-11:18:07:46
BALANCE_KW_20131 BIN$7ryO1NH1ffbgQKgKUQQoGQ==$0 TABLE 2013-12-30:15:40:29
219
JS_CIB_HISTORY BIN$7+t3EHNFWvHgQKgKUQQu8Q==$0 TABLE 2014-01-14:17:03:25
LS_VIRTUAL_ACCOU BIN$8KDhJE24qF7gQKgKUQRx5A==$0 TABLE 2014-01-23:17:29:57
NT_BACK
LS_VIRTUAL_ACCOU BIN$8KDhJE22qF7gQKgKUQRx5A==$0 TABLE 2014-01-23:17:29:34
NT_MODIFY_LOG
LVA1 BIN$7ryO1NH4ffbgQKgKUQQoGQ==$0 TABLE 2013-12-30:15:40:49
TEST BIN$8GQ12Ejk64fgQKgKUQQbvA==$0 TABLE 2014-01-20:17:06:42
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-1102953/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 閃回刪除表原理分析Oracle
- Oracle閃回刪除恢復誤刪資料Oracle
- oralce恢復誤刪除的表中的資料（閃回、閃回查詢）
- Oracle閃回刪除Oracle
- 閃回刪除、閃回查詢
- Oracle 11g 閃回刪除Oracle
- 閃回查詢恢復誤刪資料
- 閃回查詢找到誤刪除資料
- Oracle 11g 閃回表Oracle
- 閃回表、閃回查詢
- 循序漸進oracle第8章：Oracle的閃回特性之恢復刪除表Oracle
- 閃回技術二：閃回表
- 使用閃回查詢恢復誤刪除的資料
- 基本閃回查詢和閃回表
- oracle閃回Oracle
- oracle 閃回Oracle
- 利用undo的閃回特性恢復錯誤操作的表
- 10g裡的閃回表命令-- 表的刪除和恢復
- Oracle 10G 新特性——閃回表Oracle 10g
- 閃回 錯誤的DML 操作
- 使用oracle 閃回查詢找回誤更新的資料Oracle
- 閃回查詢之閃回表查詢
- oracle 誤刪表 drop tableOracle
- 閃回刪除技術：
- 循序漸進oracle第8章：Oracle的閃回特性之恢復truncate刪除表的資料Oracle
- 【備份恢復】 閃回技術之閃回刪除
- oracle 閃回 flashbackOracle
- oracle閃回特性Oracle
- Oracle回收站表閃回機制研究Oracle
- Oracle Database 10g新特性-閃回表OracleDatabase
- oracle的閃回查詢Oracle
- oracle的回閃查詢Oracle
- 使用 Oracle Database 10g中的閃回表特性OracleDatabase
- 閃回表技術
- 關於oracle 9i的閃回的一個錯誤Oracle
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- Oracle閃回查詢恢復delete刪除資料Oracledelete
- 2 Day DBA-管理方案物件-練習：參考“執行閃回表操作”練習，閃回刪除RegionsHist表。物件