jump to navigation

Beware Deleted Tables September 22, 2009

Posted by mwidlake in internals.
Tags:
trackback

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.
About these ads

Comments»

1. coskan - September 22, 2009

I think you don’t need a spesific privilege at all to see dropped ones because dropped column looks like not working at all for dba_tables for any user:)

SQL> purge recyclebin;

Recyclebin purged.

SQL> select count(*) from recyclebin;

COUNT(*)
———-
0

SQL> create table t1 as select * from dual;

Table created.

SQL> select count(*) from dba_tables;

COUNT(*)
———-
1994

SQL> drop table t1;

Table dropped.

SQL> select count(*) from dba_tables;

COUNT(*)
———-
1993

SQL> select count(*) from recyclebin;

COUNT(*)
———-
1

Another addition is dba_indexes is working with the same logic as dba_tables

SQL> select count(*) from dba_indexes;

COUNT(*)
———-
3191

SQL> create index t_ix on t(object_name);

Index created.

SQL> select count(*) from dba_indexes;

COUNT(*)
———-
3192

SQL> drop table t;

Table dropped.

SQL> select count(*) from dba_indexes;

COUNT(*)
———-
3191

2. mwidlake - September 22, 2009

Thanks Coskan.

I think the bottom line is, when looking at the data dictionary in respect of objects and segments, keep in mind deleted but not purged objects.

Maybe another example of a new feature that is sort-of nice but just adds complexity… (how in heck does anyone brand new to Oracle learn all this stuff?)

3. Neil Chandler - September 23, 2009

Are you randomly dropping the wrong object and need to recover it? In Production!

Is FLASHBACK not good enough for you, or can’t you take the overhead of having FLASHBACK enabled as you have too much activity in too many LOBs

ALTER SYSTEM SET recyclebin = OFF;

Job done. I’ll take my 10% now, thanks.

mwidlake - September 23, 2009

Errr, no Neil, not dropping things on Production. I tend to trial things and do development on a separate database – I like to call it “DEV”. It saves all that tedious impact on the live system than developing code on Live can introduce. I’d highly recommend it to you…
Can I have my 15% now? Or over the next couple of months at a reasonable interest rate? :-) {OK, I’ll settle for a beer at the MI SIG next week}.

I can’t turn off the recyclebin on live in this case anyway. We let managers play on there with OLAP and database management tools they have downloaded off the net….

4. CJ - January 10, 2010

I was reading up on the ‘recycle bin’ feature and I am puzzled as to the use of ‘dropped’ column in the ‘dba_tables’ view.

Looking at Coskan’s example, I am puzzled more than ever. Surely you don’t need the column, because the dropped table won’t appear in the DBA_TABLES view at all.

Or am I missing something?

5. CJ - January 10, 2010

On Googling a bit more, I found the answer to my question.. The column was required in early releases of 10gR1, when the deleted tables still showed up in DBA_TABLES. This was fixed in 10.1.0.3 and 10.2.0.1.

http://searchoracle.techtarget.com/answer/Difference-between-RECYCLEBIN-and-DROPPED-column


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 171 other followers

%d bloggers like this: