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...
