Re: script to find and move omf datafiles?

Akimeu wrote:
> hello group... my requirement is to write a sql script, that will move
> datafiles around. basically, what i need to do (since we're using omf) is
> retrieve the existing datafile location for a tablespace and rename it to
> the new location, where i'll move it. can i do this in sql*plus
> automatically??? can i use some sort of local variables to store the
> name/location of the existing datafiles which i can then use via the rename
> command??? thanks for any tips/examples.
>
> al
>
>

You could create a script via spool:

column value new_value old_omf
select value from v$parameter where name = 'db_create_file_dest'; spool move_omf
select 'move ' || file_name || ' ' || replace (file_name, '&old_omf', '/your/path/goes/here') from dba_data_files;
spool off

You'll have to modify the script to only select the filenames of the tablespace you're interested in.

Use at your own risk and before you do, please take the time and read the documentation at tahiti.oracle.com so you understand each of the steps involved.

Regards,
Holger Received on Fri Jun 17 2005 - 09:00:13 CDT