Data Pump

Oracle Data Pump
Oracle 10g's new DataPump utility is designed as the eventual replacement for the original Oracle Import and Export utilities.

Some of the drawbacks in traditional Export and Import that needed to be addressed.
<> Operations are difficult to restart.
<> Execution is client-side and single-threaded.
<> Dump files can grow enormously.
<> Tuning mechanisms are limited.
<> Progress monitoring is difficult.
<> Database object filtering features are limited.

Enter The DataPump: Features Overview
Fortunately, Oracle 10g has addressed many of these issues with the DataPump, a new set of utilities that significantly expands the capabilities of the original Export and Import utilities.
<> Server-side parallel execution.
<> Improved control and restartability of operations.
<> Simplified monitoring of operational status..
<> Automated performance tuning.
<> Improved database object filtering.
<> Export dump file control enhancements.

for details visit of above visit http://www.databasejournal.com/features/oracle/article.php/3489491

DataPump 10g at Oracle Website by Arup Nanda
http://www.oracle.com/technology/pub/articles/10gdba/week4_10gdba.html

=============================================================================
Data Pump Export
First you need create a Directory for Data Pump Utility. and must grant read, write priviledges to user.

SQL> create or replace directory expdp as 'D:\expdp';
grant read, write on directory expdp to rakesh;

SQL> GRANT exp_full_database to rakesh;


---------------------------------------------------------------------------------------------Exporting with DataPump
H:>expdp erp/********@live-db tables=client_type directory=exp_dir dumpfile=datapump_erp.dmp

---------------------------------------------------------------------------------------------Importing with DataPump
H:\>impdp aa/********@rock tables=client_type directory=SYS_DMP REMAP_SCHEMA=erp:aa remap_tablespace=erp:users dumpfile=datapump_erp.dmp

-------------------Live Database----------------------TestDatabase-----
DB-Name----------Live-db---------------------------------rock---------
DB-Users-------------erp------------------------------------aa----------
Directoy-----------EXP_DIR----------------------------SYS_DMP------
Tablespaces----------ERP---------------------------------USERS-------

Above chart might help Young, novice DBA's to get any idea, how we can export from one database to another with DataPump.

Note: Before importing you Manually MUST COPY dumpfile from exp_dir to sys_dir.
---------------------------------------------------------------------------------------------
SQL> column directory_path format a70
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------ -----------------------------------
SYS -------------EXPDP---------------------D:\expdp_dir
SYS-------------ABC------------------------d:\abc_dir
SYS-------------WORK_DIR----------------C:\ADE\aime_10.2_nt_push\oracle/work
SYS-------------SYS_DMP------------------D:\exp_dir
SYS-------------DATA_PUMP_DIR---------D:\oracle\product\10.2.0\admin\rock\dpdump\
SYS-------------ADMIN_DIR---------------C:\ADE\aime_10.2_nt_push\oracle/md/admin
6 rows selected.

SQL> select * from dba_directories;

SQL> GRANT read, write ON DIRECTORY expdp TO hr;
Grant succeeded.
SQL>

SQL> CREATE DIRECTORY hrdir AS 'D:\hr_dir';
Directory created.

SQL> grant read, write on directory hrdir to hr;
Grant succeeded.
SQL>

Exporting EMPLOYESS table...

H:\>expdp hr/hr@rock directory=hrdir dumpfile=hr_emptab.dmp logfile=hr_emptab.log tables=EMPLOYEES

Export: Release 10.2.0.1.0 - Production on Friday, 12 June, 2009 12:02:40
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@rock directory=hrdir dumpfile=hr_emptab.dmp logfile=hr_emptab.log tables=EMPLOYEES
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal 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/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "HR"."EMPLOYEES" 15.78 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is: D:\HR_DIR\HR_EMPTAB.DMP
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 12:03:01

The above export command has also exported indexes, views, functions, packages, constraints, we need to export only TABLE EMPLOYEES, not other objects, check the below command.

H:\>expdp hr/hr@rock directory=hrdir dumpfile=hr_only_EMP_table.dmp logfile=hr_emp_table.log tables=EMPLOYEES exclude=index, view, REF_CONSTRAINT, function, trigger, constraint

Export: Release 10.2.0.1.0 - Production on Friday, 12 June, 2009 12:17:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@rock directory=hrdir dumpfile=hr_only_EMP_table.dmp logfile=hr_emp_table.log tables=EMPLOYEES exclude=index, view, REF_CONSTRAINT, function, trigger, constraint
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "HR"."EMPLOYEES" 15.78 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is: D:\HR_DIR\HR_ONLY_EMP_TABLE.DMPJob "HR"."SYS_EXPORT_TABLE_01" successfully completed at 12:18:01