#data pump import

New Post has been published on http://www.varindersandhu.in/2013/06/06/oracle-data-pump-expdp-and-impdp/

Oracle Data Pump (expdp and impdp)

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Data Pump Export is a utility for unloading data and metadata into dump file. The same exported dump file can be imported only by Data Pump Import utility. With the help of this Data Pump Export we can export the data of one machine and import into another machine.

Dump File :

The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set.

In this post we will see how to export the schema/user from one machine (i.e. source machine) and import to the machine (destination machine). For the demo purpose we will export/import the schema/user name as “testuser”.

Steps to Export Schema using Data Pump Export Utility (expdp)

Step -1 : Create a Directory Object (You should be login with Admin Privilege User)

The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server.

CREATE OR REPLACE DIRECTORY data_dir AS 'D:\EXportDir';

Step -2: Grant to testuser  Schema/User (here testuser is a schema/User at the Source machine)

GRANT READ, WRITE ON DIRECTORY data_dir TO testuser;

Step-3: Export Schema using expdp

expdp testuser/test123@orcl schemas=testuser directory= data_dir dumpfile=testuser_data.dmp logfile=testuser_explog.log

Steps to Import Schema using Data Pump Import Utility (impdp)

Step-1: First we have to create a Schema/User as “testuser” at destination machine (Refer: Oracle – Create tablespace and User / Schema.

Step-2: Create a Directory Object  same as we did above during Export Schema (Refer Step-1 of Data Pump Export Utility)

Step-3: Grant to testuser  Schema/User (Refer Step-2 of Data Pump Export Utility)

Step-4:  Import Schema using impdp

impdp testuser/test123@orcl  schemas= testuser directory= data_dir  dumpfile= testuser_data.dmp logfile= testuser_implog.log.log

New Post has been published on http://www.varindersandhu.in/2013/06/06/oracle-data-pump-expdp-and-impdp/

Oracle Data Pump (expdp and impdp)

Data Pump Export is a utility for unloading data and metadata into dump file. The same exported dump file can be imported only by Data Pump Import utility. With the help of this Data Pump Export we can export the data of one machine and import into another machine.

Dump File :

The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set.

In this post we will see how to export the schema/user from one machine (i.e. source machine) and import to the machine (destination machine). For the demo purpose we will export/import the schema/user name as “testuser”.

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Steps to Export Schema using Data Pump Export Utility (expdp)

Step -1 : Create a Directory Object (You should be login with Admin Privilege User)

The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server.

CREATE OR REPLACE DIRECTORY data_dir AS 'D:\EXportDir';

Step -2: Grant to testuser  Schema/User (here testuser is a schema/User at the Source machine)

GRANT READ, WRITE ON DIRECTORY data_dir TO testuser;

Step-3: Export Schema using expdp

expdp testuser/test123@orcl schemas=testuser directory= data_dir dumpfile=testuser_data.dmp logfile=testuser_explog.log

Steps to Import Schema using Data Pump Import Utility (impdp)

Step-1: First we have to create a Schema/User as “testuser” at destination machine (Refer: Oracle – Create tablespace and User / Schema.

Step-2: Create a Directory Object  same as we did above during Export Schema (Refer Step-1 of Data Pump Export Utility)

Step-3: Grant to testuser  Schema/User (Refer Step-2 of Data Pump Export Utility)

Step-4:  Import Schema using impdp

impdp testuser/test123@orcl  schemas= testuser directory= data_dir  dumpfile= testuser_data.dmp logfile= testuser_implog.log.log