Re: script to find and move omf datafiles?

well... i got a bit further. unfortunately, the failing point is the spool move.bat line. the actual file gets created, but unfortunately it has no text in it (the move command). anyone have any insight? here's the updated script.

echo column file_name new_value data_small >>run.sql echo select file_name from dba_data_files >>run.sql echo where tablespace_name = 'DATA_SMALL'; >>run.sql echo accept small_dest char prompt 'Where should the data files be moved to? '>>run.sql
echo alter tablespace data_small offline; >>run.sql echo spool move.bat >>run.sql
echo select 'move ''&data_small'' ''&small_dest\DATA_SMALL.dbf''' from dual;  >>run.sql
echo spool off >>run.sql
echo host move.bat >>run.sql
echo alter database rename '&data_small' to '&small_dest\DATA_SMALL.dbf';  >>run.sql
echo alter tablespace data_small online; >>run.sql sqlplus /nolog @run.sql

echo alter tablespace xhq_data_small offline; >>run.sql "Akimeu" <akimeu_at_yahoo.comAAA> wrote in message news:eIBse.2163$wV5.945_at_fed1read06...
> thanks for the tip, holger.
>
> however, i have a small problem. the spool does not seem to work as
> expected, therefore i made a small change. here is part of my batch
> script, that generates my sqlplus script that i run at the end.
>
> @echo off
> set data_ts=d:\oracle
> echo column file_name new_value data_small >>run.sql
> echo select file_name from dba_data_files >>run.sql
> echo where tablespace_name = 'DATA_SMALL'; >>run.sql
> echo alter tablespace data_small offline; >>run.sql
> echo host move '&data_small' %data_ts% >>run.sql
> echo alter tablespace data_small online; >>run.sql
> sqlplus /nolog @run.sql
>
> the problem that i'm having here, is that the &data_small does not have
> the correct value (or any value that i can tell) when doing the host move
> command. any ideas?
>
> thanks again.
>
>
>
> "Holger Baer" <holger.baer_at_science-computing.de> wrote in message
> news:d8ul1f$hj3$1_at_news.BelWue.DE...
>> 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 - 10:34:53 CDT