Oracle数据泵远程导入数据

时间:2023-01-08 12:41:58
  1. 查看现存镜像目录

    select * from dba_directories;

  2. 创建镜像目录

    create or replace directory my_dir as 'local_dir' ;

  3. 把镜像目录的权限赋予所需用户

    grant read,write on directory my_dir to oracle_user_name;

  4. 创建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;

  5. 导数据

    表模式导入:

    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