spool show_backup_file_stat.rslt 

set linesize 130;
set pagesize 1000;
set long 50;
set echo on;
break on "Tablespace Name" on "Backup?";

/* ************************************************ */
/*                                                  */
/* Function: Will query the internal Oracle tables  */
/*           to see if tablespaces are in HOT       */
/*           BACKUP mode                            */
/* Creator:  R.Kupcunas                             */
/* Date:     01-May-2000                            */
/*                                                  */
/* ************************************************ */

set echo off;

select substr(TABLESPACE_name,1, 20)					"Tablespace Name",
      decode(A.status, 'ACTIVE', 'IN BACKUP MODE', 'Available')	"Backup?",
	substr(FILE_NAME, 1,50)						"File Name",
	to_char(bytes/(1024*1024), '9,999.99')				"Size (Mb)",
	substr(B.status, 1, 3)						"AVL"
from 	v$backup 		A, 
	sys.dba_data_files 	B
where A.STATUS != 'NOT ACTIVE' 
and B.file_id = A.file#
order by 1 asc,
         2 asc,
         3 asc,
         4 asc;
         
set echo on;
spool off;
set long 80;
commit;










