Data Pump- Table Mode and Parameter files (PARFILE).
Table export is also known as the table mode export in data pump. Exporting tables using expdp data pump utility is the most frequently performed tasks of DBA. By exporting table you can take the logical backup of the necessary tables.
In the previous tutorial we learnt how to export full database, schema of database and tablespace of database using standard way of executing expdp export utility but today in this tutorial we will learn how to export tables using Parameter File in expdp export utility of data pump.
What is a parameter file?
Parameter file is just a simple text file which is read and opened by expdp client unlike Dumpfiles and Logfiles which are created and written by the server. Which means that Parameter file can be created by client on its own system and there is no need to involve the DBA whereas files like DUMPFILE and LOGFILE are the files which are created by the expdp utility on the server.
Moreover along with the creation, Database user (client) can also update and modify the parameter files as per the requirements whereas tampering DUMPFILE can cause an error as dumpfiles are only made for server user.
Where should you create a Parameter file?
Database user can create a parameter file anywhere on its system.
What is the name convention of Parameter file?
In Oracle Database there are no such restrictions for naming a parameter file. You can give whatever name you want to your parameter file regarding the extension. Similar to dumpfile and logfile you can specify whatever extension you want for your parameter file.
But it’s highly recommended to give .par extension as using it will help you easily distinguish the parameter files from other files in your system or while transferring back up from one system to another.
What are the contents of a parameter file?
As the name suggests a parameter file contains the parameters of the expdp command such as DIRECTORY, DUMPFILE, LOGFILE and –
- SCHEMAS if you are performing the Schema export
- TABLESPACES if you are exporting tablespaces of the database
- FULL if you are doing a full database export or TABLES in case of table export along with many other parameters of expdp utility.
How to export tables using parameter files?
As we know that before performing any export we have to perform some general setting. Let’s do it.
Step 1: Create a Directory
Note here, this step must be performed by DBA on server system. Here in this step you need to create a folder on your server system and give it some logical name (if you are practicing then just make this folder on your localhost). You can make this folder anywhere on the system.
As I have already told you that If this export is a part of your backup strategy then it’s advisable to avoid making the folder on the same partition which also contains your Oracle home directory or OS bootable files.
For the demonstration I use a folder named ‘Table export’ and the location of this folder (full directory path) is
D:\ Data Pump\ Table Export.
Step 2: Create Directory Object and grant mandatory privileges
To perform this step we need to log on to our system as sys user with sysdba privileges.
C:\> sqlplus / as sysdba
This step also needs to be performed by a DBA on the server system. Next you have to create a directory object. Now we have already discussed the directory object in detail in tutorial 52 so I suggest you to take a look at tutorial 52 on full database export.
To create a directory object we use CREATE DIRECTORY command.
SQL>CREATE DIRECTORY exp_table AS ‘D:\Data Pump\Table Export’;
This query will create a directory object with exp_table name which will point to the directory Table export which was created in step 1.
Now after making a Directory object we need to grant some mandatory privileges to the user who wants to perform the export. For example say user HR wants to perform the export of employees and department tables. Thus we have to grant read and write privileges on the directory Table Export using Directory object exp_table to the user HR.
SQL> GRANT read, write ON DIRECTORY exp_table TO hr;
Now we have ensured all the required settings. Next we will create a Parameter file.
Step 3: Create a parameter file
Just make a plain text file anywhere you want in your system and name it. For the demonstration I have created a file in my D:\ drive and named it exp_hr_table.par. Full path of my parameter file is D:\exp_hr_table.par
After creating a parameter file we have to write all the necessary parameters in it. The content of my exp_hr_table.par parameter file must be as follows:
DIRECTORY = exp_table
DUMPFILE = table.dmp
LOGFILE = table_lg.log
TABLES = employees, departments
First parameter is DIRECTORY and the value of this parameter is exp_table which is the directory object which we created in step 2. Using this parameter you can specify the default location to which expdp Export can write the dump file set and the log file.
Next parameter is DUMPFILE using this parameter you specify the name of dumpfile for your export. Whatever name you will specify as the value of this parameter, the expdp export will create the dumpfile with the same name. For example here in my case I have set the name of my dumpfile as table.dmp.
Next parameter is LOGFILE Using log file parameter expdp export utility will generate a human readable log file for you which are very helpful in tracking the status of your export. Similar to the dumpfile, using this parameter you specify the name of log files for your export.
And the last parameter is TABLES. This parameter tells the expdp export that we want to perform a table export. Also using TABLES parameter you can specify the name of all the tables separated by commas which you want to export.
Step 4: Export Tables Using PARFILE
After creating the parameter file you are free to execute the expdp export utility. Expdp export utility is an executable file which means we cannot execute it on SQL prompt. To execute expdp export you need to be on your command prompt so if you are on you SQL prompt then I suggest you to exit it first then execute your expdp.
To execute expdp export you have to write
C:\> expdp hr/hr@ORCL PARFILE=’ D:\exp_hr_table.par’
This command starts with expdp which is an executable utility followed by login information of the user who wants to perform the export and then parameter PARFILE which takes the path of your parameter file as its value. Using this parameter you specify the location or name of your parameter file which you have created and want to execute.
Restrictions with PARFILE
- The PARFILE Parameter cannot be specified within a parameter file.
- You cannot write login information of the database user in Parameter file.
Restrictions With Table Export
- Cross-schema references are not exported. For example, a trigger defined on a table within one of the specified schemas, but which resides in a schema not explicitly specified, is not exported.
- Types used by the table are not exported in table mode. This means that if you subsequently import the dump file and the type does not already exist in the destination database, then the table creation will fail.
- The use of synonyms as values for the TABLES parameter is not supported. For example, if the regions table in the hr schema had a synonym of regn, then it would not be valid to use TABLES=regn. An error would be returned.
- The export of tables that include a wildcard character, %, in the table name is not supported if the table has partitions.
- The length of the table name list specified for the TABLES parameter is limited to a maximum of 4 MB, unless you are using the NETWORK_LINK parameter to an Oracle Database release 10.2.0.3 or earlier or to a read-only database. In such cases, the limit is 4 KB.
- You can only specify partitions from one table if TRANSPORTABLE=ALWAYS is also set on the export.
That’s all in Table Export. Hope this blog article helped you in understanding the topic in detail. Kindly please share it on your social network and help me reach out to more people. Thanks & have a great day!