Oracle Data Pump is a
newer, faster and more flexible alternative to the "exp" and
"imp" utilities used in previous Oracle versions. In addition to
basic import and export functionality data pump provides a PL/SQL API and
support for external tables. It’s very simple to use and at least 10 times
faster than IMP/EXP.
Simple steps to
configure:
For the examples to work we
must first unlock the MYORACLEUSER account and create a directory object it can
access. The directory object is only a pointer to a physical directory,
creating it does not actually create the physical directory on the file system.
CONN / AS SYSDBA
ALTER USER MYORACLEUSER
IDENTIFIED BY MYUSERPASSWORD ACCOUNT UNLOCK;
CREATE OR REPLACE DIRECTORY
test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON
DIRECTORY test_dir TO MYORACLEUSER;
Existing directories can be
queried using the ALL_DIRECTORIES view.
Table
Exports/Imports
The TABLES parameter
is used to specify the tables that are to be exported. The following is an
example of the table export and import syntax.
expdp myOraUser/myOraPass@db10g
tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp
logfile=expdpEMP_DEPT.log
impdp myOraUser/myOraPass@db10g
tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp
logfile=impdpEMP_DEPT.log
The TABLE_EXISTS_ACTION=APPEND parameter
allows data to be imported into existing tables.
Schema
Exports/Imports
The OWNER parameter
of exp has been replaced by the SCHEMAS parameter which is used to
specify the schemas to be exported. The following is an example of the schema
export and import syntax.
expdp myOraUser/myOraPass@db10g
schemas=MYORACLEUSER directory=TEST_DIR dumpfile=MYORACLEUSER.dmp logfile=expdpMYORACLEUSER.log
impdp myOraUser/myOraPass@db10g
schemas=MYORACLEUSER directory=TEST_DIR dumpfile=MYORACLEUSER.dmp logfile=impdpMYORACLEUSER.log
Database
Exports/Imports
The FULL parameter
indicates that a complete database export is required. The following is an
example of the full database export and import syntax.
expdp system/password@db10g
full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log
impdp system/password@db10g
full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log
INCLUDE and EXCLUDE
The INCLUDE and EXCLUDE parameters
can be used to limit the export/import to specific objects. When the INCLUDE parameter
is used, only those objects specified by it will be included in the
export/import. When the EXCLUDE parameter is used, all objects except
those specified by it will be included in the export/import. The two parameters
are mutually exclusive, so use the parameter that requires the least entries to
give you the result you require. The basic syntax for both parameters is the
same.
INCLUDE=object_type[:name_clause]
[, ...]
EXCLUDE=object_type[:name_clause]
[, ...]
The following code shows how they can be used as command line parameters.
expdp myOraUser/myOraPass@db10g
schemas=MYORACLEUSER include=TABLE:"IN ('EMP', 'DEPT')"
directory=TEST_DIR dumpfile=MYORACLEUSER.dmp logfile=expdpMYORACLEUSER.log
expdp myOraUser/myOraPass@db10g
schemas=MYORACLEUSER exclude=TABLE:"= 'BONUS'" directory=TEST_DIR
dumpfile=MYORACLEUSER.dmp logfile=expdpMYORACLEUSER.log
If the parameter is used from the command line, depending on your OS, the special characters in the clause may need to be escaped, as follows. Because of this, it is easier to use a parameter file.
include=TABLE:\"IN
(\'EMP\', \'DEPT\')\"
A single import/export can
include multiple references to the parameters, so to export tables, views and
some packages we could use either of the following approaches.
INCLUDE=TABLE,VIEW,PACKAGE:"LIKE
'%API'"
or
INCLUDE=TABLE
INCLUDE=VIEW
INCLUDE=PACKAGE:"LIKE
'%API'"
The valid object type paths
that can be included or excluded can be displayed using the DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS,
and TABLE_EXPORT_OBJECTS views.
Network
Exports/Imports (NETWORK_LINK)
The NETWORK_LINK parameter
identifies a database link to be used as the source for a network
export/import. The following database link will be used to demonstrate its use.
CONN / AS SYSDBA
GRANT CREATE DATABASE LINK
TO test;
CONN myuser/mypass
CREATE DATABASE LINK
remote_MYORACLEUSER CONNECT TO MYORACLEUSER IDENTIFIED BY MYUSERPASSWORD USING
'DEV';
In the case of exports,
the NETWORK_LINK parameter identifies the database link pointing to
the source server. The objects are exported from the source server in the
normal manner, but written to a directory object on the local server, rather
than one on the source server. Both the local and remote users require
the EXP_FULL_DATABASE role granted to them.
expdp myuser/mypass@db10g
tables=MYORACLEUSER.EMP network_link=REMOTE_MYORACLEUSER directory=TEST_DIR
dumpfile=EMP.dmp logfile=expdpEMP.log
For imports, the NETWORK_LINK parameter
also identifies the database link pointing to the source server. The difference
here is the objects are imported directly from the source into the local server
without being written to a dump file. Although there is no need for a DUMPFILE parameter,
a directory object is still required for the logs associated with the
operation. Both the local and remote users require the IMP_FULL_DATABASE role
granted to them.
impdp myuser/mypass@db10g
tables=MYORACLEUSER.EMP network_link=REMOTE_MYORACLEUSER directory=TEST_DIR
logfile=impdpMYORACLEUSER.log remap_schema=MYORACLEUSER:TEST
