oracle使用expdp和impdp搭建数据库

查看帮助

expdp -help

PARALLEL 是并行数，expdp和impdp都可以使用这个参数

--expdp导出：
1.创建数据泵路径：
SQL>  create or replace directory imostdb as '/opt/oracle/imost';    
---Directory created.  //需确保/home/目录空间足够大（提前进行备份验证）

2.赋予导出用户“导入导出”的权限
grant connect to imostdb_cs;
grant dba to imostdb_cs;
grant exp_full_database to imostdb_cs;
grant imp_full_database to imostdb_cs;
grant resource to imostdb_cs;

3.导出数据：（dmp文件在数据泵路径下）
expdp userid=imostdb_cs/'%TGB6yhn' directory=imostdb dumpfile=Expdp_imost_20150318.dmp LOGFILE=Expdp_imost_20150318.log SCHEMAS=PROMOTION CONTENT=METADATA_ONLY;

--需要注意的地方有：
1.userid用到的用户和密码是否正确并具有导出导入权限      
2.directory用到的路径是否存在并保证空间充裕且userid用到的用户对该路径拥有读写权限
3.用户模式导出使用SCHEMAS，除此之外还有表模式，表空间模式，全库模式等（网上可以找到很多模板的）
4.CONTENT参数值，因为本次仅导出数据结构所以我们使用的是METADATA_ONLY，该参数的默认值就是ALL

--impdp导入
第一步到第三步请使用具有dba权限的用户执行

第一步：创建用户所在的profile
-- Create profile 
create profile SDPUSER limit
  sessions_per_user unlimited
  cpu_per_session unlimited
  cpu_per_call unlimited
  connect_time unlimited
  idle_time unlimited
  logical_reads_per_session unlimited
  logical_reads_per_call unlimited
  composite_limit unlimited
  private_sga unlimited
  failed_login_attempts unlimited
  password_life_time unlimited
  password_reuse_time unlimited
  password_reuse_max unlimited
  password_lock_time unlimited
  password_grace_time unlimited
  password_verify_function Null;

第二步：创建用户并赋权
-- Create the user 
create user PROMOTION identified by "ChPromo123"
  default tablespace promotion
  temporary tablespace TEMP
  profile SDPUSER;

-- Grant/Revoke role privileges 
grant connect to PROMOTION;
grant dba to PROMOTION;
grant exp_full_database to PROMOTION;
grant imp_full_database to PROMOTION;
grant resource to PROMOTION;

-- Grant/Revoke system privileges 
grant alter any trigger to PROMOTION;
grant alter any type to PROMOTION;
grant create any directory to PROMOTION;
grant create any table to PROMOTION;
grant create any trigger to PROMOTION;
grant create any type to PROMOTION;
grant create any view to PROMOTION;
grant create job to PROMOTION;
grant create procedure to PROMOTION;
grant create sequence to PROMOTION;
grant create session to PROMOTION;
grant drop any table to PROMOTION;
grant drop any trigger to PROMOTION;
grant drop any type to PROMOTION;
grant drop any view to PROMOTION;
grant insert any table to PROMOTION;
grant unlimited tablespace to PROMOTION;

第三步：创建导入导出路径并赋权
create or replace directory LOCAL_TRANSFER as '/opt/oracle/backup';
grant read,write on directory LOCAL_TRANSFER to promotion;

第四步：在后台使用oracle用户执行如下导入语句
（请注意确认用户，表空间以及dmp文件信息）
impdp userid=promotion/'userpasswd' directory=LOCAL_TRANSFER dumpfile=Expdp_promotion_20150317.dmp LOGFILE=Impdp_promotion_20150317.log remap_tablespace=TS_PMT_DATA:PROMOTION,TS_SDP_TEMP:TEMP,TS_PMT_IDX:PROMOTION_INDEX SCHEMAS=PROMOTION

--注：需要注意的地方
1.导入的用户最还与原来保持一致，用户权限要赋足
2.以上都需执行，且dmp文件要放在创建的数据泵路径下
3.如果上面impdp导入出问题，执行“impdp userid=promotion/'userpasswd' directory=LOCAL_TRANSFER dumpfile=Expdp_promotion_20150317.dmp”

Oracle-impdp&expdp总结