How To Use Oracle Expdp Utility

How To Use Oracle Expdp Utility

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial is based on examples to make it easier to follow. Utility oracle expdp (Oracle Data Pump Export) is moving oracle database data and meta-data from one oracle database to another database oracle. Oracle expdp exporting your select oracle tables or oracle database schema into files called dump file set outside from the oracle database and to import data from the file into another oracle database use command oracle impdp (Oracle Data Pump Import).

Our oracle dba has written very basic syntax for Expdp in Oracle :

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

The following database management system help example will export 3 oracle tables (online_users, online_products, online_adds) to file online_tables.dmp using oracle user.

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

In this oracle dba example above will export the three tables to file online_tables.dmp and you can see the events and activity in logfile online_tables.log they both are in oracle directory my_dump_dir. Make sure you will give read-write permissions to the oracle directory in Unix/Windows system and in Oracle database level.

Useful Tips:

Our oracle dba has experienced some problems with expdp oracle utility and first of them is hanging in the middle as the following example.
database management database management system oracle performance tuning oracle expdp oracle dba oracle impdb oracle the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The oracle expdp is stuck after the “Starting” line. To fix the problem press <CTRL>+<C> to exit the process. The oracle expdp utility is still alive so 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 process expdp in oracle current status.

The parameter who is causing your expdp in oracle hanging named oracle FIXED_DATE and it should be set NONE. Type in the following command and when you have permission problems then try it using oracle SYSTEM user.

ALTER SYSTEM set FIXED_DATE = NONE;

Now try again your oracle expdp command and it should finish smoothly.

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 user does not have enough permissions. Give the user oracle role EXP_FULL_DATABASE as the following command. Log out and on and try to do expdp in oracle again.

grant EXP_FULL_DATABASE to <your user>;

See Also:
Oracle Select Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*