Thursday, September 22, 2011

NETWORK LINK in datapump


NETWORK LINK: in datapump is the ability to transfer data directly from one instance to another without using a datafile to transport the data. The data is transported across a db link from one instance to another. Datapump still requires a directory on the server to store some information, but the data itself is transferred directly. This is a huge time and space saver for those large data transfer operations. This is how it is done:

Here is the DEMO:

We have two databases on two different servers:

Source database: delhi---->server1

Target database: noida----->server2


Connect to database "delhi" on server1:

c:\>sqlplus sys/sys@delhi as sysdba

SQL> create user abc identified by abc;

User created.

SQL> grant CREATE PUBLIC DATABASE LINK,CREATE SESSION to abc;

Grant succeeded.

SQL> grant CREATE TABLE,insert any table to abc;

Grant succeeded.

SQL> grant create session to abc;

Grant succeeded.

SQL> conn abc/abc
Connected.

SQL> create table p
  2  (id number);

Table created.

SQL> insert into p values(3);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from p;

        ID
----------
         3
         3
         3
         3
         3

Now,connect to database "noida" on server2:

SQL>create directory gpump as 'D:\network\';

SQL>grant read,wrie on directory gpump to system;

SQL> create public database link h_link connect to abc identified by abc using 'delhi';

Database link created.

Checking database link:

SQL> select * from p@h_link;

        ID
----------
         3
         3
         3
         3
         3

Now start the import operation that will import schema "abc" from database "delhi" to database "noida" without actually creating any dumpfile;

c:\>impdp system/sys@noida LOGFILE=import.schema.log NETWORK_LINK=h_link SCHEMAS=abc directory=gpump

Import: Release 11.2.0.1.0 - Production on Thu Sep 22 16:10:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@noida LOGFILE=import.schema.log NETWORK_LINK=h_link SCHEMAS=abc directory=gpump
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "ABC"."P"                                        5 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 16:10:47

SQL> conn abc/abc@noida
Connected.
SQL> selct * from tab;

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
P                              TABLE

SQL> select * from p;

        ID
----------
         3
         3
         3
         3
         3

Now, we will export the schema "abc" present in database "delhi:server1" to "noida:server2" but before that drop the user "abc" from database "noida" to avoid ambiguity:


C:\>sqlplus sys/sys@noida as sysdba 



SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 22 16:51:08 2011


Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options




SQL> drop user abc cascade;


User dropped.

SQL> host expdp system/ramtech@noida LOGFILE=export_schema.log dumpfile=ff.dmp NETWORK_LINK=h_link SCHEMAS=abc directory=gpump

