Oracle Data Export and Import using DataPump expdp and impdp

YouTube video

Oracle Data Pump is a feature in Oracle 10g that allows users to easily import and export data within the Oracle database. In this tutorial, we will learn how to use Data Pump to perform these operations efficiently.

Creating the Directory for Data Export and Import

To begin with, we need to create a directory where the data will be exported from and imported to. In SQL Plus, connect to the Oracle database and create a directory using the following command:
SQL
CREATE DIRECTORY dir_name AS 'directory_location';

For example, let’s create a directory named “DP” at the location “/home/oracle/DP”.

Grant read and write privileges on this directory to the user who will be performing the data import and export operations:
SQL
GRANT READ, WRITE ON DIRECTORY dir_name TO user_name;

For example, let’s grant these privileges to the user “scott” using the directory “DP”:
SQL
GRANT READ, WRITE ON DIRECTORY DP TO scott;

Exporting Data

Once the directory is created and the necessary privileges are granted, we can export the data from the Oracle database. Use the expdp command with the following parameters:
SQL
expdp username/password@service_name tables=table1,table2 directory=dir_name dumpfile=file_name logfile=log_file

For example, to export the tables “EMP” and “DPT” owned by the user “scott” to a file named “EMP_DPT.dmp” and log the export process in a file named “expdp.log”, use the following command:
SQL
expdp scott/tiger@orcl tables=EMP,DPT directory=DP dumpfile=EMP_DPT.dmp logfile=expdp.log

Importing Data

After the data is exported, we can import it back into the Oracle database using the impdp command. The parameters for the import command are as follows:
SQL
impdp username/password@service_name tables=table1,table2 directory=dir_name dumpfile=file_name logfile=log_file

For example, to import the tables “EMP” and “DPT” from the file “EMP_DPT.dmp” and log the import process in a file named “impdp.log”, use the following command:
SQL
impdp scott/tiger@orcl tables=EMP,DPT directory=DP dumpfile=EMP_DPT.dmp logfile=impdp.log

Verifying the Data Export and Import

To verify the success of the data export and import operations, connect to the Oracle database and use SQL queries to check the data. For example:
SQL
SELECT * FROM table_name;

Benefits of Data Pump

Data Pump provides significant improvements in terms of performance for data export and import operations. With Oracle 10g, the performance of data export has been improved tenfold, while the performance of data import has been improved five to seven times. These improvements make the process of data export and import much faster and more efficient.

Conclusion

In conclusion, Oracle Data Pump is a powerful feature that allows users to easily export and import data within the Oracle database. By following the steps outlined in this tutorial, users can create directories, grant privileges, export data, import data, and verify the success of these operations. With the significant performance improvements provided by Data Pump, data export and import becomes a seamless and efficient process for Oracle databases.