程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle導入導出提示EXP-0006:出現內部不一致的錯誤解決辦法

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

編輯:Oracle教程

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved