rem -----------------------------------------------------------------------
rem Filename:   end_backup.sql
rem Purpose:    This script will create a file called end_backup_script.sql
rem             - run it to take all tablespaces out of backup mode.
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

column cmd format a80 heading "Text"
set feedback off
set heading  off
set pagesize 0

spool end_backup_script.sql

select   'alter tablespace '||a.tablespace_name||' end backup;' cmd
from     sys.dba_data_files a, sys.v_$backup b
where    b.status = 'ACTIVE'
and      b.file#  = a.file_id
group by a.tablespace_name
/

spool off

set feedback on
set heading  on
set pagesize 24
set termout  on

start end_backup_script.sql