11 Replies Latest reply: Feb 2, 2012 12:09 AM by redologger RSS

    can't seem to select or purge drop object from the dba_recyclebin

    redologger
      hi guys,

      as illustrated below:
      SQL> select droptime, type, object_name,ORIGINAL_NAME from dba_recyclebin;
      
      DROPTIME            TYPE                      OBJECT_NAME                    ORIGINAL_NAME
      ------------------- ------------------------- ------------------------------ -----------------------
      2012-02-01:11:31:40 INDEX                     BIN$oDYJzGWyTu2s+Ak7+2ZnwQ==$0 SYS_C007028
      2012-02-01:11:09:16 TABLE                     BIN$u0ri/NQiSdOXzztp2kbK5Q==$0 ORDERS
      2012-02-01:11:31:40 TABLE                     BIN$1g1eEwUqRcu9rlKvLMKd5Q==$0 TEST
      2012-02-01:11:09:16 INDEX                     BIN$tY/bcJiISfCi9fgyUGmXsg==$0 SYS_C007026
      
      SQL> select * from "BIN$1g1eEwUqRcu9rlKvLMKd5Q==$0";
      select * from "BIN$1g1eEwUqRcu9rlKvLMKd5Q==$0"
                    *
      ERROR at line 1:
      ORA-00942: table or view does not exist
        • 1. Re: can't seem to select or purge drop object from the dba_recyclebin
          sb92075
          flaskvacuum wrote:
          hi guys,

          as illustrated below:
          SQL> select droptime, type, object_name,ORIGINAL_NAME from dba_recyclebin;
          
          DROPTIME            TYPE                      OBJECT_NAME                    ORIGINAL_NAME
          ------------------- ------------------------- ------------------------------ -----------------------
          2012-02-01:11:31:40 INDEX                     BIN$oDYJzGWyTu2s+Ak7+2ZnwQ==$0 SYS_C007028
          2012-02-01:11:09:16 TABLE                     BIN$u0ri/NQiSdOXzztp2kbK5Q==$0 ORDERS
          2012-02-01:11:31:40 TABLE                     BIN$1g1eEwUqRcu9rlKvLMKd5Q==$0 TEST
          2012-02-01:11:09:16 INDEX                     BIN$tY/bcJiISfCi9fgyUGmXsg==$0 SYS_C007026
          
          SQL> select * from "BIN$1g1eEwUqRcu9rlKvLMKd5Q==$0";
          select * from "BIN$1g1eEwUqRcu9rlKvLMKd5Q==$0"
          *
          ERROR at line 1:
          ORA-00942: table or view does not exist
          what USER is issuing SQL?
          which schema owns objects in RECYCLEBIN?
          • 2. Re: can't seem to select or purge drop object from the dba_recyclebin
            CSM.DBA
            I guess, you are not trying with the user that owns the dropped object.

            Please find the below sequence of steps i performed to replicate the scenario.


            SQL> conn abc/abc
            Connected.


            SQL> create table a(b number);

            Table created.

            SQL> drop table a;

            Table dropped.



            DROPTIME TYPE OBJECT_NAME ORIGINAL_NAME
            ------------------- ------------------------- ------------------------------ --------------------------------
            2012-02-01:05:40:05 TABLE BIN$t+GDAgiYQTDgQ+tYHQp6hw==$0 A



            SQL> select * from "BIN$t+GDAgiYQTDgQ+tYHQp6hw==$0";

            no rows selected



            SQL> conn / as sysdba
            Connected.
            SQL> select * from "BIN$t+GDAgiYQTDgQ+tYHQp6hw==$0";
            select * from "BIN$t+GDAgiYQTDgQ+tYHQp6hw==$0"
            *
            ERROR at line 1:
            ORA-00942: table or view does not exist



            SQL> select * from abc."BIN$t+GDAgiYQTDgQ+tYHQp6hw==$0";

            no rows selected
            • 3. Re: can't seem to select or purge drop object from the dba_recyclebin
              redologger
              CSM.DBA wrote:
              I guess, you are not trying with the user that owns the dropped object.
              yes, you are right. Got to log in as the owner of the object inorder to issue the statement sucessfully.

              But my next question is, why can't me as the dba issue it un-sucessfully?
              • 4. Re: can't seem to select or purge drop object from the dba_recyclebin
                CSM.DBA
                if you have DBA privileges, you can access the object by qualifying it with the schema name or can create a synonym for that object in your schema and can access with out the qualifier.
                • 5. Re: can't seem to select or purge drop object from the dba_recyclebin
                  UweHesse
                  You just made a simple case more complex than necessary. Essentially, your case is

                  user a owns table t
                  why can't I as sys not do select * from t?

                  Because it is a's table and you as non-owner need to fully qualify the name: select * from a.t;

                  That has nothing to do with the recyclebin in the first place.

                  Kind regards
                  Uwe Hesse

                  http://uhesse.wordpress.com
                  • 6. Re: can't seem to select or purge drop object from the dba_recyclebin
                    redologger
                    SQL> show user
                    USER is "BEN"
                    SQL>
                    SQL> select * from "BIN$42FQb4OoQQOtAc4nJq9eSQ==$0";
                    
                    no rows selected
                    as above i am able to run the script sucessfully as ben user.

                    But as below, tom (dba) not able to ->:
                    SQL> show user
                    USER is "TOM"
                    SQL>
                    SQL> select * from "BEN.BIN$42FQb4OoQQOtAc4nJq9eSQ==$0";
                    select * from "BEN.BIN$42FQb4OoQQOtAc4nJq9eSQ==$0"
                                  *
                    ERROR at line 1:
                    ORA-00972: identifier is too long
                    • 7. Re: can't seem to select or purge drop object from the dba_recyclebin
                      rp0428
                      select * from "BEN.BIN$42FQb4OoQQOtAc4nJq9eSQ==$0"
                      You can't put the schema name inside the same set of quotes:
                      select * from ben."BIN$42FQb4OoQQOtAc4nJq9eSQ==$0"
                      • 8. Re: can't seem to select or purge drop object from the dba_recyclebin
                        redologger
                        rp0428 wrote:
                        select * from "BEN.BIN$42FQb4OoQQOtAc4nJq9eSQ==$0"
                        You can't put the schema name inside the same set of quotes:
                        select * from ben."BIN$42FQb4OoQQOtAc4nJq9eSQ==$0"
                        as below:
                        SQL> select * from BEN."BEN.BIN$42FQb4OoQQOtAc4nJq9eSQ==$0";
                        select * from BEN."BEN.BIN$42FQb4OoQQOtAc4nJq9eSQ==$0"
                                          *
                        ERROR at line 1:
                        ORA-00972: identifier is too long
                        • 9. Re: can't seem to select or purge drop object from the dba_recyclebin
                          rp0428
                          You still have BEN inside the quotes!
                          select * from BEN."BEN.BIN$42FQb4OoQQOtAc4nJq9eSQ==$0"
                          BEN."BEN...." is garbage.

                          Use the code I gave you!

                          select * from "BEN.BIN$42FQb4OoQQOtAc4nJq9eSQ==$0"
                          • 10. Re: can't seem to select or purge drop object from the dba_recyclebin
                            CSM.DBA
                            In the above example I've given, you can find how to access the object of one user from another schema.

                            SQL> select * from abc."BIN$t+GDAgiYQTDgQ+tYHQp6hw==$0";

                            no rows selected


                            Please go through the series of steps i've provided which could clarify all of your questions.
                            • 11. Re: can't seem to select or purge drop object from the dba_recyclebin
                              redologger
                              CSM.DBA wrote:
                              In the above example I've given, you can find how to access the object of one user from another schema.

                              SQL> select * from abc."BIN$t+GDAgiYQTDgQ+tYHQp6hw==$0";

                              no rows selected


                              Please go through the series of steps i've provided which could clarify all of your questions.
                              Arrrggg.. . ..guess ben and bin looks alike...thus copy and paste too quick to notice the different . Thanks i'll be more careful with that. Now i got it.