Oracle Data Pump remote import file to local database

Oracle Data Pump remote import file to local database

-- Log On As A dba

C: \ Users \ Administrator> sqlplus/as sysdba

-- Create a user

SQL> create user bfzg0828 identified by bfzg0828 default tablespace users quota
Unlimited on users;

-- Grant the connection role and the resource role to the user.

SQL> grant connect, resource to bfzg0828;

Authorization successful.

-- Authorize the creation of data links

SQL> grant create table, create database link to bfzg0828;

Authorization successful.

-- Create a directory

SQL> create or replace directory oracle_exp as 'd: \ oracle ';

The directory has been created.

-- Create an oracle file on the d disk of the Physical Machine

......

-- Grant the directory read/write permission to the user

SQL> grant read, write on directory oracle_exp to bfzg0828;

Grant read, write on directory oracle_exp to bfzg;

Authorization successful.

-- Log in as a bfzg0828 user

SQL> conn bfzg0828/bfzg0828

Connected.

-- Create a network database link

SQL> create database link test113 connect to btv10_bz identified by btv10_bz using '192. 168.0.66: 192/bttest ';

The database link has been created.

-- Query the created network link

SQL> select * from global_name @ test113;

GLOBAL_NAME
-----------------------------------------------

BTTEST

SQL>

-- Use expdp to export a remote database to a local directory

D: \ oracle \ bfzg0828.dmp

C: \ Users \ Administrator> expdp bfzg0828/bfzg0828 @ orcl11g directory = DIR_EXP dumpfile = bfzg0828.dmp logfile = bfzg0828.log network_link = test113

-- Import data to the local database using impdp after the export is successful

-- The New bfzg user has the connect and resouce role permissions.
Limited, table_exists_action = replace indicates, if the table exists, replace, imp does not function

Impdp bfzg/bfzg directory = oracle_exp dumpfile = BFZG0828.DMP remap_schema = btv10_bz: bfzg exclude = statistics table_exists_action = replace

Note that btv10_bz is the export user and bfzg is the import user.

BFZG0828.DMP Export user is btv10_bz (btv10_bz/btv10_bz@192.168.0.66: 1521/bttest)

-- Import a single table

Impdp bfzg/bfzg directory = oracle_exp dumpfile = BFZG0828.DMP tables = btv10_bz.bt_corp remap_schema = btv10_bz: bfzg exclude = statistics table_exists_action = replace