Re: Interesting topic in linked in

Hi,

I sort of did a presentation on this last UKOUG. The slides are available on the oaktable website - here: http://www.oaktable.net/sites/default/files/ukoug2012_dude.pdf

In short - without SYSTEM, all metadata is lost - this includes :
- table names

• column names
• column datatypes
• column count
• location of segment headers
• index information
• PL code
• mapping of lob column to lobindex/lobsegment

This means that all unloaders on the market can not retrieve table/column names - they can't make stuff up :-) In case of DUDE we use an heuristic algorithm based on data statistics to determine the datatypes. But because it is based on statistics, they are not a 100% correct, especially if the sample set is small. So that means, the procedure is as follows :
- sample data

• unload data
• possible manual correct datatypes
• unload again

In case of LOBs - it's more complex, because lob locators consist of binary information that does not decode to numbers or date's or varchar's. That means there will always be a manual intervention to insert LOB information into that column. Basically this means adding the data objectid's of the lob segment for the specified column. The objectid for the first lob column is typically the data objectid of the table + 2.

There are other side effects when SYSTEM is missing :
- empty tables are not seen

• trailing null columns are not seen
• physical column order might differ from logical column order when using longs
• dropped/truncated tables are seen !

kind regards,
Kurt Van Meerbeeck
http://www.ora600.be

--
http://www.freelists.org/webpage/oracle-l