14年10月份到電子所部署系統時,用exp、imp導庫命令成功的實現了Oracle數據庫的導出、導入,對此,還專門發表過一篇文章Oracle數據庫導出導入,講解導出、導入過程。
昨天再對服務器新安裝的Oracle數據庫用exp、imp導庫,一直報“EXP-0006:出現內部不一致的錯誤”,於是在網上百度,嘗試其他導庫方式,發現采用expdp、impdp數據泵同樣可以完成數據庫的導出、導入,而且數據泵與傳統導出導入有如下區別:
1.EXP和IMP是客戶段工具程序, EXPDP和IMPDP是服務端的工具程序;
2.EXP和IMP效率比較低. EXPDP和IMPDP效率高;
3.數據泵功能強大並行、過濾、轉換、壓縮、加密、交互等等;
4.數據泵不支持9i以前版本, EXP/IMP短期內還是比較適用;
5.同exp/imp數據泵導出包括導出表,導出方案,導出表空間,導出數據庫4種方式。
有了理論支持,下面開始實戰。
expdp導出Oracle數據庫
1.在sqlplus下創建Directory,優點在於讓我們可以在Oracle數據庫中靈活的對文件進行讀寫操作,極大的提高了Oracle的易用性和可擴展性。
命令:createdirectory oracleDB as 'D:\OracleDB';
2.把讀寫權限授予特定用戶
命令:Grantread,write on directory oracleDB to radpcs;
3.在dos窗口執行expdp導出命令
命令:expdp radpcs/ictradpcs@rdpcs directory=oracleDB dumpfile =20150226.dmp logfile=20150226.logFULL=y;
到此導出工作完成,下面講解如何用impdp導入Oracle數據庫。
impdp導入Oracle數據庫
1.以sysdba級別登錄Oracle數據庫
命令:--sqlplus /nolog
--conn system/system@radpcs as sysdba
2.創建數據表空間
命令:
--創建數據表空間
CREATE TABLESPACE RADPCS_DATA
LOGGING
DATAFILE 'D:\OracleDB\radpcs_DATA.DBF' SIZE 200M REUSE AUTOEXTEND
ON NEXT 10M MAXSIZE 16383M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K;
--創建索引表空間
CREATE TABLESPACE RADPCS_INDX
LOGGING
DATAFILE 'D:\OracleDB\radpcs_INDX.DBF' SIZE 200M REUSE AUTOEXTEND
ON NEXT 10M MAXSIZE 16383M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K;
這步很關鍵,創建的表空間需要和原Oracle表空間名稱、個數相同,否則會導入失敗。如果不知道原表空間名稱、個數,就先創建一個臨時的表空間進行導入,導入過程中根據錯誤提示,比如“RADPCS1_DATA表空間不存在”提示,缺哪個創建哪個。
3.創建用戶、對用戶授權
--創建用戶 create user radpcs identified by ictradpcs default tablespace radpcs_data quota unlimited on radpcs_data quota unlimited on radpcs_indx; --授權 grant aq_administrator_role to radpcs; grant aq_user_role to radpcs; grant authenticateduser to radpcs; grant connect to radpcs; grant ctxapp to radpcs; grant dba to radpcs; grant delete_catalog_role to radpcs; grant ejbclient to radpcs; grant execute_catalog_role to radpcs; grant exp_full_database to radpcs; grant gather_system_statistics to radpcs; grant hs_admin_role to radpcs; grant imp_full_database to radpcs; grant javadebugpriv to radpcs; grant javaidpriv to radpcs; grant javasyspriv to radpcs; grant javauserpriv to radpcs; grant java_admin to radpcs; grant java_deploy to radpcs; grant logstdby_administrator to radpcs; grant oem_monitor to radpcs; grant olap_dba to radpcs; grant recovery_catalog_owner to radpcs; grant resource to radpcs; grant select_catalog_role to radpcs; grant xdbadmin to radpcs; -- Grant/Revoke system privileges grant administer database trigger to radpcs; grant alter any cluster to radpcs; grant alter any dimension to radpcs; grant alter any index to radpcs; grant alter any indextype to radpcs; grant alter any library to radpcs; grant alter any outline to radpcs; grant alter any procedure to radpcs; grant alter any role to radpcs; grant alter any sequence to radpcs; grant alter any snapshot to radpcs; grant alter any table to radpcs; grant alter any trigger to radpcs; grant alter any type to radpcs; grant alter database to radpcs; grant alter profile to radpcs; grant alter resource cost to radpcs; grant alter rollback segment to radpcs; grant alter session to radpcs; grant alter system to radpcs; grant alter tablespace to radpcs; grant alter user to radpcs; grant analyze any to radpcs; grant audit any to radpcs; grant audit system to radpcs; grant backup any table to radpcs; grant become user to radpcs; grant comment any table to radpcs; grant create any cluster to radpcs; grant create any context to radpcs; grant create any dimension to radpcs; grant create any directory to radpcs; grant create any index to radpcs; grant create any indextype to radpcs; grant create any library to radpcs; grant create any operator to radpcs; grant create any outline to radpcs; grant create any procedure to radpcs; grant create any sequence to radpcs; grant create any snapshot to radpcs; grant create any synonym to radpcs; grant create any table to radpcs; grant create any trigger to radpcs; grant create any type to radpcs; grant create any view to radpcs; grant create cluster to radpcs; grant create database link to radpcs; grant create dimension to radpcs; grant create indextype to radpcs; grant create library to radpcs; grant create operator to radpcs; grant create procedure to radpcs; grant create profile to radpcs; grant create public database link to radpcs; grant create public synonym to radpcs; grant create role to radpcs; grant create rollback segment to radpcs; grant create sequence to radpcs; grant create session to radpcs; grant create snapshot to radpcs; grant create synonym to radpcs; grant create table to radpcs; grant create tablespace to radpcs; grant create trigger to radpcs; grant create type to radpcs; grant create user to radpcs; grant create view to radpcs; grant debug any procedure to radpcs; grant debug connect session to radpcs; grant delete any table to radpcs; grant drop any cluster to radpcs; grant drop any context to radpcs; grant drop any dimension to radpcs; grant drop any directory to radpcs; grant drop any index to radpcs; grant drop any indextype to radpcs; grant drop any library to radpcs; grant drop any operator to radpcs; grant drop any outline to radpcs; grant drop any procedure to radpcs; grant drop any role to radpcs; grant drop any sequence to radpcs; grant drop any snapshot to radpcs; grant drop any synonym to radpcs; grant drop any table to radpcs; grant drop any trigger to radpcs; grant drop any type to radpcs; grant drop any view to radpcs; grant drop profile to radpcs; grant drop public database link to radpcs; grant drop public synonym to radpcs; grant drop rollback segment to radpcs; grant drop tablespace to radpcs; grant drop user to radpcs; grant execute any indextype to radpcs; grant execute any library to radpcs; grant execute any operator to radpcs; grant execute any procedure to radpcs; grant execute any type to radpcs; grant flashback any table to radpcs; grant force any transaction to radpcs; grant force transaction to radpcs; grant global query rewrite to radpcs; grant grant any object privilege to radpcs; grant grant any privilege to radpcs; grant grant any role to radpcs; grant insert any table to radpcs; grant lock any table to radpcs; grant manage tablespace to radpcs; grant on commit refresh to radpcs; grant query rewrite to radpcs; grant restricted session to radpcs; grant resumable to radpcs; grant select any sequence to radpcs; grant select any table to radpcs; grant under any table to radpcs; grant under any type to radpcs; grant under any view to radpcs; grant unlimited tablespace to radpcs; grant update any table to radpcs; grant select on dba_free_space to radpcs; grant select on dba_data_files to radpcs;
4.在sqlplus下創建Directory
命令:createdirectory oracleDB as 'D:\OracleDB';
5.把讀寫權限授予特定用戶
命令:Grantread,write on directory oracleDB to radpcs;
6.在dos窗口執行impdp導入命令
命令:impdp radpcs/ictradpcs@rdpcs directory=oracleDB dumpfile=20150226.dmp logfile=20150226.log;
漫長的等待後,dos窗口會提示導出命令,是否出錯提示。