Difference between OraclePurge and drop

The new flashbackdrop feature provided by Oracle10g provides the flashbackdrop feature to accelerate recovery of user error operations. In earlier versions,

The new flashback drop feature provided by Oracle 10g provides the flashback drop feature to speed up recovery of user error operations. In earlier versions,

Difference between Purge and drop:

The new flashback drop feature provided by Oracle 10g provides the flashback drop feature to speed up recovery of user error operations. In earlier versions, there was usually no good solution except incomplete restoration.

Oracle's 10 Gb flashback drop function allows you to restore a dropped object from the current database. During the drop operation, Oracle does not actually delete it now, instead, the object is automatically placed in the recycle bin. Deleting an object is simply a simple re-order operation.

The so-called 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.

C: \> sqlplus/nolog

SQL * Plus: Release 10.1.0.2.0-Production on Wednesday June 1 10:09:32 2005

Copyright (c) 1982,200 4, Oracle. All rights reserved.

SQL> conn tiger/tiger @ xe

Connected.

SQL> select count (*) from goodsinfo1;

COUNT (*)

----------

38997

SQL> drop table goodsinfo1;

The table has been deleted.

SQL> commit;

Submitted.

SQL> select count (*) from goodsinfo1;

Select count (*) from goodsinfo1

*

Row 3 has an error:

ORA-00942: table or view does not exist

Ah! Oh, my God! What should I do if I have deleted the wrong table? Ah! You can flash the database back to the time before the table was deleted.

No! Other operations will flash back together. Now we can use the flashback drop function.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

GOODSINFO1 BIN $ RFG58GsfRheKlVKnWw8KKQ = $0 TABLE: 10: 11: 03

SQL> FLASHBACK TABLE goodsinfo1 TO BEFORE DROP;

Flash back complete.

SQL> select count (*) from goodsinfo1;

COUNT (*)

----------

38997

Check that the deleted table is back. Thank God!

SQL> show recyclebin;

To completely clear these objects, use the Purge command, for example:

SQL> select count (*) from goodsinfo2;

COUNT (*)

----------

38997

SQL> drop table goodsinfo2;

The table has been deleted.

SQL> commit;

Submitted.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

GOODSINFO2 BIN $ BgSuEWMOSLOGZPcIc97O8w ==$ 0 TABLE: 10: 13: 18

SQL> purge table goodsinfo2;

The table has been cleared.

SQL> show recyclebin;

SQL>

Use purge recyclebin to clear all objects in the recycle bin.

Similarly, we can 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.

Ps: the user does not enter recycle bin after drop, so purge is not required.