Oracle数据泵远程导入数据
- 查看现存镜像目录
select * from dba_directories;
- 创建镜像目录
create or replace directory my_dir as 'local_dir' ;
- 把镜像目录的权限赋予所需用户
grant read,write on directory my_dir to oracle_user_name;
- 创建dblink
create public database link link_80
connect to remote_oracle_user_name identified by password
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = remote_oracle_IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = remote_oracle_instancename)
)
)';
检查是否创建成功
select * from dba_db_links;
- 导数据
表模式导入:
impdp local_oracle_user_name/password tables=remote_table_name1,remote_table_name2,remote_table_namen remap_schema=remote_oracle_user_name:local_oracle_user_name directory=my_dir network_link=remote_database_link
用户模式导入:
impdp \'\/ as sysdba \' directory=DATA_PUMP_DIR1 network_link=link_160 schemas='TEST3' REMAP_TABLESPACE=TEST3:TEST