How to import schema from remote database using dblink or network link

Question

I have created a network link as follows:

CREATE PUBLIC DATABASE LINK "remote_lab2"
 CONNECT TO system
 IDENTIFIED BY itl
 USING '(DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS =
            (PROTOCOL = TCP)
            (Host = 192.168.5.43)
            (Port = 1521)
          )
      )
      (CONNECT_DATA = (SID = orcl)
      )
  )';

And I am able to query on the remote database using the following query:

select * from bill@remote_lab; //fetches rows in the table 'bill'

But I want to import all the schema as a DUMP file. For that I have created a directory called mydump in my D: drive. but I am not sure how to import all the schema as dump file from remote database to my local system?

Update: After creating the network link, I am able to create a dump file in default directory DATA_PUMP_DIR on remote system using:

expdp VTMOLAP2/vtmolap2 schemas=VTMOLAP2 network_link=remote_lab directory=DATA_PUMP_DIR dumpfile=vtm.dmp logfile=vtm.log;

Now I need to import the dump file in to my local system. How to I go about it?

Solution

You need to use Network import.

With network mode imports, one doesn't need any intermediate dump files. Data is exported across a database link and imported directly into the target database.

For example,

SQL> create user new_scott identified by tiger;
User created.

SQL> grant connect, resource to new_scott;
Grant succeeded.

SQL> grant read, write on directory dmpdir to new_scott;
Grant succeeded.

SQL> grant create database link to new_scott;
Grant succeeded.

SQL> conn new_Scott/tiger
Connected.

SQL> create database link old_scott connect to scott identified by tiger using 'orcl';
Database link created.

impdp new_scott/tiger DIRECTORY=dmpdir NETWORK_LINK=old_scott remap_schema=scott:new_scott

Comments

[kittu]

Updated the question. Please check

[Lalit Kumar B]

@Satyadev I see your update, and the answer is already there. impdp command.

[kittu]

So I just change the query to: impdp VTMOLAP2/vtmolap2 schemas=VTMOLAP2 network_link=remote_lab directory=DATA_PUMP_DIR dumpfile=vtm.dmp logfile=vtm.log; ??

[Lalit Kumar B]

You need to run this on the server you need to import.

[kittu]

The expdp export is very slow. I have about 40gb and its been more than an hour and its still going on. Is there any way to increase the speed?

[Lalit Kumar B]

It depends on your server configuration and network latency.

[kittu]

I guess its not only that. There are some parameters like parallel etc are being used. Just found it on other site

[Lalit Kumar B]

Once again, parallel also depends on your server configuration. The number of cores, CPUs etc. See community.oracle.com/thread/894648?tstart=0

[Welsige]

Is it possible to import just a sample of data over dblink using impdp?

[Lalit Kumar B]

@Welsige Yes, use INCLUDE/EXCLUDE parameter. See examples here oracle-base.com/articles/10g/…