impdp+networklink如何跳过expdp直接导入目标库
这篇文章给大家分享的是有关impdp+network link如何跳过expdp直接导入目标库的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。
创新互联长期为上1000+客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为钦州企业提供专业的成都网站建设、成都做网站，钦州网站改版等技术服务。拥有十年丰富建站经验和众多成功案例,为您定制开发。
impdp命令特殊用途，可以将数据库的一个用户迁移到另一台机器上的数据库的用户中。如果目标用户不存在，还可以对应的创建该用户，快速的把A库上的用户迁移到B库上。
下面就来看一下命令格式：
B库下执行命令：（目标库，需要到导入数据的数据库）
Impdpusername/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直接导入目标库”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
当前题目：impdp+networklink如何跳过expdp直接导入目标库
文章转载：http://ybzwz.com/article/pogdsj.html
