IMP跨平台迁移数据库SQL生成SQL方式表空间创建脚本
我们在利用EXP/IMP进行跨平台迁移的时候,由于表空间路径变更,需要先手工创建在目标端创建与源端一致的表空间和数据文件,以下为创建生成语句的脚本。
一.在源端数据库执行以下命令,生成创建脚本
select 'create tablespace ' || b.NAME || ' DATAFILE ' || chr(39) || a.NAME || chr(39) || ' SIZE ' || a.BYTES / 1024 / 1024 || ' m;' from v$datafile a, v$tablespace b where a.ts# = b.TS# And b.INCLUDED_IN_DATABASE_BACKUP='YES' Union All select 'Create Temporary Tablespace ' || b.NAME || ' Tempfile ' || chr(39) || a.NAME ||chr(39) || ' SIZE ' || a.BYTES / 1024 / 1024 || ' m;' from v$tempfile a, v$tablespace b where a.ts# = b.TS# And b.INCLUDED_IN_DATABASE_BACKUP='NO' Union All select 'alter database datafile ' ||chr(39) ||a.NAME ||chr(39)||' autoextend on ;' from v$datafile a, v$tablespace b where a.ts# = b.TS# And b.INCLUDED_IN_DATABASE_BACKUP='YES' Union All select 'alter database Tempfile ' ||chr(39) ||a.NAME ||chr(39)||' autoextend on ;' from v$tempfile a, v$tablespace b where a.ts# = b.TS# And b.INCLUDED_IN_DATABASE_BACKUP='NO'
二.生成结果类似如下:
create tablespace system datafile ‘/oradata/orcl/system01.dbf‘ size 1000 m;
create tablespace undotbs1 datafile ‘/oradata/orcl/undotbs01.dbf‘ size 1630 m;
create tablespace sysaux datafile ‘/oradata/orcl/sysaux01.dbf‘ size 1000 m;
create tablespace users datafile ‘/oradata/orcl/users01.dbf‘ size 5 m;
......
create tablespace zl9indexdev datafile ‘/oradata/orcl/zl9indexdev.dbf‘ size 20 m;
create tablespace zl9humanbase datafile ‘/oradata/orcl/zl9humanbase.dbf‘ size 20 m;
create tablespace zl9humaninfo datafile ‘/oradata/orcl/zl9humaninfo.dbf‘ size 20 m;
.......
alter database datafile ‘/oradata/orcl/system01.dbf‘ autoextend on ;
alter database datafile ‘/oradata/orcl/undotbs01.dbf‘ autoextend on ;
alter database datafile ‘/oradata/orcl/sysaux01.dbf‘ autoextend on ;
alter database datafile ‘/oradata/orcl/users01.dbf‘ autoextend on ;
......
alter database tempfile ‘/oradata/orcl/zltoolstmp.dbf‘ autoextend on ;
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。