/**********************************************************************
 * File:        spcheck.sql  
 * Type:        SQL*Plus script
 * Author:      Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:        17-Dec-01
 *
 * Description:
 *      SQL*Plus script to display status of DBMS_JOB jobs involving
 *	STATSPACK
 *
 * Modifications:
 *********************************************************************/
spool spcheck
set echo off feedback off timing off verify off pagesize 100 lines 80 trimout on trimspool on recsep off
col what format a26 word_wrap
col interval format a15 word_wrap
col sort1 noprint
col mb format 999,999,990.00
col next_date format a9 word_wrap

select	version, instance_name, user from v$instance;

select	job,
	what,
	broken,
	failures,
	lower(interval) interval,
	to_char(next_date, 'DD-MON-RR HH24:MI:SS') next_date
from	user_jobs;

select  tablespace_name tablespace,
        'a' sort1,
        segment_type type,
        sum(bytes)/1048576 mb
from    user_segments
group by tablespace_name, segment_type
union
select  tablespace_name tablespace,
        'b' sort1,
        '-----free------' type,
        sum(bytes)/1048576 mb
from    dba_free_space
where   tablespace_name in
        (select distinct tablespace_name from user_segments)
group by tablespace_name
union
select  tablespace_name tablespace,
        'c' sort1,
        '---allocated---' type,
        sum(bytes)/1048576 mb
from    dba_data_files
where   tablespace_name in
        (select distinct tablespace_name from user_segments)
group by tablespace_name
order by 1, 2, 3;

spool off