impdp+network link如何跳过expdp直接导入目标库

这篇文章给大家分享的是有关impdp+network link如何跳过expdp直接导入目标库的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

impdp命令特殊用途，可以将数据库的一个用户迁移到另一台机器上的数据库的用户中。如果目标用户不存在，还可以对应的创建该用户，快速的把A库上的用户迁移到B库上。

下面就来看一下命令格式：

B库下执行命令：（目标库，需要到导入数据的数据库）

Impdp username/passwd schema=userA remap_schema=userA:userB remap_tablespace=tbsA:tbsB network_link=dblink_name

说明： Userid: Username/passwd用户建议为system。

Remap_schema: userA:userB。数据库用户映射。 同用户的话，此参数省略

Remap_tablespace: tbsA:tbsB。默认表空间映射。

Schemas: userA。必须是dblink中指定用户。建议不指定。

Directory: 该种模式下，此参数指定的是日志文件的路径。如果不指定，则路径默认为data_pump_dir。

Network_link: 在B库上创建的连接到A库的dblink。
 

不过有几个前提：

1、username：这个操作的数据库用户建议是system，如果是其他用户的话就需要有dba权限的用户才能执行；

2、dblink：必须能够连接到对应库上的数据库用户下。

3.优点：只是不再将数据导出后导入，而是直接将数据从源库导入到目的库。

4.如果从原库导出schema A，且db_link建立在schema A上，则原库的该schema A用户需具有exp_full_database权限否则会报错：

With the Partitioning, OLAP and Data Mining options ORA-31631: privileges are required ORA-39149: cannot link privileged user to non-privileged user

5、这个操作是局域网内迁移数据最方便的工具，不过也可能是速度最慢的工具。

6、同时还可用此方法导表空间，单独的表等等.....tablespaces=xxx_tbs即可。...

3、在目标库上建立到源端的db_link的时候，可以针对system用户建立，这样就可以导出导入全库数据或者表空间数据。

7、当针对某个用户A创建db_link时，需要给该用户A exp_full_database的权限才可以导出该schema得数据。

8、在导入的过程中注意目标数据库存在表数据的情况，可采用table_exists_action来处理。

实验一
源库：:192.168.56.20  hostname:slient
目标库：:192.168.56.12 hostname:wang

一、目标库操作：
1.创建tns连接字符串：
[oracle@wang admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@wang admin]$
[oracle@wang admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = wang)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DBdb)
    )
  )

20  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
[oracle@wang admin]$ tnsping 20

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-AUG-2017 23:51:59

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.20)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))
OK (0 msec)
[oracle@wang admin]$

注意：检查源库、目标库监听是否起来。

2.创建对于源库的dblink
SQL> create public database link test connect to scott identified by tiger using '20';

Database link created.

验证：
SQL> set lines 200 pages 999
SQL> col db_link for a10
SQL> col host for a10
SQL> select OWNER,DB_LINK,USERNAME,HOST,CREATED from dba_db_links;

OWNER                          DB_LINK    USERNAME                       HOST       CREATED
------------------------------ ---------- ------------------------------ ---------- ------------
PUBLIC                          TEST       SCOTT                          20         06-AUG-17

注意：创建公用dblink
3.创建导入的用户：
SQL> create user hh identified by hh account unlock;

User created.

二、源库对应导出用户授予导出权限：
SQL> grant EXP_FULL_DATABASE to scott;

Grant succeeded.

三、目标库进行导入作业：
[oracle@wang admin]$ impdp system/oracle schemas=scott remap_schema=scott:hh  network_link=test

Import: Release 11.2.0.4.0 - Production on Sun Aug 6 00:54:06 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** schemas=scott remap_schema=scott:hh network_link=test
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HH" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "HH"."DBXL"                                     13 rows
. . imported "HH"."DEPT"                                      4 rows
. . imported "HH"."EMP"                                      14 rows
. . imported "HH"."SALGRADE"                                  5 rows
. . imported "HH"."BONUS"                                     0 rows
. . imported "HH"."TEST":"P1"                                 0 rows
. . imported "HH"."TEST":"P2"                                 0 rows
. . imported "HH"."TEST":"P3"                                 0 rows
. . imported "HH"."TEST":"P4"                                 0 rows
. . imported "HH"."TEST123"                                   0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sun Aug 6 00:54:23 2017 elapsed 0 00:00:15

[oracle@wang admin]$    

四、验证：
SQL> conn hh/hh;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DBXL                           TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TEST                           TABLE
TEST123                        TABLE

7 rows selected.


实验二

目标库：192.168.56.12 hostname:wang  db_name:DBdb  service_names:service1,service2,service3
源库：  192.168.56.11 hostnamerhel   db_name:orcl  service_names:orcl

