Oracle導庫那些事兒，Oracle導庫事兒

Oracle導庫那些事兒，Oracle導庫事兒

      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 ictradpcsdefault tablespace radpcs_dataquota unlimited on radpcs_dataquota 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視窗會提示匯出完成。如果匯入處理程序出錯，會提示錯誤資訊（只要資料完整，一些錯誤可以忽略）。

   參考文章：http://blog.csdn.net/indexman/article/details/7849401