OracleDB‎ > ‎


Oracle import export using datapump (data pump)

Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database

SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';
Directory created.
SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
Grant succeeded.

PS: Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';


Give permission to user doing the export

grant dba to <user>
grant EXP_FULL_DATABASE to <user>
grant select any table to <user>
grant create table to <user>

Run export:

$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

To import to another schema

impdp kreypour/p4ssw0rd@EBDEV directory=ORADUMPS DUMPFILE=MARACUJA.dmp SCHEMAS=<schema to be imported> remap_schema=<source schema>:<destination Schema>

Full export mode: You use the FULL parameter when you want to export the entire database in one export session. You need the EXPORT_FULL_DATABASE role to use this mode.

• Schema mode: If you want to export a single user’s data and/or objects only, you must use the SCHEMAS parameter.

• Tablespace mode: By using the TABLESPACES parameter, you can export all the tables in one or more tablespaces. If you use the TRANSPORT_TABLESPACES parameter, you can export just the metadata of the objects contained in one or more tablespaces. You may recall that you can export tablespaces between databases by first exporting the metadata, copying the files of the tablespace to the target server, and then importing the metadata into the target database.

• Table mode: By using the TABLES parameter, you can export one or more tables. The TABLES parameter is identical to the TABLES parameter in the old export utility. Schema mode is the default mode for Data Pump Export and Import jobs.