--use the SQL in this file
--to generate scripts to rename tablespaces
--NR Dec 2002
select t.name, d.name from v$datafile d, v$tablespace t
where t.ts#=d.ts# ;
echo on
echo alter tablespace ROLLBACK offline; > murgh.sql
echo exit >> murgh.sql
sqlplus sys@&&inst @murgh.sql
copy E:\ORACLE\ORADATA\DATA\TRN3\RBACK1.DBF
e:\oracle\oradata\data\trn3\\DATA\TRN3\RBACK1.DBF
echo alter tablespace ROLLBACK rename datafile
'E:\ORACLE\ORADATA\DATA\TRN3\RBACK1.DBF' to
'e:\oracle\oradata\data\trn3\\DATA\TRN3\RBACK1.DBF'; > murgh.sql
echo alter tablespace ROLLBACK online; >> murgh.sql
echo exit >> murgh.sql
sqlplus sys@&inst @murgh.sql
;
--data-data files
select
'echo on ' || chr(13) ||
'echo alter tablespace ' || t.name || ' offline; > murgh.sql' || chr(13)
||
'echo exit >> murgh.sql' || chr(13) ||
'sqlplus sys@&inst @murgh.sql' || chr(13) ||
'copy ' || d.name || ' e:\oracle\oradata\data\&inst\'
|| substr(d.name, &ln,2000) || chr(13) ||
'echo alter tablespace ' || t.name || ' rename datafile ''' || d.name ||
''' to '''
|| 'e:\oracle\oradata\data\&inst\' || substr(d.name, &ln,2000) ||
'''; > murgh.sql'
|| chr(13) ||
'echo alter tablespace ' || t.name || ' online; >> murgh.sql' || chr(13)
|| 'echo exit >> murgh.sql' || chr(13)
|| 'sqlplus sys@&inst @murgh.sql' || chr(13)
as "rem SCRIPT"
from v$datafile d, v$tablespace t
where t.ts#=d.ts#
and d.name not like 'E:\ORACLE\ORADATA\DATA\&inst\%'
and t.name not like '%IND%' and t.name not in ('SYSTEM','ROLLBACK')
;
--index-data files
select
'echo on ' || chr(13) ||
'echo alter tablespace ' || t.name || ' offline; > murgh.sql' || chr(13)
||
'echo exit >> murgh.sql' || chr(13) ||
'sqlplus sys@&inst @murgh.sql' || chr(13) ||
'copy ' || d.name || ' G:\oracle\oradata\index\&inst\'
|| substr(d.name, &ln,2000) || chr(13) ||
'echo alter tablespace ' || t.name || ' rename datafile ''' || d.name ||
''' to '''
|| 'G:\oracle\oradata\index\&inst\' || substr(d.name, &ln,2000) ||
'''; > murgh.sql'
|| chr(13) ||
'echo alter tablespace ' || t.name || ' online; >> murgh.sql' || chr(13)
|| 'echo exit >> murgh.sql' || chr(13)
|| 'sqlplus sys@&inst @murgh.sql' || chr(13)
as "rem SCRIPT"
from v$datafile d, v$tablespace t
where t.ts#=d.ts#
--and d.name not like 'E:\ORACLE\ORADATA\DATA\TRN3%'
and t.name like '%IND%' and t.name not in ('SYSTEM','ROLLBACK')
;
--rollback
select
'echo on ' || chr(13) ||
'echo alter tablespace ' || t.name || ' offline; > murgh.sql' || chr(13)
||
'echo exit >> murgh.sql' || chr(13) ||
'sqlplus sys@&inst @murgh.sql' || chr(13) ||
'copy ' || d.name || ' d:\oracle\oradata\rback\&inst\'
|| substr(d.name, &ln,2000) || chr(13) ||
'echo alter tablespace ' || t.name || ' rename datafile ''' || d.name ||
''' to '''
|| 'd:\oracle\oradata\rback\&inst\' || substr(d.name, &ln,2000) ||
'''; > murgh.sql'
|| chr(13) ||
'echo alter tablespace ' || t.name || ' online; >> murgh.sql' || chr(13)
|| 'echo exit >> murgh.sql' || chr(13)
|| 'sqlplus sys@&inst @murgh.sql' || chr(13)
as "rem SCRIPT"
from v$datafile d, v$tablespace t
where t.ts#=d.ts#
and d.name not like 'E:\ORACLE\ORADATA\DATA\&inst\%'
and t.name ='ROLLBACK'
;
Note: Proofread any scripts before using. Always try scripts on a test
instance
first. I'm not responsible for any damage, even if you somehow manage to make my
scripts corrupt every last byte of your data, set your server on fire and serve you
personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...