Skip to content
Advertisements

Datapump network_link export and import

This post show you, just a easy understanding about Datapump network_link parameter.

I have used two site database

Broker and ACE

Step 1:

Go to Broker database

SQL> create user nettest identified by nettest;

User created.

SQL> grant connect,resource,dba to nettest;

Grant succeeded.

SQL> conn nettest/nettest@broker.world;
Connected.
SQL> create table mynet(empno number,empname varchar2(20));

Table created.

SQL> insert into mynet values(001,’azar’);

1 row created.

SQL> insert into mynet values(002,’jahir’);

1 row created.

SQL> commit;

Commit complete.

Step 2:

Go to ACE database

SQL> conn / as sysdba
Connected.
SQL> create user mynet identified by mynet;

User created.

SQL> grant connect,resource,dba to mynet;

Grant succeeded.

SQL> grant read,write on directory data_pump_dir to mynet;

Grant succeeded.

SQL> commit;

Commit complete.

SQL> conn mynet/mynet;
Connected.

SQL> select * from tab;

no rows selected

SQL> create database link useme connect to nettest identified by nettest using ‘
BROKER.world’;

Database link created.

SQL> commit;

Commit complete.

SQL> select * from dba_directories where directory_name like ‘DATA_PUMP_DIR%’;

OWNER                          DIRECTORY_NAME
—————————— ——————————
DIRECTORY_PATH
——————————————————————————–

SYS                            DATA_PUMP_DIR
c:\oracle\product\10.2.0\admin\ace\dpdump\
SQL> select * from tab;

no rows selected

SQL>

 

SQL> select * from dba_db_links where owner=’MYNET’;

OWNER
——————————
DB_LINK
—————————————————-

USERNAME
——————————
HOST
—————————————————-

CREATED
———
MYNET
USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM
NETTEST
BROKER.world
27-OCT-10

Step 3:

Go to broker db server and execute your export command here, you can see your broker DB schema (nettest) dump file stored in ACE db server schema directory (mynet).

C:\Documents and Settings\Administrator>expdp mynet/mynet@jed.world dire
ctory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=t
estnet.log dumpfile=testnet.dmp tables=mynet

Export: Release 10.2.0.1.0 – 64bit Production on Wednesday, 27 October, 2010 16:
53:52

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting “MYNET”.”SYS_EXPORT_TABLE_01″:  mynet/********@jed.world directory=data
_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnet.log
 dumpfile=testnet.dmp tables=mynet
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “NETTEST”.”MYNET”                            5.25 KB       2 rows
Master table “MYNET”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MYNET.SYS_EXPORT_TABLE_01 is:
  C:\ORACLE\PRODUCT\10.2.0\ADMIN\ACE\DPDUMP\TESTNET.DMP
Job “MYNET”.”SYS_EXPORT_TABLE_01″ successfully completed at 16:55:02

Export Finished and then If you need , you can import the dump file from broker db server to ace db server.

For import, dumpfile parameter not need.
C:\Documents and Settings\Administrator>impdp mynet/mynet@jed.world dire
ctory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=t
estnetimp.log remap_schema=nettest:mynet  tables=mynet

Import: Release 10.2.0.1.0 – 64bit Production on Wednesday, 27 October, 2010 17:
00:45

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting “MYNET”.”SYS_IMPORT_TABLE_01″:  mynet/********@jed.world directory=data
_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnetimp.
log remap_schema=nettest:mynet tables=mynet
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported “MYNET”.”MYNET”                                  2 rows
Job “MYNET”.”SYS_IMPORT_TABLE_01″ successfully completed at 17:02:15
C:\Documents and Settings\Administrator.ACE-INS>

 Step 4:

SQL> conn mynet/mynet@jed.world
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
MYNET                          TABLE

SQL> select * from mynet;

     EMPNO EMPNAME
———- ——————–
         1 azar
         2 jahir

SQL>

 

If you need to refer about network_link parameter, you can from here

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_export.htm#i1011008

Advertisements
2 Comments Post a comment
  1. Arjun #

    On exporting using network link in the remote server, where will be the server process for expdp will be started. Whether the expdp server process will be started in source database server or remote database server?

    August 2, 2012
  2. abdul #

    Hi

    I tried the way you described but unfortunately I hit with this errors, basicall I was trying to take dp export of 11g from 10g database over the network link.
    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
    ORA-06512: at “SYS.KUPW$WORKER”, line 6345

    thanks
    qabdul

    November 15, 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: