select 'alter database move datafile ''' || file_name || ''' TO ''' || replace(file_name,'old_sid','new_sid') || ''';'Recently, an experienced co-worker showed me another version of the script which is, in my opinion, much simpler:
select 'alter database move datafile ' ||chr(39) || file_name || chr(39) || ' TO ' || chr(39) || replace(file_name,'old_sid','new_sid') || chr(39)|| ';' from dba_data_files;By referring to chr(39) instead of masking the char ' with the same character, your script becomes simpler to read and less error-prone. This will be particulary important as your scripts get more complex.
The same method can of course be applied when generating scripts for moving table partitions, indexes etc.
No comments:
Post a Comment