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

数据库 2024年04月11日 6:01

评论»

这篇文章给大家分享的是有关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 20TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-AUG-2017 23:51:59Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting 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.创建对于源库的dblinkSQL> create public database link test connect to scott identified by tiger using '20';Database link created.验证：SQL> set lines 200 pages 999SQL> col db_link for a10SQL> col host for a10SQL> select OWNER,DB_LINK,USERNAME,HOST,CREATED from dba_db_links;OWNER DB_LINK USERNAME HOST CREATED------------------------------ ---------- ------------------------------ ---------- ------------PUBLIC TEST SCOTT 20 06-AUG-17注意：创建公用dblink3.创建导入的用户：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=testImport: Release 11.2.0.4.0 - Production on Sun Aug 6 00:54:06 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsFLASHBACK 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_DATATotal estimation using BLOCKS method: 256 KBProcessing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:"HH" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing 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 rowsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "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 TABLEDBXL TABLEDEPT TABLEEMP TABLESALGRADE TABLETEST TABLETEST123 TABLE7 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 TABLEDEPARTMENTS TABLEEMPLOYEES TABLEJOBS TABLEJOB_HISTORY TABLELOCATIONS TABLEREGIONS TABLE7 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-17HR ORA HR orcl 08-DEC-17SYS OL HR orcl 09-DEC-17二、源库检查：--检查要导出的测试表SQL> conn hr/hr;Connected.SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------COUNTRIES TABLEDEPARTMENTS TABLEEMPLOYEES TABLEJOBS TABLEJOB_HISTORY TABLELOCATIONS TABLEREGIONS TABLESYNO TABLE8 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=olImport: Release 11.2.0.4.0 - Production on Sat Dec 9 08:54:17 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-31631: privileges are requiredORA-39149: cannot link privileged user to non-privileged user[oracle@wang ~]$ 报错：需要授予源库要导出的用户exp_full_database权限。--源库授权：SQL> conn / as sysdbaConnected.SQL> grant EXP_FULL_DATABASE to hr;Grant succeeded.--再次在目标库执行导入：[oracle@wang ~]$ impdp \'\/ as sysdba\' tables=hr.SYNO network_link=olImport: Release 11.2.0.4.0 - Production on Sat Dec 9 09:03:34 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . imported "HR"."SYNO" 35 rowsProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSJob "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 sysdbaConnected.SQL> set lines 200SQL> 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 VALIDSQL> conn hr/hr;Connected.SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------COUNTRIES TABLEDEPARTMENTS TABLEEMPLOYEES TABLEJOBS TABLEJOB_HISTORY TABLELOCATIONS TABLEREGIONS TABLESYNO TABLE8 rows selected.SQL> select count(*) from syno; COUNT(*)---------- 35实验三源库、目标库参照实验二任务：将源库下的scott用户下的表a、b导入到目标库的hr用户下，且表的表空间映射为TS_XXF，索引的表空间映射为TST一、源库操作，模拟测试表a、bSQL> conn scott/tiger;Connected.SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------BONUS TABLEDEPT TABLEEMP TABLESALGRADE TABLESQL> 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(*)---------- 14SQL> select count(*) from b; COUNT(*)---------- 8SQL> conn / as sysdbaConnected.SQL> col SEGMENT_NAME for a15SQL> 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 65536SCOTT A USERS 65536SQL> 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 VALIDSCOTT IDX_OBJ_ID USERS VALID二、目标库查询：SQL> col name for a70SQL> set lines 200 pages 999SQL> 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 ONLINE7 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_space10 group by tablespace_name) fs11 where df.tablespace_name = fs.tablespace_name;表空间名总空间M 剩余空间M 使用率%------------------------------ ---------- ------------- ----------TS_XXF 10 9 10TST 20 19 5SYSAUX 710 41 94.23UNDOTBS1 2585 2118 18.07USERS 3466 3435 .89SYSTEM 2800 2027 27.61EXAMPLE 339 29 91.457 rows selected.--创建目标库对于源库scott的dblinkSQL> 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-17HR ORA HR orcl 08-DEC-17SYS OL HR orcl 09-DEC-17SYS 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=scImport: Release 11.2.0.4.0 - Production on Sat Dec 9 10:32:50 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-31631: privileges are requiredORA-39149: cannot link privileged user to non-privileged user[oracle@wang ~]$ 报错，需要对源库scott用户授予exp_full_database权限。--源库授权操作：SQL> conn / as sysdbaConnected.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=scImport: Release 11.2.0.4.0 - Production on Sat Dec 9 10:37:37 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 128 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . imported "HR"."A" 14 rows. . imported "HR"."B" 8 rowsJob "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=scImport: Release 11.2.0.4.0 - Production on Sat Dec 9 10:39:07 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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 KBProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSJob "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 a15SQL> 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 65536HR A TS_XXF 65536SQL> 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 VALIDHR IDX_OBJ_ID TST VALIDSQL> SQL> conn hr/hr;Connected.SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------A TABLEB TABLECOUNTRIES TABLEDEPARTMENTS TABLEEMPLOYEES TABLEJOBS TABLEJOB_HISTORY TABLELOCATIONS TABLEREGIONS TABLE9 rows selected.SQL> select count(*) from a; COUNT(*)---------- 14SQL> select count(*) from b; COUNT(*)---------- 8

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