Sunday, July 4, 2010

Oracle Data Pump 10g

Recently i worked on Oracle data pump for second time. Its actually a feature of oracle database that helps to export data in a faster manner, i had earlier worked on this in one of my previous projects, but recently used it to export somewhat bigger table in the database.
This method of exporting data is much faster that simple imp and exp.

I will just outline some simple steps to use this great utility -

First of of all we need to create a Dump directory -

In my case i create this directory at the location /backup/dpump_dir

SQL> Create directory exp_dir as '/backup/dpump_dir';

Then we need to grant read,write access to the users that are exporting data on this directory.

SQL> grant read,write on exp_dir to user1;

Now we are all set to start datapump export , we will use -

$expdp username/password DIRECTORY=exp_dir DUMPFILE=exp1.dmp LOGFILE=exp1l.og FULL=y PARALLEL= 10

Also to export a single table we can use -

$ expdp username/password DIRECTORY=exp_dir DUMPFILE=user1.dmp LOGFILE=user1l.log TABLES=TABLENAME PARALLEL=10

This will export a single table

For more information use EXPDP help=y on dollar prompt

Now to check your jobs you can write-

sql> select * from dba_datapump_jobs

From here you can view the name of your job.

To kill currently running job we can first find session name from above and then use -

expdp username/password attach=JOB_NAME
Afterwards we get a prompt

Export >
Export > Kill_job
Are you sure you wish to stop this job ([y]/n): y

Again we can check with -->

SQL> select * from dba_datapump_jobs
no rows selected

No comments:

Post a Comment