一、目标库操作：
--配置到源库的tns
[oracle@wang admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

orcl =
  (DESCRIPTION =
    (LOAD_BALANCE = yes)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
[oracle@wang admin]$

--查看要导入的用户下的数据
SQL> conn hr/hr;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE

7 rows selected.

--创建导源库hr用户的dblink:
SQL> create database link ol connect to hr identified by hr using 'orcl';

Database link created.

SQL> select * from dba_db_links;

OWNER           DB_LINK         USERNAME                       HOST            CREATED
--------------- --------------- ------------------------------ --------------- ------------
SYS             FTLINK          SCOTT                          20              13-AUG-17
HR              ORA             HR                             orcl            08-DEC-17
SYS             OL              HR                             orcl            09-DEC-17



二、源库检查：
--检查要导出的测试表
SQL> conn hr/hr;
Connected.
SQL>  select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE
SYNO                           TABLE

8 rows selected.

SQL> select count(*) from syno;

  COUNT(*)
----------
        35

--创建索引
SQL> create index idx_object_id on syno(object_id);

Index created.

SQL>


三、目标库执行导入：
[oracle@wang ~]$ impdp \'\/ as sysdba\' tables=hr.SYNO network_link=ol

Import: Release 11.2.0.4.0 - Production on Sat Dec 9 08:54:17 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

[oracle@wang ~]$


报错：需要授予源库要导出的用户exp_full_database权限。

--源库授权：
SQL> conn / as sysdba
Connected.
SQL> grant EXP_FULL_DATABASE to hr;

Grant succeeded.


--再次在目标库执行导入：
[oracle@wang ~]$ impdp \'\/ as sysdba\' tables=hr.SYNO network_link=ol

Import: Release 11.2.0.4.0 - Production on Sat Dec 9 09:03:34 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" tables=hr.SYNO network_link=ol
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "HR"."SYNO"                                     35 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Dec 9 09:03:51 2017 elapsed 0 00:00:16

[oracle@wang ~]$
发现索引统计信息等都已经导入


四、验证：
--目标库验证：
SQL>    conn / as sysdba
Connected.
SQL> set lines 200
SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where table_name='SYNO';

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
HR                             IDX_OBJECT_ID                  HR                             SYNO                           VALID

SQL> conn hr/hr;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE
SYNO                           TABLE

8 rows selected.

SQL> select count(*) from syno;

  COUNT(*)
----------
        35

实验三
源库、目标库参照实验二

任务：将源库下的scott用户下的表a、b导入到目标库的hr用户下，且表的表空间映射为TS_XXF，索引的表空间映射为TST

一、源库操作，模拟测试表a、b
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> create table a as select * from emp;

Table created.

SQL> create table b as select * from user_objects;

Table created.

SQL> create index idx_deptno on a (deptno);

Index created.

SQL> create index idx_obj_id on b(object_id);

Index created.

SQL> select count(*) from a;

  COUNT(*)
----------
        14

SQL> select count(*) from b;

  COUNT(*)
----------
         8

SQL> conn / as sysdba
Connected.
SQL> col SEGMENT_NAME for a15
SQL>  select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES from dba_segments where SEGMENT_NAME in('A','B');

OWNER           SEGMENT_NAME    TABLESPACE_NAME                     BYTES
--------------- --------------- ------------------------------ ----------
SCOTT           B               USERS                               65536
SCOTT           A               USERS                               65536

SQL> select OWNER,INDEX_NAME,TABLESPACE_NAME,STATUS from dba_indexes where TABLE_OWNER='SCOTT' and table_name in ('A','B');

OWNER           INDEX_NAME                     TABLESPACE_NAME                STATUS
--------------- ------------------------------ ------------------------------ --------
SCOTT           IDX_DEPTNO                     USERS                          VALID
SCOTT           IDX_OBJ_ID                     USERS                          VALID


二、目标库查询：
SQL> col name for a70
SQL> set lines 200 pages 999
SQL> select f.file#,
  2         t.name tablespace,
  3         f.name,
  4         trunc(f.bytes / 1048576, 2) size_mb,
  5         to_char(f.creation_time, 'yyyy-mm-dd') creation_time,
  6         status
  7    from v$datafile f, v$tablespace t
  8   where f.ts# = t.ts#
  9   order by f.creation_time;

     FILE# TABLESPACE                     NAME                                                                      SIZE_MB CREATION_T STATUS
---------- ------------------------------ ---------------------------------------------------------------------- ---------- ---------- -------
         1 SYSTEM                         /u01/app/oracle/oradata/DBdb/system01.dbf                                    2800 2013-08-24 SYSTEM
         2 SYSAUX                         /u01/app/oracle/oradata/DBdb/sysaux01.dbf                                     710 2013-08-24 ONLINE
         4 USERS                          /u01/app/oracle/oradata/DBdb/users01.dbf                                  3466.25 2013-08-24 ONLINE
         3 UNDOTBS1                       /u01/app/oracle/oradata/DBdb/undotbs01.dbf                                   2585 2013-08-24 ONLINE
         5 EXAMPLE                        /u01/app/oracle/oradata/DBdb/example01.dbf                                 338.75 2017-04-27 ONLINE
         6 TS_XXF                         /u01/app/oracle/fast_recovery_area/DBDB/newback/ts_xxf_01.dbf                  10 2017-12-07 ONLINE
         7 TST                            /u01/app/oracle/oradata/DBdb/tst.dbf                                           20 2017-12-09 ONLINE

7 rows selected.

SQL> select df.tablespace_name "表空间名",
  2         totalspace "总空间M",
  3         freespace "剩余空间M",
  4         round((1 - freespace / totalspace) * 100, 2) "使用率%"
  5    from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace
  6            from dba_data_files
  7           group by tablespace_name) df,
  8         (select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace
  9            from dba_free_space
 10           group by tablespace_name) fs
 11   where df.tablespace_name = fs.tablespace_name;

表空间名                   总空间M 剩余空间M  使用率%
------------------------------ ---------- ------------- ----------
TS_XXF                                 10             9         10
TST                                    20            19          5
SYSAUX                                710            41      94.23
UNDOTBS1                             2585          2118      18.07
USERS                                3466          3435        .89
SYSTEM                               2800          2027      27.61
EXAMPLE                               339            29      91.45

7 rows selected.

--创建目标库对于源库scott的dblink
SQL> create database link sc connect to scott identified by tiger using 'orcl';

Database link created.

SQL> select * from dba_db_links;

OWNER           DB_LINK         USERNAME                       HOST            CREATED
--------------- --------------- ------------------------------ --------------- ------------
SYS             FTLINK          SCOTT                          20              13-AUG-17
HR              ORA             HR                             orcl            08-DEC-17
SYS             OL              HR                             orcl            09-DEC-17
SYS             SC              SCOTT                          orcl            09-DEC-17

三、目标库执行导入：
导入表（exclude,不包括索引）
impdp \'\/ as sysdba\' remap_schema=scott:hr  REMAP_TABLESPACE=USERS:TS_XXF exclude=index  table_exists_action=TRUNCATE TABLES=scott.a,scott.b network_link=sc

只导入索引（include）
impdp \'\/ as sysdba\' remap_schema=scott:hr  REMAP_TABLESPACE=USERS:TST include=index TABLES=scott.a,scott.b network_link=sc

[oracle@wang ~]$ impdp \'\/ as sysdba\' remap_schema=scott:hr  REMAP_TABLESPACE=USERS:TS_XXF exclude=index  table_exists_action=TRUNCATE TABLES=scott.a,scott.b network_link=sc

Import: Release 11.2.0.4.0 - Production on Sat Dec 9 10:32:50 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

[oracle@wang ~]$   
报错，需要对源库scott用户授予exp_full_database权限。

--源库授权操作：
SQL> conn / as sysdba
Connected.
SQL> grant EXP_FULL_DATABASE to scott;

Grant succeeded.

SQL>

--再次在目标库执行导入操作：
[oracle@wang ~]$ impdp \'\/ as sysdba\' remap_schema=scott:hr  REMAP_TABLESPACE=USERS:TS_XXF exclude=index  table_exists_action=TRUNCATE TABLES=scott.a,scott.b network_link=sc

Import: Release 11.2.0.4.0 - Production on Sat Dec 9 10:37:37 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" remap_schema=scott:hr REMAP_TABLESPACE=USERS:TS_XXF exclude=index table_exists_action=TRUNCATE TABLES=scott.a,scott.b network_link=sc
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "HR"."A"                                        14 rows
. . imported "HR"."B"                                         8 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Dec 9 10:37:50 2017 elapsed 0 00:00:12

[oracle@wang ~]$
[oracle@wang ~]$ impdp \'\/ as sysdba\' remap_schema=scott:hr  REMAP_TABLESPACE=USERS:TST include=index TABLES=scott.a,scott.b network_link=sc

Import: Release 11.2.0.4.0 - Production on Sat Dec 9 10:39:07 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" remap_schema=scott:hr REMAP_TABLESPACE=USERS:TST include=index TABLES=scott.a,scott.b network_link=sc
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Dec 9 10:39:12 2017 elapsed 0 00:00:05

[oracle@wang ~]$

四、目标库验证：
SQL> col SEGMENT_NAME for a15
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES from dba_segments where SEGMENT_NAME in('A','B');

OWNER           SEGMENT_NAME    TABLESPACE_NAME                     BYTES
--------------- --------------- ------------------------------ ----------
HR              B               TS_XXF                              65536
HR              A               TS_XXF                              65536

SQL> select OWNER,INDEX_NAME,TABLESPACE_NAME,STATUS from dba_indexes where table_name in ('A','B');

OWNER           INDEX_NAME                     TABLESPACE_NAME                STATUS
--------------- ------------------------------ ------------------------------ --------
HR              IDX_DEPTNO                     TST                            VALID
HR              IDX_OBJ_ID                     TST                            VALID

SQL>
SQL> conn hr/hr;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
B                              TABLE
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE

9 rows selected.

SQL> select count(*) from a;

  COUNT(*)
----------
        14

SQL> select count(*) from b;

  COUNT(*)
----------
         8

感谢各位的阅读！关于“impdp+network link如何跳过expdp直接导入目标库”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！