Export/Import using ExpDP in Oracle 10g Part1

This is my first blog. In this blog, I have written about  Oracle's ExpDP/ImpDP (data pump) utility.
This utility is a lot different from earlier Export/Import utility(Exp/Imp).

This is not a complete list. More will come on the later part(s) of the series.

Environment:-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod               
PL/SQL Release 10.2.0.1.0 - Production                                         
CORE 10.2.0.1.0 Production                                                     
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                        
NLSRTL Version 10.2.0.1.0 - Production     

Machine:- <>.<>.<.>.54 X86  Windows 2003 Server
 
Machine:- <>.<>.<.>.82 X86 bit     RedHat Linux 5

User : Scott/Test_user/Rman_User

Directories:
Instead of creating a new directory, I have used DATA_PUMP_DIR which is created by default when you install Oracle software. The location of this directory is :

On windows
<OraclePath>10.2.0\admin\<SID>\dpdump\

On Unix/Linux (Linux 5 in this case)

<mount point>/oracle/admin/<SID>/dpdump/

In case you want to use a new directory,You can use following procedure.

On Windows:
Create directory on the drive. eg. E:\DP_DIR
Login to database: Connect to SYS user or any user with DBA priv(s). and execute following create statement.

1: Create directory DPDIR AS 'D:\DP_DIR'

This will create a directory. Check DBA_DIRECTORIES.


2: GRANT READ,WRITE ON DIRECTORY DPDIR TO SCOTT
To check the priv(s) granted on this directory to user Scott: Connect to Scott and run following query

3: SELECT * FROM all_tab_privs   WHERE table_name = 'DPDIR';
On Linux/Unix boxes, The procedure is same, but you need to change Owner and Group of folder, you referenced in CREATE DIRECTORY statement.

Case 1: Scott exported his tables M1,M2,M1_Mask from Machine 54 using ExpDP.  Scott of  82 could import tables from Export file of scott from Machine 54 without any hitch.
 Export:
  ExpDp Scott/tiger@seed_54 partfile=tb1_lvl1.txt
 The contents of parfile are
 tables=T1,T2,T1_MASK DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT_TB2.DMP  LOGFILE=SCOTT_TB2.LOG

 Import:

ImpDp Scott/Tiger@seed_82 ParFile=Tb1_lvel_imp.txt

 The Content of Partifle are
 TABLES=T1,T2,T1_MASK DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT_TB2.DMP LOGFILE=SCOTT_TB2_IMP.LOG

Note:- As we know that the user scott has exported his tables.The default tablespace of scott on 54 is USERs but on 82, the default tablespace of scott is Rman_Tbs. In above import script we have not mentioned any key word, parameter to remap this tablespace. So in this case if USERS tablespace exists in 82 then that USERS tablespace will be used otherwise error (Tablespace Does Not exists) will be shown on screen if we donot use remap_tablespace parameter. now lets run Import Script once again with Rmap_Tablesapce cluase

 IMPORT: IMPDP SCOTT/TIGER@SEED_82
 Now the changed Parfile contains following:
 TABLES=T1,T2,T1_MASK DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT_TB2.DMP LOGFILE=SCOTT_TB2_IMP.LOG  REMAP_TABLESPACE=USERS:SCOTT_TBS

Case 2:- Scott's tables imported to another user. Eg. Rman_user of Seed_54
 We to export tables of Scott from 82 machine and have to import those tables into Rman_USer Schema of 54 machine.

 Export:
  Expdp scott/tiger@seed_82 parfile=abc.txt

  contents of parfile are
  tables=dept,emp directory=data_pump_dir dumpfile=scott_tbl2_exp.dmp logfile=scott_m_log.log
 Import
  ImpDp rman_user/rman_user@seed_54 parfile=xyz.txt

  contents of parfile are
  tables=DEPT,EMP DUMPFILE=SCOTT_TBL2_EXP.DMP DIRECTORY=DATA_PUMP_DIR LOGFILE=IMP_SCTT_BL.LOG                  REMAP_SCHEMA=SCOTT:RMAN_USER
Note:- In this case we have exported table(s) (emp,dept) of scott and imported those to RMAN_USER.The default tablespace of Scott on 82 is SCOTT_TBS. and this tablespace does not exits in seed_54 machine. so we got error tablespace does not exits. To troubleshoot this problem,We have two options either create tablespace or use REMAP_TABLESPACE like we used in case1.Finally, after troubleshooting
this error, our parfile looked like following

  tables=DEPT,EMP DUMPFILE=SCOTT_TBL2_EXP.DMP DIRECTORY=DATA_PUMP_DIR LOGFILE=IMP_SCTT_BL.LOG
 remap_tablespace=SCOTT_TBS:USERS REMAP_SCHEMA=SCOTT:RMAN_USER

Case 3:- Scott's (emp,dept) tables to exported with data for Deptno=10 only.

 Export:
  ExpDp scott/tiger@seed_82 parfile=abc.txt

  contents of parfile are
  tables=dept,emp directory=data_pump_dir dumpfile=scott_tbl2_exp.dmp logfile=scott_m_log.log query='where deptno=10'

 Import
  ImpDP test_user/test_user@seed_54 parfile=STIMPQ.TXT
  Contents of parfile are
  tables=DEPT,EMP DUMPFILE=SCOTT_TBL2_EXP.DMP DIRECTORY=DATA_PUMP_DIR LOGFILE=IMP_SCTT_BL.LOG remap_tablespace=SCOTT_TBS:USERS REMAP_SCHEMA=SCOTT:TEST_USER



Continues...