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
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
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回收站表閃回機制研究Oracle
- 【RECO_ORACLE】Oracle閃回PDB的方法Oracle
- MySQL使用binlog2sql閃回誤刪除資料MySql
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- Oracle資料庫閃回Oracle資料庫
- MySQL誤刪資料？試試資料閃回工具binlog2sqlMySql
- Oracle 11G 閃回技術 使用Oracle閃回事務查詢Oracle
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- Oracle閃回技術--Flashback Version QueryOracle
- Oracle 閃回資料庫測試Oracle資料庫
- 【PDB】pdb閃回，Oracle還原點Oracle
- 詳解oracle資料庫閃回Oracle資料庫
- Flashback Drop閃回刪除功能實踐
- 【Flashback】Flashback Drop閃回刪除功能實驗
- Oracle資料庫閃回區空間不足Oracle資料庫
- Oracle drop分割槽表單個分割槽無法透過閃回恢復Oracle
- Oracle恢復誤刪資料Oracle
- [20180423]表空間閃回與snapshot standby
- Oracle閃回功能恢復偶然丟失的資料(轉)Oracle
- Oracle快速找回被刪除的表Oracle
- mysql資料誤刪後的資料回滾MySql
- ORACLE 閃回檢視v$flashback_database_log/statOracleDatabase
- DM7閃回與閃回查詢
- oracle truncate table recover(oracle 如何拯救誤操作truncate的表)Oracle
- Oracle閃回技術 概覽 應用程式開發功能Oracle
- Oracle 12.2新特性: PDB級閃回資料庫（Flashback PDB）Oracle資料庫
- 【FLASHBACK】Oracle閃回及回收站相關語句參考Oracle
- 刪使用者刪表空間的操作還能flashback回來嗎？
- 【Oracle 12c資料庫支援閃回庫功能】實驗Oracle資料庫
- 【Oracle】表空間誤刪除導致startup啟動時提示ORA-01110和ORA-01157錯誤Oracle
- linux下恢復誤刪除oracle的資料檔案LinuxOracle
- oracle 臨時表空間的增刪改查Oracle
- Oracle中刪除表中的重複資料Oracle
- [20180423]關於閃回表與主外來鍵約束.txt
- 2.6.3 指定閃回區
- ORACLE刪除-表分割槽和資料Oracle
- oracle級聯刪除使用者，刪除表空間Oracle
- 恢復Oracle資料庫誤刪除資料的語句Oracle資料庫