Re: script to find and move omf datafiles?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 17 Jun 2005 09:41:08 -0700
Message-ID: <1119026468.699724.188330@g14g2000cwa.googlegroups.com>

Akimeu wrote:
> 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
> >
> >

As others have said, take good backup before attempting this *untested* script. Copy it in a batch script and run. Make sure to change set values in the beginning of the script.

• Copy Start ------ @echo off

set oracle_sid=your_sid
set runfile=run.sql
set spoolfile=spool.sql
set sqlexe=c:\oracle\ora92\bin\sqlplus.exe set targetdir=c:\target
set tblspace=TABLESPACE_NAME_IN_UPPERCASE

echo connect / as sysdba >%runfile%
echo set pagesize 0 >>%runfile%
echo set echo off >>%runfile%
echo set verify off >>%runfile%

echo set feedback off >>%runfile%
echo set heading off >>%runfile%
echo set trimspool on >>%runfile%

echo set serverout on size 1000000 >>%runfile% echo spool %spoolfile% >>%runfile%
echo declare >>%runfile%
echo cursor cur_ is >>%runfile%

echo       select file_name >>%runfile%
echo       from dba_data_files >>%runfile%
echo       where upper(tablespace_name) = '%tblspace%'; >>%runfile%

echo begin >>%runfile%
echo dbms_output.put_line('alter tablespace %tblspace% offline;');
>>%runfile%

echo for rec_ in cur_ loop >>%runfile% echo dbms_output.put_line('host move ^"' ^|^| rec_.file_name ^|^| '^" ^"%targetdir%^"'); >>%runfile%
echo end loop; >>%runfile%
echo dbms_output.put_line('alter tablespace %tblspace% online;');
>>%runfile%

echo end; >>%runfile%
echo / >>%runfile%
echo spool off >>%runfile%
echo start %spoolfile%
echo exit >>%runfile%

%sqlexe% /nolog @%runfile%
----- Copy End ------

Regards
/Rauf Received on Fri Jun 17 2005 - 11:41:08 CDT