Data pump in datawarehousing environment

Hi Guys,

Basic requirement for using Data pump -- To use data pump across 2 schema or 2 instance of databases both the databases should be on same physical server . ( Same machine ) because you will be using local directory ( local folder on that machine) and this folder should be accessible from both schema.In case of using datapump using db links over the network. It is considered very slow and you are dependent on the speed of db link and so no where close to speed Data pump is capable of .

According to me to dump the file both schema must be on the same server where they can see the folder on which the dump file is saved

1. Create a directory on the oracle database server which will save dump file
2. Grant permission to users for this dump file
3. Command to export data using data pump to directory on the linux server

impdp username/password DIRECTORY=dpump_dir1 DUMPFILE=scott.dmp

TABLES=scott.emp REMAP_SCHEMA=scott:jim

Easy Way To Export import using External table ( Added 21 Aug 2014)

impdp,expdp is new command in 11g instead of imp , exp. Instead of doing that we can make use of external table to makes things much simpler

/* Creates a directory */

CREATE OR REPLACE DIRECTORY test_dir AS '/app1/ADMIN/product/11.2.0/dbhome_1/demo/'

/* exports table using datapump into external table. The external table can be directly queried we can export data to external table using datapum and oracle loader both */

create table emp_unload
organization external
( type oracle_datapump
 default directory test_dir
 location( 'emp.dat' )
 )
 as
 select * from emp

/* We can directly select from external table using select *. Though its not a actual table */

select * from emp_unload

/* Loading data into emp2 from External table */

create table emp3 as select * from emp_unload

How to find Hostnames

select host_name from v$instance

TNSNAMES.ora file

ABC =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = bbbbbb )(PORT = 1521))

   )

 (CONNECT_DATA =

   (SERVICE_NAME = kkkkk.iiiii.com )

 )

)

Below are steps for using data pump.

1.  Check if you have read/write access to data pump directory. I think you have access to default directory DATA_PUMP_DIR  . So commands are based on default directory. Please check name while running command.
2. Open SQLPLUS by logging with ABC which has access to data pump directory.
3. Place the attached tnsnames file at  C:\oracle32\product\11.2.0\client_1\network\admin
4. Login to sqlplus with username : ABC@ABC ( note – ABC is alias in tnsnames.ora)
5. Command to export table 
6. expdp ABC ESTIMATE_ONLY=YES DIRECTORY=data_pump_dir  DUMPFILE= data_pump_dir: ref_type.dmp LOGFILE= data_pump_dir: ref_type.log tables=ABC.ref_bill_type
7. Command to Import table
8. impdp KLJ_STAGE SCHEMAS=KLJUSER DIRECTORY=data_pump_dir  DUMPFILE= data_pump_dir: ref_type.dmp tables=ABC.ref_bill_type LOGFILE= data_pump_dir:exp_ref_bill_type.log