We are planning to migrate 3 TB of data from Oracle 9i XML database to Oracle 10g and I have suggested the Transportable Tablespace feature, but was told that we cannot because of a 9i XML database compatibility issue. What do you recommend?
One option is to transport the data to an Oracle 9i database that will support this move and then upgrade the database to 10g. This will normally be quicker than export/import for 3 TB of data. Also, you may wish to check to see if you have tables in a tablespace that are not using XML DB. Those tablespaces may be able to be transported with TTS. This can make for a shorter transition time for those tablespaces that do have XML DB components.
TTS does have its restrictions. If those restrictions apply to you, then the other alternatives are export/import, SQL*Plus COPY and homegrown solutions.
This was first published in August 2007