Re: Dynamically generate sql for hot backup

In article <_39b6.164587$w35.29679886_at_news1.rdc1.nj.home.com>,   "Dmitry Vays" <dimaweiss_at_home.com> wrote:
> Hi everybody,
> I have this script
> select 'alter tablespace ' || tablespace_name || ' begin backup; ' ,
> '! cp ' || file_name || ' $ORA_HOT ', 'alter tablespace ' ||
> tablespace_name || ' end backup
> from dba_data_files
>
> IT WILL PRODUCE THE FOLLOWING :
>
> alter tablespace SYSTEM begin backup;
> ! cp /data1/oradata/eccp/system01.dbf $ORA_HOT
> alter tablespace SYSTEM end backup;
>
> alter tablespace RBS begin backup;
> ! cp /log1/eccp/rbs01.dbf $ORA_HOT
> alter tablespace RBS end backup;
>
> alter tablespace RBS begin backup;
> ! cp /log2/eccp/rbs02.dbf $ORA_HOT
> alter tablespace RBS end backup;
>
> alter tablespace USERS begin backup;
> ! cp /data1/oradata/eccp/users01.dbf $ORA_HOT
> alter tablespace USERS end backup;
>
> If you notice I have 2 datafiles for RBS tablespace.
>
> Can anobody help my to achieve the following result:
>
> alter tablespace RBS begin backup;
> ! cp /log1/eccp/rbs01.dbf $ORA_HOT
> ! cp /log2/eccp/rbs02.dbf $ORA_HOT
> alter tablespace RBS end backup;
>
> Dima
>

Try this:
  SELECT '/* '||tablespace_name||' 1 */'||

         'ALTER TABLESPACE '||tablespace_name||' BEGIN BACKUP;'     FROM dba_tablespaces
  UNION ALL
  SELECT '/* '||tablespace_name||' 2 */'||

         'HOST cp '||file_name||' '||'$ORA_HOT'     FROM dba_data_files WHERE status='AVAILABLE'   UNION ALL
  SELECT '/* '||tablespace_name||' 3 */'||     'ALTER TABLESPACE '||tablespace_name||' END BACKUP;'     FROM dba_tablespaces
  ORDER BY 1; Lev Smirnov
Brainbench MVP for Oracle Admin
http://www.brainbench.com

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 23 2001 - 00:32:25 CST