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.
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 10.2.0.4.0 - 64bit Production Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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.
ALTER SYSTEM set FIXED_DATE = NONE;
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