溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關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直接導入目標庫”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
