Oracle Database - Data Pump Export (expdp)

> Database > Oracle Database

1 - About

Do not invoke Export as SYSDBA. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users.

Advertising

3 - Prerequisites

3.1 - Directory

In order to use Data Pump, the database administrator must create a directory object and grant privileges to the user on that directory object.

If a directory object is not specified, a default directory object called data_pump_dir is provided. The default data_pump_dir is available only to privileged users unless access is granted by the DBA.

-- create a directory
SQL> CREATE directory data_dump_dir AS 'F:\Dumps';
 
Directory created.
 
SQL> GRANT READ,WRITE ON DIRECTORY data_dump_dir TO QS_ODS, QS_STG;
 
Grant succeeded.

4 - Size estimation

4.1 - ESTIMATE

The ESTIMATE parameter specify the estimation method (in bytes). The estimate is printed in the log file and displayed on your standard output device. The value for this parameter is either:

  • BLOCKS (default). The estimate is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes.
  • or STATISTICS. The estimate is calculated using statistics for each table. For this method to be as accurate as possible, all tables should have been analyzed recently.

The LOB is not accounted for with the estimate in DataPump.

4.2 - ESTIMATE_ONLY

With the parameter ESTIMATE_ONLY you can estimate the space in bytes that would be consumed without actually performing the export operation. Using this Parameter will not generate the dump file other than a logfile (if specified).

The ESTIMATE_ONLY parameter causes export to estimate the space needed, by multiplying the number of database blocks used by the target objects times the appropriate block sizes.

The value for this parameter is either Y (yes) or N (no = default).

  • Y: Export estimates the space that would be consumed, but quits without actually performing the export operation.
  • N: Export does not only estimate, it performs an actual export of data, too.
Advertising

5 - Mode

5.1 - schema

expdp User/[email protected] DIRECTORY=data_dump_dir dumpfile=QS_ODS.dmp
Export: Release 10.2.0.3.0 - Production on Donderdag, 16 April, 2009 4:56:35

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "QS_ODS"."SYS_EXPORT_SCHEMA_01":  QS_ODS******** DIRECTORY=data_dump_dir dumpfile=QS_ODS.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 813.2 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
.......

5.2 - tables

expdp User/[email protected]_identifier DIRECTORY=myDirectory TABLES=Table1,Table2 dumpfile=myDumpFile.dmp
Starting "DI_OBIEE_AIRLINE_AGG"."SYS_EXPORT_TABLE_01":  DI_OBIEE_AIRLINE_AGG/********@DIDB01
DIRECTORY=data_dump_dir_airline TABLES=SAF_OR_10M_T dumpfile=SAF_OR_10M_T.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DI_OBIEE_AIRLINE_AGG"."SAF_OR_10M_T"       3.471 GB 42599572 rows
Master table "DI_OBIEE_AIRLINE_AGG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DI_OBIEE_AIRLINE_AGG.SYS_EXPORT_TABLE_01 is:
  /mnt/data/SAF_OR_10M_T.dmp
Job "DI_OBIEE_AIRLINE_AGG"."SYS_EXPORT_TABLE_01" successfully completed at 13:27:34

6 - Metadata

6.1 - Master table

When a job is started, a “temporary”master table is created op basis van the job_name during the export. If an export fails. the table stay and you may got an error: DataPump Export Or Import Fails With Errors ORA-31633 ORA-6512 ORA-955 (Doc ID 556425.1)

To get the list of the master:

SELECT o.status, o.object_id, o.object_type,
       o.owner||'.'||object_name "OWNER.OBJECT"
FROM   dba_objects o, dba_datapump_jobs j
WHERE  o.owner=j.owner_name AND
       o.object_name=j.job_name AND
       j.job_name NOT LIKE 'BIN$%'
ORDER  BY 4, 2;

If the job is no more running, just suppress this master table.

6.2 - Jobs

SET linesize 250;
COLUMN OWNER_NAME format A30;
COLUMN OPERATION format A30;
COLUMN JOB_MODE format A30;
COLUMN STATE format A30;
SELECT OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE FROM dba_datapump_jobs;
Advertising
db/oracle/expdp.txt · Last modified: 2017/09/06 19:30 by gerardnico