What’s this table? SYS_IOT_OVER_152769

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.)

For more info: http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/indexiot.htm#CNCPT911