jump to navigation

Beware Deleted Tables September 22, 2009

Posted by mwidlake in internals.
Tags:
6 comments

From version 10 onwards, Oracle introduced the concept of the recycle bin and the purge command. I won’t go into details of the recycle bin, go to Natalka Roshak’s FAQ page on it if you want the details.

I am only concerned with the fact that from Oracle 10 onwards by default if you drop a table or partition it is not dropped but just renamed, unless you use the PURGE command {which can be tricky if you are using a 9i client as PURGE won’t be accepted by SQL*Plus}.

And it keeps catching me out. So I’m blogging about it –  more for me than any of you lot :-)).

How exactly does it catch me out? Well, as an example,  I’m looking at some partitioning considerations at the moment and ran the below.

select count(distinct(table_name)) from dba_tables
where partitioned='YES'
and owner='ERIC'

COUNT(DISTINCT(TABLE_NAME))
---------------------------
393
Elapsed: 00:00:00.14

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
7079 consistent gets

select count(distinct(table_name))
from dba_part_tables
where owner='ERIC'

COUNT(DISTINCT(TABLE_NAME))
---------------------------
419
Elapsed: 00:00:00.07

Statistics
--------------------------------------------
8 recursive calls
0 db block gets
14084 consistent gets

419 records from DBA_PART TABLES and 393 from DBA_TABLES.

How can that be? How can you have more records with partition information than tables with PARTITIONED flag set to YES?

{And for readers of my post yesterday on querying the data dictionary, check which of the statements performs better – one might expect the view specific to partitions to be quicker than one that is not, but then the partition-specific view is looking at a lot more under the covers}.

OK, Let’s find the objects in DBA_PART_TABLES not in DBA_TABLES

SELECT DISTINCT TABLE_NAME FROM DBA_PART_TABLES
WHERE OWNER ='ERIC'
minus
SELECT DISTINCT TABLE_NAME FROM DBA_TABLEs
WHERE PARTITIONED='YES'
AND OWNER='ERIC'

TABLE_NAME
------------------------------
BIN$c0wxEyj93/vgQKAKQstN+w==$0
BIN$c59mEBlzOvLgQKAKQssjNA==$0
BIN$c6D0zY7yTrvgQKAKQssmdQ==$0
BIN$c6FJQBU6FG3gQKAKQssxCw==$0
BIN$c9qFcYojb/LgQKAKQssyVQ==$0
BIN$c9qFcYokb/LgQKAKQssyVQ==$0
BIN$c9qFcYolb/LgQKAKQssyVQ==$0
BIN$c9qFcYoqb/LgQKAKQssyVQ==$0
BIN$c9t8m70OdEXgQKAKQss4LA==$0
BIN$c9tu0S7KoUngQKAKQss39w==$0
BIN$c9uGpjGSbcvgQKAKQss4fw==$0
BIN$cWfis1j/BGPgQKAKQss7Lw==$0
BIN$cWft6d2mNcvgQKAKQss7qQ==$0
(snip)
BIN$czz0jembWe/gQKAKQsthhg==$0
BIN$czzu+/hC+yTgQKAKQsthpw==$0

26 rows selected.

All 26 records are for deleted objects. You can tell they are deleted as the name is “BIN$something”.

That’s the thing that keeps catching me out, I keep forgetting to exclude deleted but not purged objects from maintenence scripts. *sigh*.

DBA_TABLES has a DROPPED column but other views do not, so you have to exclude on the name, which is not ideal. Someone might want to create a table called “BIN$somthing”.

Mind you, the DROPPED column is not much help:
select count(*) from dba_tables WHERE DROPPED !=’NO’

COUNT(*)
———-
0

So that is as much use a chocolate teapot.
{some little bell is ringing in my head that you need special privs to see the dropped tables. I have DBA role and I can’t…}

This does highlight something very, very odd though.

Why have the records gone from DBA_TABLES and not DBA_PART_TABLES?

Because the concept of the wastebasket has not been around long enough with V10.2 of Oracle for it to work consistently 🙂

I’ll check DBA_OBJECTS for the last object name in the above list:

select * from dba_objects
where object_name='BIN$cxIzaly77FzgQKAKQst5tg==$0'

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
--------------------------------------------------------
DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME
--------------------------------------------------------
TIMESTAMP STATUS T G S
---------------------------
ERIC BIN$cxIzaly77FzgQKAK 2613938
Qst5tg==$0
TABLE 08-SEP-2009 13:49 08-SEP-2009 14:10
2009-09-08:14:10:02 VALID N N N

And I can still look at it {note the use of quotes to allow for an object name with mixed case}:

DESC ERIC.”BIN$cxIzaly77FzgQKAKQst5tg==$0″
Name Null? Type
—————————————– ——– ————
LOAD_LOG_ID NUMBER(12)
LOG_DATE DATE
ABCDEFG_ID NUMBER(12)
REMOTE_ADDRESS VARCHAR2(20)
KGBHFDERS NUMBER(12)
PRODUCT_ID NUMBER(12)
KNGFDRET NUMBER(22)
WKNHGFYTRERDS NUMBER(12)
CHANNEL_TYPE VARCHAR2(1)
COUNT_HITS NUMBER(12)
MFDKEMNFK NUMBER(12)
COUNT_NON_WEIGHTED NUMBER(12)

I can still select from it with the right privileges as well {I’ve not shown this, I’m out of time}.

In conclusion:

  • Objects that are dropped but not purged are still in the data dictionary.
  • You may have to exclude such objects using the fact that the name starts ‘BIN$…’.
  • Dropped objects appear in some parts of the data dictionary but not others.
  • You can still and in fact look at dropped objects.
  • I have a poor memory.
Advertisement