I have an Oracle database which we will be removing shortly. But I would like to store some of the data somewhere else so if can be accessed if required.

Is there a standard/recommended method of doing this? Was thinking export into XML then XML into SQL Server...or this recommmended?



XML is not a good transmission protocol for moving large amounts of data between RDBMSs. I would rather use SSIS and load the data from Oracle that way. Or use an Oracle bulk export tool. XML is very bloated and is best suited to storing unstructured data or moving small amounts data between unknown sources.


If you are unshure as into which rdbms you want to restore the data an you do not want to dump the structure, you could dump it into a file on the oracle server using an external table (type oracle_datapump) as a .csv-file. That should be easy to import into any rdbms.

Another way would be to store data as (ANSI-)sql import statements (which, like xml, are rather verbose, but compress well). Those statements can be edited easily afterwards.

You could dump the ddl for the structure into a text file using either exp/imp or dbms_metadata.get_ddl('table', 'schema', 'user'), but you would have to do a lot of adjustment on the (Oracle-) ddl to adapt to other rdbms.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.