4 Replies Latest reply: Oct 8, 2013 9:14 AM by user8949151 RSS

    Unable to descripe the table and unable to drop the table

    user8949151

      Hi,

       

      I have a temp table that we use like staging table to import the data in to the main table through some scheduled procedures.And that will dropped every day and will be created through the script.

       

      Some how while I am trying to drop the table manually got hanged, There after I could not find that table in dba_objects, dba_tables or any where.

       

      But Now I am unable to create that table manually(Keep on running the create command with out giving any error), Even I am not getting any error (keep on running )if I give drop/desc of table.

       

      Can you please any one help on this ? Is it some where got stored the table in DB or do we any option to repair the table ?

       

      SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME like 'TEMP%';

       

      no rows selected

       

      SQL> desc temp

       

       

      Thank in advance.

        • 1. Re: Unable to descripe the table and unable to drop the table
          Anar Godjaev

          Hi,

           

          if this table drops then it moved DBA_RECYCLEBIN table. and also original name of its changed automatically by oracle.

           

          For example :

           

          SQL> create table tst (col varchar2(10), row_chng_dt date);

           

           

          Table created.

           

           

          SQL> insert into tst values ('Version1', sysdate);

           

           

          1 row created.

           

           

          SQL> select * from tst ;

           

           

          COL        ROW_CHNG

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

          Version1   16:10:03

          If the RECYCLEBIN initialization parameter is set to ON (the default in 10g), then dropping this table will place it in the recyclebin:

           

           

          SQL> drop table tst;

           

           

          Table dropped.

           

           

          SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime

            2  from recyclebin

          SQL> /

           

           

          OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME

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

          BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2013-10-08:16:10:12

           

          All that happened to the table when we dropped it was that it got renamed. The table data is still there and can be queried just like a normal table:

           

           

          SQL> alter session set nls_date_format='HH24:MI:SS' ;

           

           

          Session altered.

           

           

          SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

           

           

          COL        ROW_CHNG

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

          Version1   16:10:03

          Since the table data is still there, it's very easy to "undrop" the table. This operation is known as a "flashback drop". The command is FLASHBACK TABLE... TO BEFORE DROP, and it simply renames the BIN$... table to its original name:

           

           

          SQL> flashback table tst to before drop;

           

           

          Flashback complete.

           

           

          SQL> select * from tst ;

           

           

          COL        ROW_CHNG

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

          Version1   16:10:03

           

           

          SQL> select * from recyclebin ;

           

           

          no rows selected

          It's important to know that after you've dropped a table, it has only been renamed; the table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.

           

           

          SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime

            2  from recyclebin

          SQL> /

           

           

          OBJECT_NAME                    ORIGINAL_NAME TYPE                      UND PUR DROPTIME

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

          BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE                     YES YES 2006-09-01:16:10:12

           

           

          SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

           

           

          Table purged.

           

           

          SQL> select * from recyclebin ;

           

           

          no rows selected

           

          Thank you

           

          And check this link:

          http://www.orafaq.com/node/968

          http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm

           

          Thank you

          • 2. Re: Unable to descripe the table and unable to drop the table
            BluShadow

            How was the table created?  Was the name in double quotes?  if so, then the name would be case sensitive.

             

            What if you try:

             

            select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where upper(OBJECT_NAME) like 'TEMP%';

            • 3. Re: Unable to descripe the table and unable to drop the table
              user8949151

              Thanks for your help. Its helped me.

              • 4. Re: Unable to descripe the table and unable to drop the table
                TSharma-Oracle

                So what was the actual issue?