oracle11g回闪,oracle11G闪来flashback

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as hr@JIAGULUN

SQL> purge recyclebin;

Done

SQL> select * from tab;

TNAME                     TABTYPE  CLUSTERID

------------------------------ ------- ----------

ADDRESS                 TABLE

SQL> select * from address;

ID NAME

----------- --------------------

1 jiangxi

2 hunan

SQL> show recyclebin;

SQL> select * from user_recyclebin;--回收站并没有任何垃圾

OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ----------

----------- ------------ ----------

SQL> drop table address;--删除表

Table dropped

SQL> show recyclebin;

SQL> select * from user_recyclebin;--会发现回收站中出现了——表，索引垃圾

OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ----------

----------- ------------ ----------

BIN$DXVcPQqfU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE                 USERS                     2015-01-22:06:10:58 2015-01-24:22:49:08    3145534                                  YES     YES

77236       77236        77236          8

BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 PK_ADDRESS             DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:49:08    3145530                                  NO      YES         77236       77236        77239

8

BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 INDEX_ADDRESS_NAME DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:49:08    3145525                                  NO      YES         77236       77236        77240

8

SQL> select * from tab;--刚刚存在address表不见了，但是多了一张下面的表

TNAME                     TABTYPE  CLUSTERID

------------------------------ ------- ----------

BIN$DXVcPQqfU6HgUKjAA1tAfw==$0 TABLE

SQL> flashback table student  to before drop;

flashback table student  to before drop

ORA-38305: 对象不在回收站中

SQL> flashback table address  to before drop;--对回收站的表进行闪回

Done

SQL> select * from user_recyclebin;--回收站中的表被闪回了

OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ----------

----------- ------------ ----------

SQL> select * from tab;--表被闪回了

TNAME                     TABTYPE  CLUSTERID

------------------------------ ------- ----------

ADDRESS                 TABLE

SQL> drop table address;

Table dropped

SQL> create table address(id number(10));

Table created

SQL> insert into address values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> drop table address;

Table dropped

SQL> select * from user_recyclebin;--回收站中有两个address表

OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ----------

----------- ------------ ----------

BIN$DXVcPQqkU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE               USERS                     2015-01-24:22:51:52 2015-01-24:22:52:19    3145976                                  YES     YES

77424       77424        77424          8

BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE                USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145840                                  YES     YES

77236       77236        77236          8

BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145836                                  NO      YES         77236       77236

77239          8

BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145831                                  NO      YES         77236       77236

77240          8

SQL> flashback table address to before drop;--闪回表

Done

SQL> select * from user_recyclebin;

OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ----------

----------- ------------ ----------

BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE              USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145840                                  YES     YES

77236       77236        77236          8

BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145836                                  NO      YES         77236       77236

77239          8

BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145831                                  NO      YES         77236       77236

77240          8

SQL> select * from address;--被闪回的表示最晚送到回收站的那张表

ID

-----------

1

SQL>

SQL> drop table address purge;--删除表并不送入到回收站中

Table dropped

SQL> select * from user_recyclebin;

OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ----------

----------- ------------ ----------

BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE                USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145840                                  YES     YES

77236       77236        77236          8

BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145836                                  NO      YES         77236       77236

77239          8

BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145831                                  NO      YES         77236       77236

77240          8

SQL>

SQL> flashback table address to before drop;--闪回原来存在于回收站的表

Done

SQL> select * from user_recyclebin;

OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ----------

----------- ------------ ----------

SQL> select * from address;

ID NAME

----------- --------------------

1 jiangxi

2 hunan

SQL>