thanks a lot, Rauf. i'll give this puppy a try w/ my mods....
--
al
"Rauf Sarwar" <rs_arwar_at_hotmail.com> wrote in message
news:1119028870.095422.230850_at_g49g2000cwa.googlegroups.com...
>
>
> Rauf Sarwar wrote:
>> 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
>
> Forgot the rename bit. Here is the revised script. Still *untested*.
> Lines may wrap.
>
> @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 linesize 250 >>%runfile%
> echo set trimspool on >>%runfile%
> echo set serverout on size 1000000 >>%runfile%
> echo spool %spoolfile% >>%runfile%
> echo declare >>%runfile%
> echo name_ varchar2(30); >>%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 name_ := substr(rec_.file_name, instr(rec_.file_name, '\',
> -1, 1) + 1, length(rec_.file_name)); >>%runfile%
> echo dbms_output.put_line('alter database rename ''' ^|^|
> rec_.file_name ^|^| ''' to ''' ^|^| '%targetdir%\' ^|^| name_ ^|^|
> ''';'); >>%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%
>
> Regards
> /Rauf
>
Received on Fri Jun 17 2005 - 13:28:02 CDT
