Oracle導入導出提示EXP-0006:出現內部不一致的錯誤解決辦法

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

　　以前用exp、imp導庫命令成功的實現了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:OracleDBradpcs_DATA.DBF' SIZE 200M REUSE AUTOEXTEND

　　ON NEXT 10M MAXSIZE 16383M EXTENT MANAGEMENT LOCAL UNIFORM

　　SIZE 1024K;

　　--創建索引表空間

　　CREATE TABLESPACE RADPCS_INDX

　　LOGGING

　　DATAFILE 'D:OracleDBradpcs_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窗口會提示導出完成。如果導入過程出錯，會提示錯誤信息(只要數據完整，一些錯誤可以忽略)。