Oracle Database Tutorial 55: How to export tables using PARFILE in expdp Data Pump By Manish Sharma

Summary

In this tutorial, Manish from RebellionRider.com demonstrates how to perform table exports using Oracle’s Data Pump (expdp) utility with a parameter file. He explains the steps to set up a directory, create a directory object in the database, and grant the necessary privileges. The tutorial also covers how to create a parameter file, configure it with essential parameters (like directory, dump file, and tables), and run the export command. This practical guide offers a clear, hands-on approach to exporting tables efficiently using Oracle's expdp utility.

Takeaways

• 😀 The tutorial covers how to export tables using the Data Pump (expdp) utility in Oracle Database.
• 😀 In this tutorial, the user will learn how to export tables using a parameter file, a method different from the traditional export methods.
• 😀 Before starting, a directory is created on the system to hold the export files, including the dump and log files.
• 😀 A directory object is created in Oracle to map the physical directory created earlier to the database, using the SQL command: CREATE DIRECTORY.
• 😀 Permissions are granted on the directory object to the user performing the export, allowing read and write access.
• 😀 The user also needs the ‘Datapump_Exp_Full_Database’ role to perform the export successfully.
• 😀 A parameter file is created with essential parameters like DIRECTORY, DUMPFILE, LOGFILE, and TABLES to specify the export details.
• 😀 The parameter file specifies the directory for export files, the names of the dump and log files, and which tables to export (e.g., employees and departments from the HR schema).
• 😀 Once the parameter file is set up, the expdp command is executed from the command prompt, using the PARFILE parameter to reference the parameter file.
• 😀 The export operation is executed successfully and the user is encouraged to visit the blog for detailed documentation and additional resources.
• 😀 The tutorial emphasizes the use of parameter files for more efficient and customizable exports, which can simplify large or complex export tasks.

Q & A

What is the main focus of this tutorial?

The tutorial demonstrates how to export specific tables using the Oracle Data Pump (`expdp`) utility, with an emphasis on using a parameter file for the export process.

How does exporting tables with a parameter file differ from previous tutorials?

In previous tutorials, the export process was performed using traditional methods, whereas this tutorial shows how to export tables using a parameter file, providing more flexibility and control.

What is the purpose of creating a directory object in Oracle Database?

A directory object in Oracle serves as a pointer to a physical directory on the file system where the Data Pump utility can store the dump and log files created during the export process.

What steps are necessary before performing the export using `expdp`?

Before performing the export, you need to create a directory object pointing to the desired directory, grant read and write privileges on that directory to the user performing the export, and ensure the user has the necessary roles like `datapump_exp_full_database`.

What is a parameter file in the context of `expdp` export?

A parameter file is a simple text file that contains all the necessary parameters for the Data Pump export job, such as the directory object, dump file name, log file name, and specific tables to export.

Why is the `.par` extension recommended for the parameter file?

The `.par` extension is recommended for the parameter file as it clearly indicates that the file is intended for use with the `expdp` utility, making it easier to manage and identify.

What are the key parameters included in the parameter file?

The key parameters in the parameter file include `DIRECTORY`, which points to the directory object, `DUMPFILE`, specifying the name of the dump file, `LOGFILE` for the log file name, and `TABLES`, which lists the tables to be exported.

What is the significance of the `TABLES` parameter in the export process?

The `TABLES` parameter is used to specify which tables to export. In this tutorial, the `employees` and `departments` tables from the `HR` schema are exported using this parameter.

How do you execute the `expdp` export using a parameter file?

To execute the `expdp` export using a parameter file, you use the command `expdp <username>/<password>@<DB> PARFILE='<path to parameter file>'`, where you provide the login credentials and the path to the parameter file.

What happens after executing the `expdp` command?

Once the `expdp` command is executed successfully, the Data Pump export job runs and generates the dump and log files in the specified directory. The success message indicates that the export process has completed without errors.