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

Question

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

Navigation

#1 by (3 votes)

3

I'm having trouble exporting data from one Oracle 11 to another Oracle in version 10. I tried copying the exp binary from the old machine to the new but as they are distinct architectures (32/64 bits) it did not work.

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

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

Any tips?

oracle oracle10g

asked by anonymous 10.03.2014 / 22:40

1 answer

How to run an R script from the WIndows command line? Dynamic allocation problem - realloc ()

score 3 · Accepted Answer

Exporting on the console with Data Pump

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

expdp system directory=DATA_PUMP_DIR schemas=seu_schema version=10.2

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

select * from dba_directories

By the way DATA_PUMP_DIR is the default name but the above SQL command must be run to verify that it is actually configured this way in your installation.

Exporting on the console with Data Pump

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

impdp seu_schema directory=DATA_PUMP_DIR schemas=seu_schema

Note that you do not have to name the DMP file because it assigns the value expdat.dmp by default. To check this, simply list the contents of the directory, the one displayed when you ran the select command above.

For example:

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

Do not forget to copy the DMP file from the machine where the export was done to the other case the two versions of Oracle are installed on different machines.