Taking an Export- External Tables

A small amount of setup is required when you run a Data Pump export job. Here are the steps:

1. Create a database directory object that points to an OS directory that you want to write/read Data Pump files to/from. (We used the directory object with our external tables.)

2. Grant read and write privileges on the directory object to the database user running the export.

3. From the OS prompt, run the expdp utility.

Here are the first two steps since we have been discussing the directory object with the external tables:

SQL> create directory dp_dir as ‘/oradump’;

SQL> grant read, write on directory dp_dir to mv_maint;

You can now use Data Pump to export data from the database. The simple example in this section shows how to export a table. In previous examples in this book we have used tables such as INV and EMP that we can use as our tables for export:

$ expdp mv_maint/Pa$$w0rd123! Directory=dp_dir tables=inv dumpfile=exp_inv. dmp logfile=exp_inv.log

The expdp utility creates a file named exp_inv.dmp in the /oradump directory, containing the information required to re-create the INV table and populate it with data as it was at the time the export was started.

Additionally, a log file named exp_inv.log is created in the /oradump directory, containing logging information associated with this export job.

If you do not specify a dump file name, Data Pump creates a file named expdat.dmp. If a file named expdat.dmp already exists in the directory, then Data Pump throws an error. If you do not specify a log file name, then Data Pump creates one named export. log. If a log file named export.log already exists, then Data Pump overwrites it.

Tip although it is possible to execute Data pump as the SYS user, it is better to use a different user. First, SYS is required to connect to the database with the AS SYSDBA clause.this requires a Data pump parameter file with the USERID parameter and quotes around the associated connect string.this is unwieldy. second, most tables owned by SYS cannot be exported (there are a few exceptions, such as AUD$). If you attempt to export a table owned by SYS, Data pump will throw an ORA-39166 error and indicate that the table doesn’t exist.this is confusing. even when exporting a FULL database, the system schemas SYS, ORDSYS, and MDSYS will not be exported even if exporting using an SYS account.