REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	c_db_bk_HOT.sql
REM
REM <SQLDIR_GRP>BACKUP UTIL TABSP</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Generates and runs HOT back-up script
REM	backup's one tablespace at a time    
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	c_db_bk_HOT.sql
REM 
REM Example:
REM	c_db_bk_HOT.sql
REM
REM
REM History:
REM	08-01-1999	VMOGILEV	Created
REM
REM

set feedback off
set serveroutput on size 100000
set heading off
set pages 999
set term on
set verify off
ttitle off
btitle off

accept path prompt "Enter BACKUP DIRECTORY name [d:\oradata\ORCL\H_bk\]: "

set term off
col dummy noprint new_value 1

select nvl('&path','d:\oradata\ORCL\H_bk\') dummy
from dual;

spool bk.tmp

declare
  cursor ts_cur is
    select  tablespace_name
    from    dba_tablespaces
    order by tablespace_name;

  cursor dbf_cur (in_ts VARCHAR2) is
    select  file_name
    from    dba_data_files
    where   tablespace_name = in_ts;
begin
    for ts_rec in ts_cur
    loop
     dbms_output.put_line('prompt BACKING UP '||ts_rec.tablespace_name||' tablespace ....');
     dbms_output.put_line('alter tablespace '||ts_rec.tablespace_name||' begin backup;');
     for dbf_rec in dbf_cur(ts_rec.tablespace_name)
     loop
      dbms_output.put_line('prompt ... copying '||dbf_rec.file_name||' to &&1'||substr(dbf_rec.file_name,17)||'');
      dbms_output.put_line('HOST copy '||dbf_rec.file_name||' &&1'||substr(dbf_rec.file_name,17)||'');
     end loop;
    dbms_output.put_line('alter tablespace '||ts_rec.tablespace_name||' end backup;');
    end loop;
end;
/

spool off

set term on
accept dummy prompt "Backup script was generated, do you wish to run it? [Enter: YES, CTL-C: No]: "

@bk.tmp