oracle闪回表删除,Oracle闪回误删的表

最新推荐文章于 2022-11-09 09:28:41 发布

weixin_39727105

最新推荐文章于 2022-11-09 09:28:41 发布

阅读量111

点赞数

文章标签： oracle闪回表删除

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

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';

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

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>

weixin_39727105

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle闪回表删除,Oracle闪回误删的表

文章版权所有Jusin Hao(luckyfriends)，支持原创，转载请注明。1.1. 闪回误删的表开发误删除了可能还要用的表，想要找回该表；1.1.1. 查找删除的表是否还在回收站SQL> conn LSMVAS/********Connected.SQL> set linesize 200SQL> set feedback off pagesize 0 tab offSQ...
复制链接

扫一扫