example:利用impdp通过dblink导入数据库
說明
本地數據庫需要10g?以上版本
本地數據庫沒有新建賬戶，直接導入到scott?用戶;
?
impdp?特色
只需要一個dblink?，就能實現將數據庫從一臺機器導入到另外一臺機器
?
以下是測試全過程,?每個小格子，代表了一次嘗試
準備工作
在scott schemas?下創建到TESTDB?的DBlink
create database link TESTDB_aix
connect to TESTDB identified by "TESTDB123!" using 'aix_TESTDB_172';
?
測試DBlink
select * from dual@TESTDB_aix
?
?
| C:/>impdp scott/scott schemas=TESTDB??NETWORK_LINK=TESTDB_aix?EXCLUDE=CONSTRAINT logfile=d:/impdp.log ? Import: Release 10.2.0.1.0 - Production on?星期二, 24 5?月, 2011 10:49:03 ? Copyright (c) 2003, 2005, Oracle.??All rights reserved. ? 連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-31631:?需要權限 ORA-39149:?無法將特權用戶鏈接到非特權用戶 Google?后，將scott?用戶權限調整到TESTDB?一致 ? |
?
?
| ? C:/>impdp scott/scott schemas=TESTDB??NETWORK_LINK=TESTDB_aix??EXCLUD E=CONSTRAINT logfile=d:/impdp.log ? Import: Release 10.2.0.1.0 - Production on?星期二, 24 5?月, 2011 10:49:23 ? Copyright (c) 2003, 2005, Oracle.??All rights reserved. ? 連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-31626:?作業不存在 ORA-31633:?無法創建主表?"SCOTT.SYS_IMPORT_SCHEMA_05" ORA-06512:?在?"SYS.DBMS_SYS_ERROR", line 95 ORA-06512:?在?"SYS.KUPV$FT", line 863 ORA-01536:?超出表空間?'USERS'?的空間限額 ? 增加TESTDB?表空間 alter tablespace users add datafile 'D:/ORACLEAPP/PRODUCT/10.2.0/ORADATA/SDHTEST/USERS02.DBF' size 8G; ? alter tablespace users add datafile 'D:/ORACLEAPP/PRODUCT/10.2.0/ORADATA/SDHTEST/USERS03.DBF' size 8G; ? alter tablespace users add datafile 'D:/ORACLEAPP/PRODUCT/10.2.0/ORADATA/SDHTEST/USERS04.DBF' size 4G; ? grant unlimited tablespace to scott ? |
?
| C:/Users/Administrator>impdp scott/scott schemas=TESTDB?NETWORK_LINK=TESTDB_aix??EXCLUDE=CONSTRAINT logfile=d:/impdp.log ? Import: Release 10.2.0.1.0 - Production on?星期二, 24 5?月, 2011 11:10:42 ? Copyright (c) 2003, 2005, Oracle.??All rights reserved. ? 連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-39002:?操作無效 ORA-39070:?無法打開日志文件。 ORA-39088:?文件名不能包含路徑說明 命令行中未指定directory, 新建?CREATE DIRECTORY dmpdir AS 'D:/OracleApp/product/10.2.0/datapump'; 給scott?賦予在dmpdir?目錄的讀寫權限GRANT read, write ON DIRECTORY dmpdir TO scott; Ps:?也可以使用oracle?默認的dir DATA_PUMP_DIR: SELECT * FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR' ? |
?
| C:/>impdp scott/scott schemas=TESTDB??NETWORK_LINK=TESTDB_aix??EXCLUD E=CONSTRAINT logfile=impdp.log directory=dmpdir ? Import: Release 10.2.0.1.0 - Production on?星期二, 24 5?月, 2011 11:19:18 ? Copyright (c) 2003, 2005, Oracle.??All rights reserved. ? 連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-31631:?需要權限 ORA-39109:?未授權用戶不能對其他用戶的方案進行操作 在導入語句中添加schema?的映射 impdp scott/scott?remap_schema=TESTDB:scott???NETWORK_LINK=TESTDB_ aix??EXCLUDE=CONSTRAINT logfile=impdp.log directory=dmpdir ? |
?
| ? C:/>impdp scott/scott?remap_schema=TESTDB:scott??NETWORK_LINK=TESTDB_ aix??EXCLUDE=CONSTRAINT logfile=impdp.log directory=dmpdir ? ? 失敗的?sql?為: CREATE TABLE "SCOTT"."NGN" ("NETLEVEL" VARCHAR2(255), "GWCATEGORY" VARCHAR2(255) , "HARDWAREVERSION" VARCHAR2(255), "SOFEWAREVERSION" VARCHAR2(255), "TELENETIPAD DR" VARCHAR2(255), "BEGINTIME" DATE, "ANALOGCAPACITY" NUMBER(10,0), "ANALOGACTUA LCAPCITY" NUMBER(10,0), "PSTN2BDACTUALCAPACITY" NUMBER(10,0), "PSTN2BDCAPACITY" NUMBER(10,0), "TOTALCAPACITY" NUMBER(10,0), "ACTUALTOTALCAPACITY" NUMBER(10,0), "NUMOF2MSIGNA ORA-39083:?對象類型?TABLE?創建失敗,?出現錯誤: ORA-00959:?表空間?'SDH_DATA'?不存在 ? 添加從sdh_data?、sdh_index?到users?的映射 impdp scott/scott remap_schema=TESTDB:scott??NETWORK_LINK=TESTDB_ aix??EXCLUDE=CONSTRAINT logfile=impdp.log directory=dmpdirremap_tablespace=sdh_data:users,sdh_index:users ? |
?
| ? C:/>impdp scott/scott remap_schema=TESTDB:scott??NETWORK_LINK=TESTDB_ aix??EXCLUDE=CONSTRAINT logfile=impdp.log directory=dmpdirremap_tablespace=sdh_data:users,sdh_index:users ? ? Import: Release 10.2.0.1.0 - Production on?星期二, 24 5?月, 2011 11:27:05 ? Copyright (c) 2003, 2005, Oracle.??All rights reserved. ;;; 連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options 啟動?"SCOTT"."SYS_IMPORT_SCHEMA_01":??scott/******** remap_schema=TESTDB:scott NETWORK_LINK=TESTDB_aix EXCLUDE=CONSTRAINT logfile=impdp.log directory=dmpdir remap_tablespace=sdh_data:users,sdh_index:users 正在使用?BLOCKS?方法進行估計... 處理對象類型?SCHEMA_EXPORT/TABLE/TABLE_DATA 使用?BLOCKS?方法的總估計: 21.57 GB 處理對象類型?SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 處理對象類型?SCHEMA_EXPORT/DB_LINK 處理對象類型?SCHEMA_EXPORT/SEQUENCE/SEQUENCE 處理對象類型?SCHEMA_EXPORT/TABLE/TABLE . .?導入了?"SCOTT"."CONNECTOR"????????????????????????31396764?行 . .?導入了?"SCOTT"."DNPORTAUDITS"?????????????????????12922037?行 . .?導入了?"SCOTT"."LOG_LIHQ"?????????????????????????2142840?行 ? |
?
?
使用總結：
l??創建?dblink
l??復制源用戶?(?本例源用戶為?TESTDB)?的權限，如果源用戶和本地用戶一直可以省略?schema?映射
l??擴展或者新建表空間，當然夠大就不需要了，上面犯錯是因為事先忘記查看兩邊數據庫表空間大小
l??創建或者使用已用的?directory?，并給本地用戶讀寫權限
總結
以上是生活随笔為你收集整理的example:利用impdp通过dblink导入数据库的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Select For update语句浅
- 下一篇： OGG-00446 ERROR: Cou