9 Replies Latest reply: Apr 22, 2014 6:08 AM by Fran RSS

    Recyclebin not working as expected

    2638616

      Could you anyone give any kind of solution as to why the recycle bin is not working properly .

      Below is the SQL

       

      Oracle 10g VERSION  - Oracle version

      Linux RedHat   - Operating System

      Flashback is On

      Archivelog Mode

       

      SQL> SHOW PARAMETER RECYCLEBIN;

       

      NAME                                 TYPE        VALUE

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

      recyclebin                           string            ON

       

      SQL> CREATE TABLE T1(AGE NUMBER);

       

      Table created.

       

      SQL> DROP TABLE T1;

            

              Table dropped.

       

      SQL> FLASHBACK TABLE T1 TO BEFORE DROP;

       

      FLASHBACK TABLE T1 TO BEFORE DROP

      *ERROR at line 1:

      ORA-38305: object not in RECYCLE BIN

       

      SQL> SHOW RECYCLEBIN;    WHEN I EXECUTED THIS STATEMENT NOTHING IS HAPPENING  IT JUST PASSES TO NEXT SQL PROMPT

       

      SQL> SELECT * FROM USER_RECYCLEBIN;

       

          no rows selected

       

      SQL> SELECT * FROM DBA_RECYCLEBIN;

      no rows selected

       

       

      Thanks

      Any kind of information is highly appreciable

        • 1. Re: Recyclebin not working as expected
          Hoek

          Are you perhaps doing this in the SYSTEM or SYS schema?

          • 2. Re: Recyclebin not working as expected
            Top.Gun

            Can you insert rows before dropping the table?

            • 3. Re: Recyclebin not working as expected
              Jjun.Tan

              Hi,

               

              On top of the feedbacks provided, how about the space constraints? Or auto purge enabled? Automatic Purging of Recyclebin

               

              Hope it helps,

              Warmest Regards,

              Jjun

              • 4. Re: Recyclebin not working as expected
                spiderwoman

                "

                SQL> CREATE TABLE T1(AGE NUMBER);

                 

                Table created.

                 

                SQL>

                "

                 

                Did you insert data?  You may want to do following after the drop to check recyclebin.:

                 

                SELECT object_name, original_name, createtime FROM recyclebin;  

                • 5. Re: Recyclebin not working as expected
                  Emad Al-Mousa

                  Hi,

                   

                  purge your recycle bin.

                   

                  SQL> PURGE RECYCLEBIN;

                   

                  Also, Recycle functionality is available only for non-system locally-managed table spaces.So, Recyclebin does not work for SYS objects.

                   

                  Hope This Helps.

                   

                  Regards,

                  • 6. Re: Recyclebin not working as expected
                    Aman....

                    In addition to confirming that you are not using SYS user or not storing the objects in the System tablespace,  also check that the parameter RECYCLEBIN=ON.

                     

                    Aman....

                    • 7. Re: Recyclebin not working as expected
                      2638616

                      Folks

                      I created a User U1 and his default tablespace is USERS.

                      So I login as U1 .

                      Space was not a issue because I am doing in my Home computer which has a lot space.

                      Still I am unable to figure this out.

                      I am very much aware that SYS user cannot perform flashback.

                      Thanks for u all information.

                      Please let me know if there is anything else I need to check.

                      I did insert data in the table like 3 rows

                      • 8. Re: Recyclebin not working as expected
                        Warrior25

                        Please check the 2 scenarios:

                         

                        ########### Scenario 1 ##############################

                        SQL> create table test (n number(10));

                        Table created.

                         

                        SQL> drop table test;

                        Table dropped.

                        SQL>

                        SQL>

                        SQL> sho parameter recycle

                         

                        NAME                                 TYPE        VALUE

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

                        buffer_pool_recycle                  string

                        db_recycle_cache_size                big integer 0

                        recyclebin                           string      on

                         

                        SQL>

                        SQL> sho recyclebin

                        SQL>

                        SQL> sho user

                        USER is "SYS"

                         

                        SQL> select * from dba_recyclebin;

                         

                        no rows selected

                         

                        SQL>

                         

                        ########### Scenario 2 ##############################

                        SQL> conn scott

                        Enter password:

                        Connected.

                        SQL>

                        SQL> create table test (n number(10));

                         

                        Table created.

                         

                        SQL> drop table test;

                         

                        Table dropped.

                         

                         

                        SQL> sho recyclebin

                        ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

                        TEST             BIN$dJ1G+BRfTOOxWiM4s6eSzA==$0 TABLE        2014-04-22:14:53:06

                        SQL>

                        SQL> sho user

                        USER is "SCOTT"

                        SQL>

                        SQL> select * from test;

                        select * from test

                                      *

                        ERROR at line 1:

                        ORA-00942: table or view does not exist

                         

                         

                         

                         

                        SQL> flashback table test to before drop;

                         

                         

                        Flashback complete.

                         

                         

                        SQL> select * from test;

                         

                         

                        no rows selected

                         

                         

                        SQL> sho recyclebin

                        SQL>

                         

                        After testing the above kindly mark this as correct answer so that it will be beneficial for the future reference.

                        • 9. Re: Recyclebin not working as expected
                          Fran

                          @user10981572 told you the answer and you ignored him


                          what happend if you run "select owner, object_name from dba_recyclebin" as sys user?


                          when you run "sho recyclebin" you are running "user_recyclebin" so you'll never see the recyclebin of other user unless you use dba_recyclebin.