How to Export Tables using PARFILE STEP BY STEP

Export Tables using PARFILE, Table export also known as the table level export in oracle data pump utility. DBA take table level export using expdp data pump command. Oracle DBA perform export import task as per there requirements. Export backup also called as logical backup.

The export backup we can perform as table level, schema level, full database level and tablespace level using expdp data pump utility, in my previous post you can find the related tutorials. In this post we are going to learn how to take table level export using parameter file in expdp utility of data pump.

Table of Contents

What is PARFILE (parameter) file?

The PARFILE (parameter) file is just a text file which is read by expdp utility during the export backup. And the parfile (parameter file) is created by the user on the database server then use it in data pump utility.

Location for parameter file

The DBA user can create the parameter file in any location on the database server, but remember the parfile location.

Name convention of PARFILE?

You can create the parfile with any name because oracle never restrictions for parfile naming, also there is no restrictions for extension. But Oracle highly recommended to give .par extension.

Contents in PARFILE?

The parameter file contains the parameters which is required for expdp command like DIRECTORY NAME, DUMPFILE NAME, LOGFILE NAME etc.

If you are taking table level export then specify the TABLES.
For taking tablespace export specify the TABLESPACES.
Taking user level backup specify the SCHEMAS.
If you want to perform full database export backup specify the FULL.

How to export tables using parameter files?

Before Export Tables using PARFILE we need to performed some settings.

Step 1. Create Directory

Oracle DBA can create a directory on the Database server. First of all DBA need to create a simple folder in any location on database server the give a logical name to this folder.

In my case i'm using '/u01' mount point to creating a folder name "EXPORT_TABLE" and below is the full path of this folder.

/u01/EXPORT_TABLE

Step 2. Create Directory and grant required privileges

First of all we need to login in Database as admin then create a directory at database level using below commands.

$sqlplus / as sysdba

SQL> create directory tables_exp as '/u01/EXPORT_TABLE';

The above command will create a directory object "tables_exp" which is pointing the '/u01/EXPORT_TABLE' location.

Now it's time to grant required privileges to this directory. If you want to performing the export backup by scott user or any other user then we need to grant privileges to that user on the directory.

Suppose in my case i want to performing the export backup by shripal user, then i'm granting the privileges to shripal user on above directory (tables_exp).

SQL> grant read, write on directory tables_exp to shripal;

Step 3. Create PARFILE

Just open a simple notepad and write below parameters in the notepad file. In linux environment just open vi editor and create the parfile.

DIRECTORY=tables_exp
DUMPFILE=tables.dmp
LOGFILE=tables.log
TABLES=emp, dept

Save above file with any name, I'm giving tab.par and the location of parfile is in '/u01/tab.par'

DIRECTORY parameter define the directory name which we creating in the step 2.
DUMPFILE parameter define the backup file name means export dump file name.
LOGFILE parameter store the expdp log in readable format.
TABLES parameter defines the tables name which you want to take backup.

Step 4. Run export using PARFILE

Now all settings has been completed, It's time to run expdp command using parfile. The expdp is executable file which we need to run on $ prompt not on SQL prompt. Just run below command to run the export backup.

$expdp shripal/shripal@ocptech PARFILE=/u01/tab.pra