impdp+network link 跳過expdp直接匯入目標庫
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
完成！！！！！！！！！！！！！！！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2143160/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用impdp network link 跳過expdp直接匯入資料
- expdp透過db_link遠端匯出
- expdp與impdp全庫匯出匯入
- expdp與impdp全庫匯出匯入（二）
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- expdp 全庫匯入報錯總結
- expdp impdp 資料庫匯入匯出命令詳解資料庫
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- 通過EXPDP/IMPDP匯出匯入遠端資料倒本地
- oracle匯入匯出之expdp/impdpOracle
- expdp impdp只匯出匯入viewView
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- python將目標檢測資料匯入到指定資料庫中Python資料庫
- oracle impdp network_link直接匯入資料包ora-39064 ora-29285Oracle
- Oracle 12c expdp和impdp匯出匯入表Oracle
- 將SQLServer表直接匯入Oracle資料庫（圖文教程）SQLServerOracle資料庫
- oracle 10.2.0.4 expdp全庫匯出 和分使用者impdp匯入的記錄Oracle
- 1.python+selenium利用cookie，跳過驗證碼直接登入PythonCookie
- 通過NETWORK_LINK在客戶端執行EXPDP客戶端
- 透過NETWORK_LINK在客戶端執行EXPDP客戶端
- Android - Activity,A,B,C跳過B直接返回AAndroid
- 【命令使用】rsync跳過大目錄
- 通過cmd視窗匯入匯出mysql資料庫MySql資料庫
- expdp/impdp中匯出/匯入任務的管理和監控
- EXP直接匯出壓縮問津，IMP直接匯入壓縮檔案的方法
- EXP，EXPDP資料匯入本地效能測試
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle 11g 透過expdp按日期匯出表Oracle
- expdp 匯出指令碼指令碼
- 一次運維-堡壘機多次跳轉匯出及匯入mysql資料庫運維MySql資料庫
- 不安裝標準件如何直接匯出含有標準件的BOM
- iOS 9 匯入類庫全面詳盡過程（Ruby安裝->CocoaPods安裝->匯入類庫）iOS
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- link和@import匯入css檔案的區別ImportCSS
- expdp 匯出特定物件物件
- AWR跨庫匯出與匯入