Exporting Dump using Data Pump Utility

Exporting Dump using Data Pump Utility

Posted by Sayan Guharoy

Exporting Dump using Data Pump Utility :Oracle 10g

Oracle 10g provides a better way of exporting or in-port data from the database.A data pump utility that comes with 10g is server oriented and hence their no need for data transfer between client & server instance.Even though the scripts are executed in client machine the data-dump will be created in oracle server side.

Prerequisites 
-------------------------------------

1.You need to login as a system dba.
2.Check whether user/ schema has unlocked account.Typically you will be doing this while installation Oracle 10G itself.
If not then use the following command to unlock your scott account.

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

3.You need to create a directory or check for any existing directory in the server already having data dumps.This will reduce your time and effort of creating a new directory.

To create a new directory from command prompt

$mkdir pmp_dir_test

To check the list of existing directory run the below code

SELECT * from ALL_DIRECTORIES

Once you located or created a physical directory in the server ,then run the below script to create a directory object that will point to actual drive and grant access.Note that directory object is only a pointer ,the dump will  be created in server side not client side.

CREATE OR REPLACE DIRECTORY data_pmp_dir_test AS '/u01/app/oracle/oradata/pmp_dir_test';
//grant access
grant read,write on directory data_pmp_dir_test

if your client oracle is same as your server(a testing environment) you can mention an actual physcal drive loaction which you have the acess like

CREATE OR REPLACE DIRECTORY data_pmp_dir_test AS 'c\oracle\product\10.2.0\admin\orcl\dpdump\dump_dir';

Export schema
-------------------------------------
Open command prompt and run the following export command

expdp scott/sysadmin@orcl schema=scott directory=data_pmp_dir_test dumpfile=temp.dmp logfile=temp.log