Recycle Bin and Flashback Drop

This test shows the commands to flashback drop a table

Env: Oracle 10gR2

(1) Table structure

scott@TOY10G> desc t1

Name Null? Type

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

OWNER NOT NULL VARCHAR2(30)

OBJECT_NAME NOT NULL VARCHAR2(30)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NOT NULL NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED NOT NULL DATE

LAST_DDL_TIME NOT NULL DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

(2) Drop the table

scott@TOY10G> drop table t1;

Table dropped.

(3) Object in the recyclebin

scott@TOY10G> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

T1 BIN$N69b2mR+VDHgRAADupTRjg==$0 TABLE 2007-08-14:15:05:11

(4) We can query recycle bin name

scott@TOY10G> ;

1 select object_name from "BIN$N69b2mR+VDHgRAADupTRjg==$0"

2* where rownum < 10

scott@TOY10G> /

OBJECT_NAME

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

CON$

I_COL2

I_USER#

C_TS#

I_OBJ#

I_CON2

IND$

BOOTSTRAP$

UET$

9 rows selected.

(5) Flash back drop and rename the table

scott@TOY10G> flashback table "BIN$N69b2mR+VDHgRAADupTRjg==$0" to before drop rename to t3;

Flashback complete.

(6) New table contents

scott@TOY10G> select object_name from t3

where rownum < 10 2

3 /

OBJECT_NAME

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

CON$

I_COL2

I_USER#

C_TS#

I_OBJ#

I_CON2

IND$

BOOTSTRAP$

UET$

9 rows selected.

(7) Recyclebin is clean now

scott@TOY10G> show recyclebin;

scott@TOY10G>