New Features of Oracle10g flashback drop

In previous versions, except incomplete restoration, there is usually no good solution. Currently, Oracle10g provides the new flashback drop feature to accelerate the recovery of user error operations.

The flashback drop function allows you to restore a dropped object from the current database. When you perform the drop operation, Oracle does not delete the object but automatically puts the object into the recycle bin. Deleting an object is actually a simple re-order name operation.

The recycle bin is a virtual container used to store all deleted objects. In the recycle bin, the deleted object occupies the same space during creation. You can even query the deleted table or use the flashback function to restore it, this is the flashback drop function.

The information in the recycle bin can be obtained from views such as recyclebin/user_recyclebin/dba_recyclebin, or through the show recyclebin command of SQL * Plus.

Sys as sysdba on 29-MAR-05> connect eygle/eygle
Connected.

EYGLE on 29-MAR-05> create table t1 as select * from dba_users;

Table created.

EYGLE on 29-MAR-05> drop table t1;

Table dropped.

EYGLE on 29-MAR-05> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------------------------------------------------------------
T1 BIN $83T2 + h92CJvgNAgAIMR + 0Q = $0 TABLE 2005-03-29: 21: 52: 00
EYGLE on 29-MAR-05> flashback table t1 to before drop;

Flashback complete.
EYGLE on 29-MAR-05> desc t1
Name Null? Type
------------------------------------------------------------
Username not null VARCHAR2 (30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2 (30)
ACCOUNT_STATUS not null VARCHAR2 (32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE not null VARCHAR2 (30)
TEMPORARY_TABLESPACE not null VARCHAR2 (30)
CREATED NOT NULL DATE
Profile not null VARCHAR2 (30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 (30)
EXTERNAL_NAME VARCHAR2 (4000)

EYGLE on 29-MAR-05> show recyclebin;

If you want to completely clear these objects, you can use the Purge Command, as shown in the following example:

EYGLE on 29-MAR-05> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------------------------------
T bin $29 ycNBfhan/gNAgAIMR + 0Q = $0 TABLE 2004-06-02: 15: 26: 35
EYGLE on 29-MAR-05> purge table t;

Table purged.

EYGLE on 29-MAR-05> show recyclebin;

Note: You can use "purge recyclebin" to clear all objects in the recycle bin.

You can also clear different recycle bin objects through purge user_recyclebin or purge dba_recyclebin.

Run the purge tablespace tsname and purge tablespace tsname user username commands to clear the recycle bin.

If you want to completely delete a table and do not want to put it in the recycle bin, you can add the purge option in the drop statement, for example:

Drop table tablename purge

Note: The Drop operation of sysdba is not recorded. Oracle never recommends that you use sysdba to create user objects.