This discussion is archived
6 Replies Latest reply: Feb 11, 2013 12:06 PM by spur230 RSS

Recycle bin maintenance

spur230 Newbie
Currently Being Moderated
I have few confusion regarding recycle bin. I could not find relevant information in documentation

Does oracle automatically purge objects in recycle bin?

If yes, under what condition does it purge?

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.

Thanks.
  • 1. Re: Recycle bin maintenance
    TSharma-Oracle Guru
    Currently Being Moderated
    Oracle does not automatically purge recyclebin. if it would do , it would defeat the purpose of having recyclebin. You will have to manually clean the recycle bin OR create an automated job to clean. Very unlikely they are purged by database due to space constraints.

    You can also DISABLE the recyclebin.

    ALTER SYSTEM SET recyclebin = OFF;
    OR
    ALTER SESSION SET recyclebin = OFF;( session level).

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#i1011357
  • 2. Re: Recycle bin maintenance
    phaeus Pro
    Currently Being Moderated
    Hello,
    it is purge from a user session or when the database detects a low space condition.

    From the documentation:
    The recycle bin is a data dictionary table containing information about dropped objects. Dropped objects and any dependent objects (such as indexes, constraints, nested tables, and so on) are not removed and still occupy space until you purge them from the recycle bin or until they are automatically purged by the database when available space becomes low.
    Also this Thread may help you

    Automatic Purging of Recyclebin


    regards
    Peter
  • 3. Re: Recycle bin maintenance
    EdStevens Guru
    Currently Being Moderated
    spur230 wrote:
    I have few confusion regarding recycle bin. I could not find relevant information in documentation

    Does oracle automatically purge objects in recycle bin?

    If yes, under what condition does it purge?

    I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.

    Thanks.
    TSharma says "Oracle does not automatically purge recyclebin"

    phaeus says "it is purge from a user session or when the database detects a low space condition."


    ;-)
  • 4. Re: Recycle bin maintenance
    spur230 Newbie
    Currently Being Moderated
    phaeus wrote:
    Hello,
    it is purge from a user session or when the database detects a low space condition.

    From the documentation:
    The recycle bin is a data dictionary table containing information about dropped objects. Dropped objects and any dependent objects (such as indexes, constraints, nested tables, and so on) are not removed and still occupy space until you purge them from the recycle bin or until they are automatically purged by the database when available space becomes low.
    Also this Thread may help you

    Automatic Purging of Recyclebin


    regards
    Peter
    Thanks Peter.

    I also found following at http://oit.scps.nyu.edu/~pallerm/11g/les_11_fl2.pdf

    The space used by recycle bin objects is never automatically reclaimed unless there is
    space pressure. This enables you to recover recycle bin objects for the maximum possible duration.
  • 5. Re: Recycle bin maintenance
    phaeus Pro
    Currently Being Moderated
    Hello,
    no problem, one thing i have not seen that the last object in the recyclebin is purged.

    For example if i create a table test3 in a tablespace.

    First i have two objects in the recyclebin.
    SQL> select OBJECT_NAME,CAN_UNDROP,CAN_PURGE from user_recyclebin;
    
    OBJECT_NAME                    CAN CAN
    ------------------------------ --- ---
    BIN$1XkLxpYUxE/gQKjBaRkb4Q==$0 YES YES
    BIN$1XlPvX+3LRbgQKjBaRkcAQ==$0 YES YES
    Then i do some inserts until a ORA Code is raised.
      1* insert into test3 select * from test3
    insert into test3 select * from test3
    *
    ERROR at line 1:
    ORA-01653: unable to extend table HR.TEST3 by 128 in tablespace TEST
    The database has pruged one object from the recyclebin but not both.
    SQL> select OBJECT_NAME,CAN_UNDROP,CAN_PURGE from user_recyclebin;
    
    OBJECT_NAME                 CAN CAN
    ------------------------------ --- ---
    BIN$1XkLxpYUxE/gQKjBaRkb4Q==$0 YES YES
    regards
    Peter
  • 6. Re: Recycle bin maintenance
    spur230 Newbie
    Currently Being Moderated
    Thanks for the example.

    This is what I was just reading
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9012.htm#SQLRF01802

    "When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table."

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points