Friday, October 27, 2023

Generation of "alter database rename file" scripts

I have written several blog posts where I generate "alter database rename file" statements using the familiar syntax
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