exp、imp是ORACLE幸存的最古老的兩個操作系統(tǒng)命令行工具，導(dǎo)入(IMP)/導(dǎo)出(EXP)，昨天在Oracle用exp、imp導(dǎo)入導(dǎo)出報EXP-0006:出現(xiàn)內(nèi)部不一致的錯誤。

以前用exp、imp導(dǎo)庫命令成功的實現(xiàn)了Oracle數(shù)據(jù)庫的導(dǎo)出、導(dǎo)入，對此，昨天再對服務(wù)器新安裝的Oracle數(shù)據(jù)庫用exp、imp導(dǎo)庫，一直報“EXP-0006：出現(xiàn)內(nèi)部不一致的錯誤”，于是在網(wǎng)上百度，嘗試其他導(dǎo)庫方式，發(fā)現(xiàn)采用expdp、impdp數(shù)據(jù)泵同樣可以完成數(shù)據(jù)庫的導(dǎo)出、導(dǎo)入，而且數(shù)據(jù)泵與傳統(tǒng)導(dǎo)出導(dǎo)入有如下區(qū)別：

1.EXP和IMP是客戶段工具程序， EXPDP和IMPDP是服務(wù)端的工具程序;

2.EXP和IMP效率比較低. EXPDP和IMPDP效率高;

3.數(shù)據(jù)泵功能強大并行、過濾、轉(zhuǎn)換、壓縮、加密、交互等等;

4.數(shù)據(jù)泵不支持9i以前版本， EXP/IMP短期內(nèi)還是比較適用;

5.同exp/imp數(shù)據(jù)泵導(dǎo)出包括導(dǎo)出表,導(dǎo)出方案,導(dǎo)出表空間,導(dǎo)出數(shù)據(jù)庫4種方式。

有了理論支持，下面開始實戰(zhàn)。

expdp導(dǎo)出Oracle數(shù)據(jù)庫

1.在sqlplus下創(chuàng)建Directory， 優(yōu)點在于讓我們可以在Oracle數(shù)據(jù)庫中靈活的對文件進行讀寫操作，極大的提高了Oracle的易用性和可擴展性。

命令：createdirectory oracleDB as 'D:\OracleDB';

2.把讀寫權(quán)限授予特定用戶

命令：Grantread,write on directory oracleDB to radpcs;

3.在dos窗口執(zhí)行expdp導(dǎo)出命令

命令：expdp directory=oracleDB dumpfile =20150226.dmp logfile=20150226.logFULL=y;

到此導(dǎo)出工作完成，下面講解如何用impdp導(dǎo)入Oracle數(shù)據(jù)庫。

impdp導(dǎo)入Oracle數(shù)據(jù)庫

1.以sysdba級別登錄Oracle數(shù)據(jù)庫

命令：--sqlplus /nolog

--conn as sysdba

2.創(chuàng)建數(shù)據(jù)表空間

命令：

--創(chuàng)建數(shù)據(jù)表空間

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;

--創(chuàng)建索引表空間

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;

這步很關(guān)鍵，創(chuàng)建的表空間需要和原Oracle表空間名稱、個數(shù)相同，否則會導(dǎo)入失敗。如果不知道原表空間名稱、個數(shù)，就先創(chuàng)建一個臨時的表空間進行導(dǎo)入，導(dǎo)入過程中根據(jù)錯誤提示，比如“RADPCS1_DATA表空間不存在”提示，缺哪個創(chuàng)建哪個。

3.創(chuàng)建用戶、對用戶授權(quán)

--創(chuàng)建用戶

create user radpcs identified by ictradpcs

default tablespace radpcs_data

quota unlimited on radpcs_data

quota unlimited on radpcs_indx;

--授權(quán)

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下創(chuàng)建Directory

命令：createdirectory oracleDB as 'D:\OracleDB';

5.把讀寫權(quán)限授予特定用戶

命令：Grantread,write on directory oracleDB to radpcs;

6.在dos窗口執(zhí)行impdp導(dǎo)入命令

命令：impdp directory=oracleDB dumpfile=20150226.dmp logfile=20150226.log;

漫長的等待后，dos窗口會提示導(dǎo)出完成。如果導(dǎo)入過程出錯，會提示錯誤信息(只要數(shù)據(jù)完整，一些錯誤可以忽略)。

更多信息請查看IT技術(shù)專欄