Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Thursday, March 22, 2007

Table that cannot be deleted

Today I run into interesting situation on one of development machines. I was drooping all tables of one user and after that there was still one table with name SYS_IOT_OVER_54321. Seeing that name I've concluded that it must be an overflow table for some Index Organized Table. But there were no other table in that schema. Of course I was not able to delete it.
Than I've realized that there are some tables in recycle bin. This situation is inconsistent as table is in recycle bin but its overflow area is still in normal table list.

Here is test case:

SQL> create table IOT_TEST
  2  (a_pk number not null primary key
  3  ,a_number number
  4  ,big_string varchar2(4000)
  5  ) organization index
  6  including a_number overflow tablespace users;

Table created.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME                                         
------------------------------ ------------------------------                   
SYS_IOT_OVER_14580             IOT_TEST                                         
IOT_TEST                                                                        
So lets drop it and see what will happen:
SQL> drop table IOT_TEST;

Table dropped.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME                                         
------------------------------ ------------------------------                   
SYS_IOT_OVER_14580             BIN$rKpJH0NuROKt+Woa00+hMg==$0                   
As you can see we still have IOT Overflow table that cannot be deleted:
SQL> drop table SYS_IOT_OVER_14580;
drop table SYS_IOT_OVER_14580
           *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
The only way to get rid of that table is to purge recycle bin:
SQL> purge recyclebin;

Recyclebin purged.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

no rows selected
In my opinion it is bug. I haven't yet reported it on metalink, but I'll fill SR soon. I've confirmed it on Oracle 10g R2 (versions: XE, 10.2.0.1, 10.2.0.2, 10.2.0.3)


Paweł

3 comments:

joeB said...

Thanks for posting this... I can confirm this is still affecting even version 11.2.0.3.8

Same solution worked for me. Appreciate your post.

Anonymous said...

If you do drop table purge, any overflow or mapping tables associated with an IOT are automatically removed.

Paweł Barut said...

Dropping table without "purge" moves it to Recycle bin. In my opinion associated overflow tables should be moved to Recycle Bin as well and should not be shown in user tables.

 

Copyright © Paweł Barut
Printing from DOS to USB Printer