Export: Release 11.2.0.1.0 - Production on Thu Sep 22 16:52:42 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/********@noida LOGFILE=export_schema.log dumpfile=ff.dmp NETWORK_LINK=h_link SCHEMAS=abc directory=gpump
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ABC"."P"                                   5.039 KB       5 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
  D:\NETWORK\FF.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 16:52:46

You will see the export dumpfile will create on server1 from server2.

Enjoy:-)

24 comments:

Maneesh Kalra said...
This comment has been removed by the author.
Vishwanath Sharma said...

Thanks Maneesh!

Anonymous said...

gud..

Maneesh Kalra said...
This comment has been removed by the author.
Anonymous said...

Hi, I have a doubt regarding when you use expdp using networklink it will create the dump file in the same server(Sever containing original data-let it prod db) or remote server(where you need the data - let it test db).

sizegenetics discount said...

the blog could be scorching words http://sizegeneticsonlinereview.com/

Anonymous said...

Thanks for one's marvelous posting! I actually enjoyed reading it, you may be a great author. I will ensure that I bookmark your blog and will come back later on. I want to encourage continue your great posts, have a nice afternoon!

Feel free to visit my weblog ... please click the next webpage

Anonymous said...

Hello to every body, it's my first pay a quick visit of this weblog; this blog consists of awesome and genuinely good information for visitors.

my weblog Source

Anonymous said...

A motivating discussion is worth comment.
I do think that you ought to write more about this subject matter,
it may not be a taboo matter but usually people do not talk about these
topics. To the next! Best wishes!!

Also visit my blog post: Going On this page

Anonymous said...

After looking into a handful of the articles on your
site, I truly like your technique of writing a blog.
I bookmarked it to my bookmark webpage list and will be checking back
in the near future. Please visit my website as well and let me know what you think.


My homepage; Read More Listed Here

Anonymous said...

Hello! Do you use Twitter? I'd like to follow you if that would be okay. I'm definitely enjoying your blog
and look forward to new posts.

Here is my homepage :: simply click the up coming internet page

Anonymous said...

Howdy just ωanted to giѵe you a quiсk heaԁѕ up аnd let yοu knоω a few of the
images arеn't loading correctly. I'm not surе ωhу but I
think its a linking issue. I've tried it in two different internet browsers and both show the same results.

My blog post :: Discounts Motels : Assess Accommodations Price ranges

Anonymous said...

Hmm iѕ аnуone еlse еncounteгіng ρгοblems with the pictuгеѕ οn thіs blog loading?
I'm trying to determine if its a problem on my end or if it's the blog.
Any reѕponѕes wоuld be gгeatly
aрpreciated.

Stop by my pаgе; Why wouldn't you Examine Resorts Costs?

Anonymous said...

ӏ'm not sure where you are getting your info, but great topic. I needs to spend some time learning much more or understanding more. Thanks for wonderful information I was looking for this info for my mission.

Also visit my web site - Prepare Your own Purchasing Live in Dubai Accommodations Near Purchasing Center

Anonymous said...

Hey there! Dο you use Twitter? I'd like to follow you if that would be ok. I'm abѕolutely
enјoying уour blоg anԁ loοk forwarԁ tο new updates.



Feel free to suгf to my web page; Cheap Motels In Las Vegas - Great For The Limited Getaway

Anonymous said...

Hellο thеrе! I knоw thіs is kinԁa off topic but I was wonderіng which blog platform агe you uѕing fοr thіs website?
I'm getting tired of Wordpress because I've had issuеs with hackеrs and I'm looking at alternatives for another platform. I would be awesome if you could point me in the direction of a good platform.

Stop by my web site; Last second Resort Reservation

Anonymous said...

I have been surfing οn-lіne greater than three hοurs latelу, but I nеver disсovered any attentіon-gгabbing aгtіcle likе yοurs.
It's beautiful price sufficient for me. In my opinion, if all website owners and bloggers made good content as you did, the web shall be much more helpful than ever before.

Here is my website ... Very last minute Hotel Reservation

Anonymous said...

It's going to be finish of mine day, but before finish I am reading this wonderful paragraph to increase my knowledge.

my webpage; http://www.travelingpages.com/lodging/hotels-and-motels :: mytradewiki.com ::

Anonymous said...

Hello webmaster I like your post ....

My web site: playa del carmen all inclusive ()

Anonymous said...

At this point the friar on the pilgrimage becomes quite upset and
he interrupts the summoner. An intern in a hospital is dispatched to the morgue located in the basement of the building.

As outrageous as such disregard may have seemed to pilgrim Chaucer,
it is common among the religious figures of the text.


my web page: summoners war sky arena hack

Anonymous said...

to other. No globally uncontroversial explanation exists,
though nigh populate can always opt for a area chop-chop.
disregard state comparatively healthy or wasted,
you get erudite many techniques for relieving and treating the Forex securities
industry, do extraordinary online search tools that you can unruffled engage pot of sites do not Coach Outlet Coach Handbags Outlet Coach Factory Outlet Coach Factory Online Coach Factory coach outlet Stores Coach Outlet Coach Purses Coach Factory Outlet Coach Outlet Online all the belongings that anyone can do in the way to have
got up with much little high-ticket to buy for nether 20 dollars,
it is clear for a new payer reckon predictable they are bucketed,
criticize them as breathing in for your buck when buying finance cute metals so much

Have a look at my blog post - Coach Outlet

Unknown said...

"air jordan shoes"
"cheap oakley sunglasses"
"tiffany and co"
"chrome hearts online store"
"adidas nmd"
"links of london"
"tiffany and co outlet"
"michael kors handbags clearance"
"michael kors outlet online"
"tiffany and co"
"tiffany and co outlet online"
"kobe shoes,kobe zoom,nike zoom kobe"
"louboutin shoes"
"Kanye West shoes"
"michael jordan shoes"
"michael kors outlet online"
"oakley sunglasses"
"http://www.jordansforcheap.us.com"
"michael kors handbags"
"kobe byrant shoes"
"authentic jordans"
"nike zoom kobe"
"michael kors outlet"
"chrome hearts"
"nike huarache"
"oakley sunglasses"
"michael kors outlet online"
"cheap air jordan"
"tiffany online"
"michael kors handbags"
"http://www.tiffanyand.co.uk"
"cheap jordans"
"ray ban sunglasses outlet"
"http://www.christian-louboutin-outlet.uk"
"http://www.cheap--jordans.us.com"

Unknown said...

HTTP://WWW.ATLAS-DAMAM.COM
شركة نقل اثاث بالدمام
شركة نقل عفش بالدمام
شركة نقل اثاث بالخبر
شركة نقل عفش بالخبر
شركة نقل عفش بالاحساء
شركة نقل اثاث بالاحساء
شركة نقل عفش بالجبيل
شركة نقل اثاث بالجبيل
شركة تخزين عفش بالدمام
شركة تخزين اثاث بالدمام
شركة تخزين عفش بالخبر
شركة تخزين اثاث بالخبر
شركة تخزين اثاث بالجبيل
شركة تخزين عفش بالجبيل
شركة تخزين عفش بالاحساء
شركة تخزين اثاث بالاحساء
شركة تخزين عفش بالقطيف
شركة تخزين اثاث بالقطيف

yanmaneee said...

golden goose outlet
yeezy shoes
supreme
christian louboutin outlet
adidas tubular
golden goose sneakers
timberland
russell westbrook shoes
balenciaga shoes
nike air max 270