复制数据库_数据库的复制资源-CSDN文库

需积分: 9 166 浏览量 2014-08-28 15:28:39 上传评论收藏 24KB TXT 举报

资源推荐

资源详情

资源评论

在现场实施或者搭建测试环境的过程中，我们需要将一个数据库快速复制到另外一个数据库，这里整理了一组SQL语句用于快速复制
1、在源数据库上执行EXP全导出
exp userid=system/yheyhis file=e:\full.dmp log=e:\full.log full=y direct=y buffer=65536

2、在源数据库上执行以下SQL语句，生成源数据库所有表空间的创建命令，将结果在目标库上执行
select 'CREATE SMALLFILE TABLESPACE "'|| t.tablespace_name
|| '" DATAFILE ''+DATA/ORCL/DATAFILE/' ||t.tablespace_name
|| '01'' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;'
from dba_tablespaces t where t.contents <> 'UNDO' and t.tablespace_name not in ('SYSTEM','SYSAUX','TEMP','USERS');
注意：这里可以根据目标库的磁盘配置情况，自行更改目标库的数据文件存放目录，示例放在e:\oradata\HIS3\下；
其他参数也可根据实际情况适当调整

创建表空间：CREATE SMALLFILE TABLESPACE "TS_HIS3" DATAFILE '+DATA/ORCL/DATAFILE/TS_HIS301' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_JYK" DATAFILE '+DATA/ORCL/DATAFILE/TS_JYK01' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_SIIM" DATAFILE '+DATA/ORCL/DATAFILE/TS_SIIM01' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_EMR_TEMP" DATAFILE '+DATA/ORCL/DATAFILE/TS_EMR_TEMP01' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_YIZHU" DATAFILE '+DATA/ORCL/DATAFILE/TS_YIZHU01' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_EMR_TJFX" DATAFILE '+DATA/ORCL/DATAFILE/TS_EMR_TJFX01' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_EMR_MZ" DATAFILE '+DATA/ORCL/DATAFILE/TS_EMR_MZ01' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 4G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_EMR_GY" DATAFILE '+DATA/ORCL/DATAFILE/TS_EMR_GY01' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_EMR_ZY" DATAFILE '+DATA/ORCL/DATAFILE/TS_EMR_ZY01' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_IIPLAT" DATAFILE '+DATA/ORCL/DATAFILE/TS_IIPLAT01' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_BATJ" DATAFILE '+DATA/ORCL/DATAFILE/TS_BATJ01' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_BAJK" DATAFILE '+DATA/ORCL/DATAFILE/TS_BAJK01' SIZE 100M AUTOEXTEND ON NEXT 500M MAXSIZE 500M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "XUEYE" DATAFILE '+DATA/ORCL/DATAFILE/XUEYE01' SIZE 100M AUTOEXTEND ON NEXT 500M MAXSIZE 500M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_TJGL" DATAFILE '+DATA/ORCL/DATAFILE/TS_TJGL01' SIZE 1100M AUTOEXTEND ON NEXT 500M MAXSIZE 8G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_YYGL" DATAFILE '+DATA/ORCL/DATAFILE/TS_YYGL01' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "TS_CBHS" DATAFILE '+DATA/ORCL/DATAFILE/TS_CBHS01' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;