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 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;

   命令：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窗口会提示导出完成。如果导入过程出错，会提示错误信息（只要数据完整，一些错误可以忽略）。