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ł

0 comments:

 

Copyright © Paweł Barut
Printing from DOS to USB Printer