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.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading