Data pump examples provided by ORADBA |
Comments for data pump: use data pump is you need speed. It is available
since 10gR2.
Data pump needs DIRECTORY objects to be set up first which are used to specify
the
source and destination folders for data pump and typically contain log and dump
files.
Creating data pump directories
Creating source and destination database DIRECTORY objects:
SQL> CREATE OR REPLACE DIRECTORY dmp_dir AS '/u04/oracle/dpump';
SQL> select * from dba_directories;
If you have separate user to execute data pump operations you’ll also
need:
SQL> GRANT EXP_FULL_DATABASE to DPUSER;
SQL> GRANT READ, WRITE ON DIRECTORY dmp_dir to DPUSER;
Data pump schema export
1) Check if database DIRECTORY objects
are set up correctly, then execute export:
SQL> select * from dba_directories where DIRECTORY_NAME='DMP_DIR';
oracle@host> expdp SYSTEM@dbname schemas=SCHEMA_NAME directory=DMP_DIR dumpfile=SCHEMA_NAME.dmp logfile=SCHEMA_NAME_expdp.log
oracle@host> expdp SYSTEM@dbname
tables=SCOTT.EMP directory=DMP_DIR dumpfile=EMP.dmp logfile=EMP_expdp.log
oracle@host> expdp DPUSER@dbname tables=SCOTT.EMP directory=DMP_DIR dumpfile=EMP.dmp logfile=expdpEMP.log
The three examples above show the basic
use of expdp for schema export, table
export and export as non system user called DPUSER
Data pump schema import
Don’t forget that you need DIRECTORY
objects in the database for data pump!
1) Check if the dmpdir
DIRECTORY exists, if not create it first.
SQL> select * from dba_directories where DIRECTORY_NAME='DMP_DIR';
SQL> CREATE OR REPLACE DIRECTORY dmp_dir AS '/u04/oracle/dpump';
2) Put the export data pump file to the OS directory specified and load
into the DB:
oracle@host> impdp
system directory=dmp_dir dumpfile=schemaDPEXP.dump schemas=SCHEMANAME logfile=SCHEMANAME_imp.log
If you want to import into a different schema
use the remap_schema parameter:
oracle@host> impdp
system directory=dmp_dir dumpfile=schemaDPEXP.dump schemas=SCHEMANAME logfile=SCHEMANAME_imp.log
remap_schema= SOURCE_SCHEMA:TARGET_SCHEMA