Table Data Export With Oracle Tool EXPDP on Windows

Preparations on Oracle

1. Logon to Operating System with SIDADM on Windows, or ORASID on Linux/Unix Systems.

2. Create a logical directory definition where Oracle EXPSP tool will export Table Data

sqlplus “/as sysdba”

SQL> CREATE OR REPLACE DIRECTORY test_dir AS ‘d:\‘;

3. Grant permission to AS Java Schema User to use Export Directory

SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO SAPSR3;

4. Get list of tables to be exported. For e.g.

Export Tables

5. Execute the following command to export the tables data.

expdp SAPSR3@SED.WORLD INCLUDE=TABLE:”LIKE’%/FITE/INV%'” directory=test_dir dumpfile=FITEINVABAPTABLES.dmp logfile=expdpFITEINVABAPTABLES.log COMPRESSION=ALL REUSE_DUMPFILES=Y

Here;

• SAPSR3 is Schema Username.
• SED.WORLD is the Listener Name.
• Directory is the directory created in preparation steps
• Dumpfile is the filename you can freely select
• Logfile is the log file you can use to check export/import results
• COMPRESSION=ALL is compressing al metadata and data within dump file {ALL | DATA_ONLY | METADATA_ONLY | NONE}
• REUSE_DUMPFILES=Y means you can overwrite dumpfile on next try.