查看帮助
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”