Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: script to find and move omf datafiles?

Re: script to find and move omf datafiles?

From: Akimeu <akimeu_at_yahoo.comAAA>
Date: Fri, 17 Jun 2005 08:08:55 -0700
Message-ID: <eIBse.2163$wV5.945@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:08:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US