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 :
You basically can define three different types of object id's
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 :
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 !
Kurt Van Meerbeeck
Cell : +32 495 580714
Service & Support Manager
Cell: +45 53 74 71 27
Cell : +1 303 885 4526
South Africa :
Cell : +27 82 7799275
East Asia Pacific
Cell : +61 2 9191 7427 ext. 1270