6 Replies Latest reply: Feb 11, 2013 2:06 PM by spur230 RSS

    Recycle bin maintenance

    spur230
      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
          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
            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
              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
                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
                  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
                    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."