Oracle Data Export and Import using DataPump expdp and impdp

YouTube video

In this tutorial, we will explore how to utilize the data pump feature of Oracle 10g to import and export data within the Oracle database. We will cover the steps to create a directory where the data will be exported from and imported into, granting read and write privileges on the directory, exporting the data using the expdp utility, deleting the data from the tables, and finally importing the data back into the tables using impdp utility.

Setting up the Directory

First, we need to create a directory where the data will be exported from and imported into. Open SQL Plus and connect to the Oracle database. Run the following command to create the directory:

sql
CREATE DIRECTORY DP_DIR AS '/path/to/directory';

Make sure to replace /path/to/directory with the actual path of the directory you want to create.

Next, grant read and write privileges on this directory to the user. Run the following command:

sql
GRANT READ, WRITE ON DIRECTORY DP_DIR TO scott;

Replace scott with the username of the user who needs access to the directory.

Exporting the Data

To export the data from the Oracle database, use the expdp utility. Run the following command:

sql
expdp scott/tiger@service_name tables=EMP,DEPT directory=DP_DIR dumpfile=EMP_DEPT.dmp logfile=expdp.log

Replace scott/tiger@service_name with the appropriate username, password, and service name. This command exports the EMP and DEPT tables to a dumpfile named EMP_DEPT.dmp and stores the log information in expdp.log.

Deleting Data from Tables

To simulate a scenario where the data is deleted from the tables, run the following commands in SQL Plus:

sql
DROP TABLE EMP;
DROP TABLE DEPT;

Importing the Data

Once the data has been exported, it can be imported back into the Oracle database using the impdp utility. Run the following command:

sql
impdp scott/tiger@service_name tables=EMP,DEPT directory=DP_DIR dumpfile=EMP_DEPT.dmp logfile=impdp.log

Replace scott/tiger@service_name with the appropriate username, password, and service name. This command imports the data from the EMP_DEPT.dmp dumpfile into the EMP and DEPT tables and stores the log information in impdp.log.

Verifying the Data

To verify that the data has been imported successfully, run the following commands in SQL Plus:

sql
SELECT * FROM EMP;
SELECT * FROM DEPT;

Conclusion

In this tutorial, we have learned how to use the Oracle data pump feature to export and import data within the Oracle database. We covered the steps to create a directory, grant read and write privileges on the directory, export data using the expdp utility, delete data from tables, import data using the impdp utility, and verify the imported data. The data pump feature has significantly improved the performance of data export and import in Oracle, making it a powerful tool for managing data within the database.

Thank you for watching this tutorial, and we hope you found it helpful.