I was writing some scripts to drop all the objects in a particular schema on an 11gR2 database, and in querying the USER_OBJECTS view, came across a whole lot of tables with names like this:
SYS_IOT_OVER_152769 SYS_IOT_OVER_152772 SYS_IOT_OVER_152775 SYS_IOT_OVER_152778 ...
What in the world are these? As it turns out, these are overflow tables for Index Organized Tables.
The following query on USER_TABLES (or ALL_TABLES or DBA_TABLES) will reveal all:
SQL> SELECT table_name, iot_type, iot_name FROM USER_TABLES WHERE iot_type IS NOT NULL; TABLE_NAME IOT_TYPE IOT_NAME =================== ============ ========================== SYS_IOT_OVER_152769 IOT_OVERFLOW TBMS_REF_ACCOUNT_TYPE SYS_IOT_OVER_152772 IOT_OVERFLOW TBMS_REF_APPLICATION_TYPE SYS_IOT_OVER_152775 IOT_OVERFLOW TBMS_REF_BOND_PAYMENT_TYPE SYS_IOT_OVER_152778 IOT_OVERFLOW TBMS_REF_BOND_STATUS ...
The IOT_NAME reveals the table that owns the overflow table. The create command for TBMS_REF_ACCOUNT_TYPE was:
CREATE TABLE TBMS.TBMS_REF_ACCOUNT_TYPE ( ACCOUNT_TYPE_CODE VARCHAR2(10 BYTE) NOT NULL, DESCRIPTION VARCHAR2(50 BYTE), COMMENTS VARCHAR2(4000), DB_CREATED_BY VARCHAR2(50 BYTE) DEFAULT USER NOT NULL, DB_CREATED_ON DATE DEFAULT SYSDATE NOT NULL, DB_MODIFIED_BY VARCHAR2(50 BYTE), DB_MODIFIED_ON DATE, VERSION_ID NUMBER(12) DEFAULT 1 NOT NULL, CONSTRAINT TBMS_REF_ACCOUNT_TYPE_PK PRIMARY KEY (ACCOUNT_TYPE_CODE) ) ORGANIZATION INDEX INCLUDING DESCRIPTION OVERFLOW;
This means that ACCOUNT_TYPE_CODE and DESCRIPTION will be kept in the index, since these are pretty much the only columns normally accessed; the rest, including the big comments field (which seems to be largely unused), will be stored in the overflow table if they are set.
Right. So I drop the REF table – that should take care of the overflow table, right? Wrong. The SYS_IOT_OVER table is still there! Ah – that’s because the REF table is sitting in the recyclebin. Purge it, and now the SYS_IOT_OVER table is gone. (Not that there was anything wrong with it, mind you – I just wanted to clean this schema out so I could recreate it.)