132014Aug
6

Oracle Import Export From Remote System Using IMPDP & EXPDP

By / In Database / Comments(0)

Oracle IMPDP and EXPDP

Oracle provides two very good tool for importing and exporting data dump very efficiently and in very simple manner using new data pump feature.

There are two ways to import data using data pump.

  • Using IMPDP directly without dump file.
  • Using IMPDP with dump file.

Things To Consider –

  1. Utility command used here are with minimal parameters to keep it simple. You can Click Here to find more about both these utilities.
  2. Both the remote user and local user must have dba capabilities assigned to them.
  3. You don’t need to create user when you are using impdp.

Using IMPDP directly without dump file

First you need to create a network link to the remote database. Just Execute the query given below.

create public database link linkname 
connect to remoteUserName identified by remoteUserPassword 
using 'IP Address:Port NO/Service';

After creating network link open command prompt and run the following command.

impdp localUserName/localUserPassword@//localhost network_link=linkname

Now import get started from remote machine to your local machine.


Using IMPDP with dump file

First create network link like we have created in previous case.

Open command prompt and tun the following command.

expdp localUserName/localUserPassword@//localhost network_link=linkname

The above utility will create a dump file in your local system not on remote system. You can go to this location to check dump file Oracle Installation Folder\app\oracle\admin\XE\dpdump\EXPDAT.dmp.

Now we have the dump file we can import it using impdp utility. Run the following command.

impdp localUserName/localUserPassword@//localhost

Now impdp will collect dump file from default location and default name and import it. You can specify dump file name, dump file location, log file name, log file location with many more parameters.

In both the cases localUser is the user who have dba capabilities that is needed to run impdp so it won’t take part in importing the data. You can specify any user with dba permissions.