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_TESTSo 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==$0As 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 tableThe 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 selectedIn 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:
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.
If you do drop table purge, any overflow or mapping tables associated with an IOT are automatically removed.
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.
Post a Comment