How to export data from an Oracle 11 database and import into an Oracle 10?

Question

I am having trouble exporting data from an Oracle 11 to another Oracle in version 10. I tried to copy the binary exp from old machine to new but as they are different architectures (32/64 bits) did not work.

I read that the Data Pump Export is able to generate dump compatible with previous versions but still could not get a suitable result.

I would like to run from the command line in the Operating System Shell (bash) without creating PL-SQL Procedure as this way is easier to document..

Any hint ?

Answer 1

Exporting on the console with Data Pump

You can export (on machine with Oracle 11) like this:

expdp system directory=DATA_PUMP_DIR schemas=seu_schema version=10.2

The name of the directory DATA_PUMP_DIR is obtained via the SQL command below:

select * from dba_directories 

Incidentally DATA_PUMP_DIR is the default name but the above SQL command must be executed to check if it is actually configured this way in your installation.

Exporting on the console with Data Pump

You can import (on machine with Oracle 10) like this:

impdp seu_schema directory=DATA_PUMP_DIR schemas=seu_schema

Note that you do not need to cite the DMP file name because it assigns the value expdat.dmp by default. To check this just list the contents of the directory, the one shown when you executed the command select above.

For example:

ls -lA /usr/lib/oracle/xe/app/oracle/admin/XE/dpdump

Don’t forget to copy the DMP file from the machine where the export was made to the other if the two versions of Oracle are installed on different machines.