EXPDP IN ORACLE - Online Tech Support

How To Use Oracle EXPDP Utility

 online tech support  Comments Off on How To Use Oracle EXPDP Utility

This Online Tech Support page is based on examples and it answers to questions like what to do when Oracle Expdp is hanging or is stuck?

Utility Oracle expdp (Oracle Data Pump Export) is for taking out data and metadata from Oracle database into a file. That file can be used to import the data into another Oracle database. When you do execute Oracle expdp it lets you to select a table or tables or all database schema to be exported into files called dump file set. These files will be created outside from the Oracle database and later can be imported using command Oracle impdp (Oracle Data Pump Import).

The very basic syntax for the exporting is:

EXPDP <username>/<password> DIRECTORY=<oracle directory> DUMPFILE=<name>.dmp LOGFILE=<name>.log 
TABLES=<table names>

The example below will export three Oracle tables (online_users, online_products and online_adds) into file online_tables.dmp using user oracle.

EXPDP oracle/secret DIRECTORY=my_dump_dir DUMPFILE=online_tables.dmp LOGFILE=online_tables.log 
TABLES=online_users, online_products, online_adds

The example above will export those three tables and the events and activity can be follow in logfile online_tables.log. The both data and log files are stored in Oracle directory my_dump_dir. About the directory before using it in the export make sure it has given read-write permissions in Unix/Windows system and also in Oracle database.

Useful Tips:

Some DBAs have experienced some problems with expdp utility and first of them is hanging in the middle of the process as on the following example.

Export: Release - 64bit Production

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

Connected to: Oracle Database 10g Enterprise Edition Release - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ONLINE"."SYS_EXPORT_TABLE_01":  oracle/******** directory=my_dump_dir 
dumpfile=online_tables.dmp logfile=online_tables.log 
tables=online_users, online_products, online_adds

The Oracle expdp got stuck after the “Starting” line and to fix the problem press <CTRL>+<C> to exit from the process. After exiting from the expdp utility the job is still alive and to kill the process enter the command “KILL_JOB“. To make sure the process is really terminated use command “STATUS” that will give you feedback about the expdp job current status.

The parameter that may is causing the expdp utility hanging named oracle FIXED_DATE and it should be set to “NONE“. Execute the following command as Oracle SYSTEM user.


Now try again Oracle expdp command and it should get further from the “Starting” line.

The second tips:
Error ORA-31696: unable to export/import TABLE_DATA:<your_schema_name.your_table_name> using client specified DIRECT_PATH method appears when the Oracle database user does not have enough permissions. Give to the Oracle user role EXP_FULL_DATABASE as the command below. Log out and on and try to do expdp or impdp again.

grant EXP_FULL_DATABASE to <your user>;

See Also:
Home Oracle Impdp