oracle从新建到导入导出重建

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/[email?protected]_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/[email?protected] 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&

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!