Oracle Data Pump remote import file to local database, oracle import 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 the remote database to the 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
-- Here is the newly created bfzg user, with the connect and resouce role permissions. table_exists_action = replace indicates that the table will be replaced if it exists, and imp has no 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
How can I import a local dmp file to an oracle database on the remote host?
You can upload files remotely and then import them. Of course, you can also connect to a remote database through the network for import, but the efficiency may be quite poor.
Check whether the local and remote database versions can be exported.
How Does oracle import my local data to a remote oracle Server?
1. Export the local database to the dmp file, and then upload the exported file to the server for import.
2. Create a local dblink and use SQL statements to insert local data to the server database.