Skip to Content
Author's profile photo Hüseyin Bilgen

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.

/FITE/INV_1_T001

/FITE/INV_1_T002

/FITE/INV_1_T00n

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.

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Fabian Schoen
      Fabian Schoen

      Dear  Huseyin

      A short input on the used COMPRESSION=ALL attribute. This is part of the Oracle Advanced Compression option which needs to be licensed.

      With Advanced Compression, Data Pump exports can now be compressed to reduce disk space requirements. Note that the COMPRESSION=METADATA_ONLY option for Data Pump does not require Advanced Compression, and is the default behavior.

      Ora 11g documentation

      regards

      fabian

      Author's profile photo Hüseyin Bilgen
      Hüseyin Bilgen
      Blog Post Author

      Thank you Fabian for your input.