Expimp and Expdpimpdp
Expimp and Expdpimpdp
Expimp and Expdpimpdp
Datapump
Why expdp is faster than exp (or) why Data Pump is faster than
conventional export/import.
1 owner schemas
2 file dumpfile
3 log logfile/nologfile
This article was originally written against Oracle 10g, but the information is still
relevant up to and including the latest versions of Oracle. New features are broken
out into separate articles, but the help section at the bottom is up to date with the
latest versions.
Getting Started
Table Exports/Imports
Schema Exports/Imports
Database Exports/Imports
INCLUDE and EXCLUDE
CONTENT and QUERY
Network Exports/Imports (NETWORK_LINK)
Flashback Exports
Miscellaneous Information
Data Pump API
External Tables (Unloading/Loading Data Using External Tables)
Secure External Password Store
Roles
Interactive Command Mode
Help
o expdp
o impdp
Time Zone File Version
Related articles.
Getting Started
For the examples to work we must first unlock the SCOTT 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
of the database server.
CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
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.
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.
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.
INCLUDE=object_type[:name_clause] [, ...]
EXCLUDE=object_type[:name_clause] [, ...]
The following code shows how they can be used as command line parameters.
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,VIEW,PACKAGE:"LIKE '%API'"
or
INCLUDE=TABLE
INCLUDE=VIEW
INCLUDE=PACKAGE:"LIKE '%API'"
EXCLUDE=SCHEMA:"LIKE 'SYS%'"
EXCLUDE=SCHEMA:"IN
('OUTLN','SYSTEM','SYSMAN','FLOWS_FILES','APEX_030200','APEX_PUBLIC_USER','ANONYM
OUS')"
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.
CONTENT and QUERY
The CONTENT parameter allows you to alter the contents of the export. The following
command uses the METADATA_ONLY parameter value to export the contents of the
schema without the data.
The QUERY parameter allows you to alter the rows exported from one or more tables.
The following example does a full database export, but doesn't include the data for
the EMP and DEPT tables.
The way you handle quotes on the command line will vary depending on what you
are trying to achieve. Here are some examples that work for single tables and
multiple tables directly from the command line.
CONN / AS SYSDBA
GRANT CREATE DATABASE LINK TO test;
CONN test/test
CREATE DATABASE LINK remote_scott CONNECT TO scott IDENTIFIED BY tiger USING 'DEV';
Flashback Exports
The exp utility used the CONSISTENT=Y parameter to indicate the export should be
consistent to a point in time. By default the expdp utility exports are only consistent
on a per table basis. If you want all tables in the export to be consistent to the same
point in time, you need to use the FLASHBACK_SCN or FLASHBACK_TIME parameter.
# In parameter file.
flashback_time="to_timestamp('09-05-2011 09:00:00', 'DD-MM-YYYY HH24:MI:SS')"
Not surprisingly, you can make exports consistent to an earlier point in time by
specifying an earlier time or SCN, provided you have enough UNDO space to keep a
read consistent view of the data during the export operation.
If you prefer to use the SCN, you can retrieve the current SCN using one of the
following queries.
SELECT current_scn FROM v$database;
SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;
The following queries may prove useful for converting between timestamps and
SCNs.
In 11.2, the introduction of legacy mode means that you can use
the CONSISTENT=Y parameter with the expdp utility if you wish.
Miscellaneous Information
Unlike the original exp and imp utilities all data pump ".dmp" and ".log" files are
created on the Oracle server, not the client machine.
DECLARE
l_dp_handle NUMBER;
BEGIN
-- Open an schema export job.
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'SCOTT_EXPORT',
version => 'LATEST');
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
Once the job has started the status can be checked using.
The following is an example of how this API can be used to perform a schema import
with a schema remap operation.
DECLARE
l_dp_handle NUMBER;
BEGIN
-- Open an schema import job.
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'SCOTT_IMPORT',
version => 'LATEST');
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
Roles
That database user performing the export and import operations will need the
appropriate level of privilege to complete the actions. For example, if the user can't
create a table in the schema, it will not be able to import a table into a schema.
Export> status
Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: D:\TEMP\DB10G.DMP
bytes written: 4,096
Worker 1 Status:
State: EXECUTING
Object Schema: SYSMAN
Object Name: MGMT_CONTAINER_CRED_ARRAY
Object Type: DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Completed Objects: 261
Total Objects: 261