1，找到系统datafile存放路径，检查服务器上磁盘空间是否充足

select * from dba_data_files t order by t.tablespace_name;

2，创建表空间及增加表空间

CREATE TABLESPACE UAL3_TBS DATAFILE '/oradata/o46g4/ual3_tbs01.dbf' SIZE 2G autoextend on next 2G maxsize 30G;

ALTER TABLESPACE UAL_LS_E2E_TST_TBS ADD DATAFILE '/oradata2/c37u1/ual_ls_e2e_tst_tbs02.dbf' SIZE 2G autoextend on next 2G maxsize 30G;

3，创建用户赋权

create user ual_src identified by ual_src default tablespace ual_src_tbs temporary tablespace temp;

alter user ual_src quota unlimited on ual_src_tbs;

grant connect,resource,create session,create view to ual_src;

4，创建dirctory

select * from dba_directories;

create directory EXPDP_DIR as '/home/oracle/ebao/expdp_dir';

create directory EXPDP_DIR as '/home/oracle4/dmp';

Grant read,write on directory EXPDP_DIR to public;

5，导出

nohup expdp aig_sg_12_tst/aig_sg_12_tstpwd directory=EXPDP_DIR dumpfile=exp_aig_sg_12_tst`date +%Y%m%d`.dmp logfile=exp_aig_sg_12_tst`date +%Y%m%d`.log exclude=STATISTICS,grant version=11.2.0.2.0&

 

nohup expdp aig_sg_12_tst/aig_sg_12_tstpwd directory=EXPDP_DIR dumpfile=exp_aig_sg_12_tst`date +%Y%m%d`.dmp logfile=exp_aig_sg_12_tst`date +%Y%m%d`.log exclude=STATISTICS,grant,TABLE:\"IN\(\'T1\',\'T2\'\)\" &

nohup expdp AIG_BR_HB_GS/AIG_BR_HB_GSpwd directory=expdp dumpfile=exp_AIG_BR_HB_GS`date +%Y%m%d`.dmp logfile=exp_AIG_BR_HB_GS`date +%Y%m%d`.log exclude=STATISTICS,grant COMPRESSION=ALL &

expdp eas/kingdee@nskf_localhost schemas=eas dumpfile=expdp2.dmp directory=expdir include=table:\"like \'CT%\'\"

expdp aig_sg_07_pre_tst/aig_sg_07_pre_tstpwd directory=EXPDP_DIR dumpfile=t_clob.dmp logfile=t_clob.log tables=t_clob query=\"where clob_id IN \(1123912\,1123944\) \" exclude=STATISTICS,grant

expdp aig_sg_07_pre_tst/aig_sg_07_pre_tstpwd directory=EXPDP_DIR dumpfile=rtsg_auto_scheme_nb_rate.dmp logfile=rtsg_auto_scheme_nb_rate.log tables=rtsg_auto_scheme_nb_rate exclude=STATISTICS,grant

nohup expdp aig_sg_12_tst/aig_sg_12_tstpwd directory=EXPDP_DIR dumpfile=exp_aig_sg_12_tst`date +%Y%m%d`.dmp logfile=exp_aig_sg_12_tst`date +%Y%m%d`.log exclude=STATISTICS,grant exclude=table:" in(select table_name from tabs where table_name in('EMP','DEPT'))" &

 

exp system/shjd@shjd file=D:\expnewnew.dmp log=D:\expnewnew.log owner=(ams,pvas,pvoas) indexes=y buffer=10240000 grants=y rows=n

常用的过滤SQL表达式

EXCLUDE=SEQUENCE,VIEW --过滤所有的SEQUENCE,VIEW

EXCLUDE=TABLE:"IN ('EMP','DEPT')" --过滤表对象EMP,DEPT

EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" --过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT

EXCLUDE=INDEX:"= 'INDX_NAME'" --过滤指定的索引对象INDX_NAME

INCLUDE=PROCEDURE:"LIKE 'PROC_U%'" --包含以PROC_U开头的所有存储过程(_ 符号代表任意单个字符)

INCLUDE=TABLE:"> 'E' " --包含大于字符E的所有表对象

6,导入

nohup impdp aig_sg_12_tst/aig_sg_12_tstpwd directory=EXPDP_DIR dumpfile=egyprod_%u.dmp logfile=imp_aig_sg_12_tst`date +%Y%m%d`.log remap_schema=egyprod:aig_sg_12_tst remap_tablespace=ACEPRODTS:aig_sg_12_tst_tbs,USERS:aig_sg_12_tst_tbs TRANSFORM=OID:n exclude=STATISTICS,grant&