Monday, April 11, 2011

Changing from Original Export/Import to Oracle Data Pump



Oracle Data Pump  is the replacement for the original Export and Import utilities. Available starting in Oracle Database 10g, Oracle Data Pump enables very high-speed movement of data and metadata from one database to another. The Data Pump Export and Import utilities have a similar look and feel to the original utilities, but they are much more efficient and give us greater control and management of our import and export jobs.
In order to use Data Pump, the database administrator must perform following two steps  :
1.) Create a directory object and
2.) Grant privileges to the user on that directory object.

1.) Creating Directory Objects : Data Pump differs from original Export and Import in that all jobs run primarily on the server using server processes. These server processes access files for the Data Pump jobs using directory objects that identify the location of the files. The directory objects enforce a security model that can be used by DBAs to control access to these files. If a directory object is not specified, a default directory object called data_pump_dir is provided. The default data_pump_dir is available only to privileged users unless access is granted by the DBA. e.g.  
          
SQL> create directory dpump as ‘D:\dpump\’ ;

2.) Grant Read,Write on Directory :  After a directory is created, we need to grant READ and WRITE permission on the directory to other users. READ or WRITE permission to a directory object means only that the Oracle database will read or write that file on our behalf. we are not given direct access to those files outside of the Oracle database unless we have the appropriate operating system privileges. Similarly, the Oracle database requires permission from the operating system to read and write files in the directories . e.g ; 

SQL> grant read,write on directory dpump to harry ;

Once the directory access is granted, the user “harry” can export his database objects with command arguments . Now we will see the difference between the datapump and original exp/imp while export a table which is in scott  account .   
                                            
1) Export of table “emp”  from scott’s account using original export.

C:\> exp harry/harry tables=scott.emp file=c:\scott_table.dmp log=c:\scott_table_log.log
Export: Release 11.1.0.6.0 - Production on Mon Apr 11 11:28:28 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                            EMP         14 rows exported
Export terminated successfully without warnings.

2. ) Export of table “emp”  from scott’s account using DataPump
C:\> expdp harry/harry directory=datapump1 tables=scott.emp dumpfile=emp_table.dmp   logfile=exp_emptab_log.log
Export: Release 11.1.0.6.0 - Production on Monday, 11 April, 2011 11:33:57
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HARRY"."SYS_EXPORT_TABLE_01":  harry/******** directory=datapump1 tables=scott.emp dumpfile=emp_table.dmp logfile=exp_emptab_log.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "HARRY"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HARRY.SYS_EXPORT_TABLE_01 is:
  D:\DPUMP\EMP_TABLE.DMP
Job "HARRY"."SYS_EXPORT_TABLE_01" successfully completed at 11:36:02


Hope above post will help you to understand the difference between data pump and original export if you are beginner. I will  further post the important and daily usages parameter of data pump .



Enjoy    :-)




No comments: