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 .
/* 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
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
- Create a directory on the oracle database server which will save dump file
- Grant permission to users for this dump file
- 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
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.
- 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.
- Open SQLPLUS by logging with ABC which has access to data pump directory.
- Place the attached tnsnames file at C:\oracle32\product\11.2.0\client_1\network\admin
- Login to sqlplus with username : ABC@ABC ( note – ABC is alias in tnsnames.ora)
- Command to export table
- 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
- Command to Import table
- 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
