ITPub博客

首页 > 数据库 > Oracle > Oracle闪回误删的表

Oracle闪回误删的表

原创 Oracle 作者:luckyfriends 时间:2014-03-07 11:55:45 0 删除 编辑

文章版权所有Jusin Haoluckyfriends),支持原创,转载请注明。

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]

1.1.2. 闪回以外删除的表

----根据上面的查询意外删除的表还在回收站,我们执行闪回

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
个人博客网 www.leosunny 长期从事数据库相关工作,涉及oracle,mysql, sqlsever,db2 , OGG, SSIS以及非关系数据库和大数据平台等。 希望一直分享一些个人案例和知识,和广大技术宅们共同进步。

注册时间:2009-06-06

  • 博文量
    468
  • 访问量
    3161463