I'm so glad you can't sub-partition an indexed organized table (IOT).  (no we do not want this feature in 12g)

I was looking into my code for unloading partitioned IOT's.
Unloading IOT's (with or without overflow segments) is easy. It's keeping track of all the meta-data that's starting to get difficult.

DUDE allows you to unload tables as follows :

• Table by table based on owner and tablename or objectid
• Shema by schema - so based on schema/owner name
• Tablespace by tablespace - and this is based on the data objectid of the table

You basically can define three different types of object id's

• There's the objectid itself, which defines the object uniquely in most if not all base dictionary table
• Then there's the data objectid (or to use an expensive word 'data layer objectid' - which is the objectid stored within the block itself. Mostly it equals objectid, but if you for example move or truncate a table it'll change. A data objectid can be NULL - for example the table part of a partitioned table does not have a physical segment - it's the partitions that actually contain the data
• And then there's the base objectid - which is the objectid of the base table of an object … or a pointer to another object to put it simple.

So - if you're unloading a complete tablespace, you are going through the data objectid's found in the tablespace. And using those physical objectid's you have to move your way up to the table objectid using the metadata in the base dictionary tables.

Why ? Because you need the table definition (name, columns, datatypes etc). This is quite simple tho.

But wait, sometimes it can get messy. Let's imaging a partitioned IOT using overflow segments with <n> partitions.
What you get is the following meta data spread around several base dictionary tables :

• 1 table   :  objectid  is <t>,  data objectid is null, base objectid is <o>
• <n> table partitions : objectid is <t+n>, data objectid is null, base objectid is <t>
• 1 index   : objectid is <i>, data objectid is null, base objectid is <t>
• <n> index partitions  : objectid is <i+1>, data objectid is <i+1> initially, base objectid is <i>
• 1 table (overflow) : objectid is <o>, data objectid is null, base objectid is <t>
• <n> table partitions (overflow) : objectid is <o+1>, data objectid is <o+1> initially, base objectid is <o>

As you can see - the table is linked to the overflow table and vice versa using a base objectid.
The table partitions are linked to the table, the index is connected to the table, the index partitions are hooked up to the index, and the overflow table partitions are linked to the overflow table… all using the base objectid's. And only two types of objects have actual physical segments - the index partitions and the overflow table partitions.  So if DUDE hits a data objectid of an IOT index partition it can traverse up to the table object and get the definitions.

Pfew … the whole thing made me think about the Front 242 song 'Headhunter' - '1 - you lock the target - 2 - you bait the line - 3 - you slowly spread the net - 4 - you catch the … euh objectid ?'

I'm sure there are a couple of Oracle nerds out there that can appreciate this clip for 1988 !