DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Empty Recyclebin

  1. #1
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Empty Recyclebin

    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>
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  2. #2
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116
    what is the function of recyclebin ?

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  4. #4
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116
    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?

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width