Empty Recyclebin

[julian]

It's not only Windows that has a recyclebin, Oracle too :-)

SQL> conn / as sysdba
Connected.
SQL> PURGE RECYCLEBIN;

Recyclebin purged.

SQL> select * from DBA_RECYCLEBIN;

OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
ICO BIN$1UcUVuC1XO3gNAgAIPzwew==$0
HUGO DROP TABLE
USERS 2004-03-10:14:23:58 2004-03-10:14:31:35

OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
1536397 YES YES 12004 12004
12004 128


OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
ICO BIN$1UcUVuC2XO3gNAgAIPzwew==$0
HUGO DROP TABLE
HUGO 2004-03-10:14:37:54 2004-03-10:14:39:58

OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
1537569 YES YES 12089 12089
12089 128


SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL> select * from DBA_RECYCLEBIN;

no rows selected

SQL>

[zainalj]

what is the function of recyclebin ?

[davey23uk]

same as in windows - to get back something you deleted by accident.

Which a quick search of the docs would have found out

http://download-west.oracle.com/docs...htm#sthref2010

[zainalj]

emmm...
1.did data on the drop table also being kept at recyclebin?
2. I guess, cyclebin is using system tbs, therefore what will happen if there is not enough room in system tbs when we try to drop some table? is oracle hit an error or some cyclebin will purge automatically?

[davey23uk]

read the docs - the space is still taken up in the tablespace the object was in. It isn't released until you purge the recycle bin or you do drop table xx purge.

Its all spelled out in the docs for you to read

[julian]

I found out that there is a way to get the data even after a truncate on the table but you must mount the database in order to flash it back.

[abhaysk]

Originally posted by julian
I found out that there is a way to get the data even after a truncate on the table but you must mount the database in order to flash it back.

That may be possible, if the freed extent/s is/are not allocated to any other object..

[julian]

Originally posted by abhaysk
That may be possible, if the freed extent/s is/are not allocated to any other object..

Yep, I did flashback at once after the TRUNCATE.