REM Monitoring and tuning script for Oracle databases all versions
REM This script has no adverse affects. There are no DML or DDL actions taken
REM Parts will not work in all versions but useful info should be returned from other parts
REM Uses anonymous procedures to avoid storing objects in the SYS schema
REM therefore this script must be run as sys 
REM calls to v$parameter need to be moved into subblocks to prevent NO_DATA_FOUND exceptions
REM parameter numbers are different between Oracle Versions
REM
REM The database device report substr call needs t be edited to match the 
REM directories the Oracle datafiles are on
REM
REM For nicer formatting run the following in vi: %s/  *$//
REM This strips the trailing whitespace returned from oracle
REM
REM To map datafile I/O onto physical devices the substr call on line 658 should be 
REM edited. The substr function is used to map to unique mount points, therefore, 
REM the number of characters chosen should equal the length of the directory path to
REM the mount point. Note that if more than one database uses the mount point these 
REM numbers must be summed across instances. Note the GROUP BY clause on line 666 uses 
REM the same substr clause. 
REM
REM These scripts have been collected from many sources and I am sure there are
REM acknowledgements missing from below. Among those are Steve Adams, Rachel Carmichael,
REM Jared Still and other members of the oracle-l mailing list
REM
REM
REM	Unknown authors 	1990 - 1995
REM	Oracle Corporation	1990 - 
REM	Bill Beaton, QC Data		1995
REM	D. Morgan, QC Data		1997
REM	Hari Krishnamoorthy, QC Data	1999
REM	J. J. Wang, Bartertrust		2000
REM	D. Morgan, 1001111 Alberta Ltd.	2002
REM

set pause off
set verify off
set echo off
set term off
set heading off

REM Set up dynamic spool filename
spool tmp7_spool.sql
	select 'spool '||name||'_'||'report'||'_'||to_char(sysdate,'yymondd')||'.dat'
	from sys.v_$database;
spool off

set heading on
set verify on
set term on
set serveroutput on size 1000000
set wrap on
set linesize 200
set pagesize 1000

/**************************************** START REPORT ****************************************************/

/* Run dynamic spool output name */
@tmp7_spool.sql

set feedback off
set heading off

select 'Report Date: '||to_char(sysdate,'Monthdd, yyyy hh:mi')
from dual;

set heading on
prompt =================================================================================================
prompt .                      DATABASE (V$DATABASE) (V$VERSION)
prompt =================================================================================================
select	NAME "Database Name",
	CREATED "Created",
	LOG_MODE "Status"
  from	sys.v_$database;

select	banner "Current Versions"
  from	sys.v_$version;

prompt =================================================================================================
prompt .                      UPTIME (V$DATABASE) (V$INSTANCE)
prompt =================================================================================================

set heading off
column sttime format A30

SELECT NAME, ' Database Started on ',TO_CHAR(STARTUP_TIME,'DD-MON-YYYY "at" HH24:MI')
FROM V$INSTANCE, v$database;
set heading on


prompt .
prompt =================================================================================================
prompt .                      SGA SIZE (V$SGA) (V$SGASTAT)
prompt =================================================================================================
column Size	format 99,999,999,999
select	decode(name,	'Database Buffers',
		'Database Buffers (DB_BLOCK_SIZE*DB_BLOCK_BUFFERS)',
		'Redo Buffers',
		'Redo Buffers     (LOG_BUFFER)', name) "Memory",
		value		"Size"
	from sys.v_$sga
UNION ALL
	select	'------------------------------------------------------'	"Memory",
		to_number(null)		"Size"
  	from	dual
UNION ALL
	select	'Total Memory' "Memory",
		sum(value)	"Size"
  	from	sys.v_$sga;

prompt .
prompt .
prompt Current Break Down of (SGA) Variable Size
prompt ------------------------------------------

column Bytes		format 999,999,999
column "% Used"		format 999.99
column "Var. Size"	format 999,999,999

select	a.name			"Name",
	bytes			"Bytes",
	(bytes / b.value) * 100	"% Used",
	b.value			"Var. Size"
from	sys.v_$sgastat a,
	sys.v_$sga b
where	a.name not in ('db_block_buffers','fixed_sga','log_buffer')
and	b.name='Variable Size'
order by 3 desc;

prompt .

set feedback ON

declare
        h_char          varchar2(100);
        h_char2         varchar(50);
        h_num1          number(25);
        result1         varchar2(50);
        result2         varchar2(50);

        cursor c1 is
        select lpad(namespace,17)||': gets(pins)='||rpad(to_char(pins),9)||
                                     ' misses(reloads)='||rpad(reloads,9)||
               ' Ratio='||decode(reloads,0,0,to_char((reloads/pins)*100,999.999))||'%'
        from v$librarycache;

begin
    dbms_output.put_line
        ('=================================================================================================');
    dbms_output.put_line('.                      SHARED POOL: LIBRARY CACHE (V$LIBRARYCACHE)');
    dbms_output.put_line
        ('=================================================================================================');
    dbms_output.put_line('.');
    dbms_output.put_line('.         Goal: The library cache ratio < 1%' );
    dbms_output.put_line('.');
    
    Begin
        SELECT 'Current setting: '||substr(value,1,30) INTO result1
        FROM V$PARAMETER        
        WHERE NUM = 23;
        SELECT 'Current setting: '||substr(value,1,30) INTO result2
        FROM V$PARAMETER        
        WHERE NUM = 325;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN 
                h_num1 :=1;
    END;
    dbms_output.put_line('Recommendation: Increase SHARED_POOL_SIZE '||rtrim(result1));
    dbms_output.put_line('.                        OPEN_CURSORS '    ||rtrim(result2));
    dbms_output.put_line('.               Also write identical sql statements.');
    dbms_output.put_line('.');
        
    open c1;
    loop
        fetch c1 into h_char;
        exit when c1%notfound;
        
        dbms_output.put_line('.'||h_char);
    end loop;
    close c1;

    dbms_output.put_line('.');

    select lpad('Total',17)||': gets(pins)='||rpad(to_char(sum(pins)),9)||
                                 ' misses(reloads)='||rpad(sum(reloads),9),
               ' Your library cache ratio is '||
                decode(sum(reloads),0,0,to_char((sum(reloads)/sum(pins))*100,999.999))||'%'
    into h_char,h_char2
    from v$librarycache;
    dbms_output.put_line('.'||h_char);
    dbms_output.put_line('.           ..............................................');
    dbms_output.put_line('.           '||h_char2);

    dbms_output.put_line('.');
end;
/

declare
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);
        result1         varchar2(50);

begin
    dbms_output.put_line
        ('=================================================================================================');
        dbms_output.put_line('.                      SHARED POOL: DATA DICTIONARY (V$ROWCACHE)');
    dbms_output.put_line
        ('=================================================================================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: The row cache ratio should be < 10% or 15%' );
        dbms_output.put_line('.');
        dbms_output.put_line('.         Recommendation: Increase SHARED_POOL_SIZE '||result1);
        dbms_output.put_line('.');

        select sum(gets) "gets", sum(getmisses) "misses", round((sum(getmisses)/sum(gets))*100 ,3)
        into h_num1,h_num2,h_num3
        from v$rowcache;

        dbms_output.put_line('.');
        dbms_output.put_line('.             Gets sum: '||h_num1);
        dbms_output.put_line('.        Getmisses sum: '||h_num2);

        dbms_output.put_line('         .......................................');
        dbms_output.put_line('.        Your row cache ratio is '||h_num3||'%');

end;
/

declare
        h_char          varchar2(100);
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);
        h_num4          number(25);
        result1         varchar2(50);
begin
    dbms_output.put_line('.');
    dbms_output.put_line
        ('=================================================================================================');
        dbms_output.put_line('.                      BUFFER CACHE (V$SYSSTAT)');
    dbms_output.put_line
        ('=================================================================================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: The buffer cache ratio should be > 70% ');
        dbms_output.put_line('.');
        Begin
                SELECT 'Current setting: '||substr(value,1,30) INTO result1
                FROM V$PARAMETER        
                WHERE NUM = 125;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN 
                result1 := 'Unknown parameter';
        END;
        dbms_output.put_line('.          Recommendation: Increase DB_BLOCK_BUFFERS '||result1);
        dbms_output.put_line('.');

        select lpad(name,15)  ,value
        into h_char,h_num1
        from v$sysstat
        where name ='db block gets';
        dbms_output.put_line('.         '||h_char||': '||h_num1);

        select lpad(name,15)  ,value
        into h_char,h_num2
        from v$sysstat
        where name ='consistent gets';
        dbms_output.put_line('.         '||h_char||': '||h_num2);

        select lpad(name,15)  ,value
        into h_char,h_num3
        from v$sysstat
        where name ='physical reads';
        dbms_output.put_line('.         '||h_char||': '||h_num3);

        h_num4:=round(((1-(h_num3/(h_num1+h_num2))))*100,3);

        dbms_output.put_line('.          .......................................');
        dbms_output.put_line('.          Your buffer cache ratio is '||h_num4||'%');

    dbms_output.put_line('.');
end;
/

declare
        h_char          varchar2(100);
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);

        cursor buff2 is
        SELECT name
                ,consistent_gets+db_block_gets, physical_reads
                ,DECODE(consistent_gets+db_block_gets,0,TO_NUMBER(null)
                ,to_char((1-physical_reads/(consistent_gets+db_block_gets))*100, 999.999))
        FROM v$buffer_pool_statistics;
begin
     dbms_output.put_line
        ('=================================================================================================');
        dbms_output.put_line('.                      BUFFER CACHE (V$buffer_pool_statistics)');
    dbms_output.put_line
        ('=================================================================================================');

        dbms_output.put_line('.');
        dbms_output.put_line('.');
        dbms_output.put_line('Buffer Pool:         Logical_Reads     Physical_Reads        HIT_RATIO');
        dbms_output.put_line('.');

        open buff2;
        loop
            fetch buff2 into h_char, h_num1, h_num2, h_num3;
            exit when buff2%notfound;

            dbms_output.put_line(rpad(h_char, 15, '.')||'         '||lpad(h_num1, 10, ' ')||'         '||
                lpad(h_num2, 10, ' ')||'       '||lpad(h_num3, 10, ' '));

        end loop;
        close buff2;

    dbms_output.put_line('.');
end;
/

declare
        h_char          varchar2(100);
        h_num1          number(25);
        result1         varchar2(50);

        cursor c2 is
        select name,value
        from v$sysstat
        where name in ('sorts (memory)','sorts (disk)')
        order by 1 desc;

begin
        dbms_output.put_line
                ('=================================================================================================');
        dbms_output.put_line('.                      SORT STATUS (V$SYSSTAT)');
        dbms_output.put_line
                ('=================================================================================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: Very low sort (disk)' );
        dbms_output.put_line('.');
        BEGIN
                SELECT 'Current setting: '||substr(value,1,30) INTO result1
                FROM V$PARAMETER        
                WHERE NUM = 320;
        EXCEPTION
                WHEN NO_DATA_FOUND THEN 
                        result1 := 'Unknown parameter';
        END;
        dbms_output.put_line('           Recommendation: Increase SORT_AREA_SIZE '||result1);
        dbms_output.put_line('.');
        dbms_output.put_line('.');
        dbms_output.put_line(rpad('Name',30)||'Count');
        dbms_output.put_line(rpad('-',25,'-')||'     -----------');

        open c2;
        loop
        fetch c2 into h_char,h_num1;
        exit when c2%notfound;
                dbms_output.put_line(rpad(h_char,30)||h_num1);
        end loop;
        close c2;
end;
/

prompt .
prompt =================================================================================================
prompt .               DATA DICTIONARY CACHE RATIO REPORT (v$rowcache)
prompt =================================================================================================

rem ttitle  'Data Dictionary Getmisses' skip
column getmiss_ratio format 999.99 heading 'Ratio(%)'
select cache#, PARAMETER, count, usage, GETS, 
GETMISSES, GETMISSES*100/(GETS+1) getmiss_ratio
from v$rowcache
order by getmisses desc;
rem ttitle off

prompt .
prompt =================================================================================================
prompt .               NON-SYS/SYSTEM OBJECTS > 25K SHARE MEM (v$db_object_cache)
prompt =================================================================================================
  
       column total_bytes format 9999999 heading 'Total|Bytes'
       column "OBJECT" format A25
       column type format A15

       select    owner || '.' || name OBJECT,
                  type, 
                  to_char(sharable_mem/1024,'9,999.9') "SPACE(K)",
                  loads, 
                  executions execs,
                  kept
       from      v$db_object_cache
      where       owner not in ('SYS','SYSTEM')
       and       kept = 'NO'
       and       sharable_mem > 25000
       and       (executions > 0 or loads >0)
       order by owner, name;

set feedback ON

prompt .
prompt =================================================================================================
prompt .               SYSTEM WAIT EVENTS REPORT (v$system_event)
prompt =================================================================================================


rem ttitle 'High water marks report' skip
       col event format a37 heading 'Event'  
       col total_waits format 99999999 heading 'Total|Waits'  
       col time_waited format 9999999999 heading 'Time Wait|In Hndrds'  
       col total_timeouts format 999999 heading 'Timeout'  
       col average_wait heading 'Average|Time' format 999999.999  
  
       select *  
       from   v$system_event order by total_waits desc;
rem ttitle off



prompt .
prompt =================================================================================================
prompt .               LATCH CONTENTION RPT 1 (v$latchname, v$latch)
prompt =================================================================================================

       column name heading "Latch Type" format a35
       column pct_miss heading "Misses/Gets (%)" format 999.99999
       column pct_immed heading "Immediate Misses/Gets (%)" format 999.99999

rem ttitle  'Misses, Immediate_Misses'

       select  n.name,
                misses*100/(gets+1) pct_miss,
                immediate_misses*100/(immediate_gets+1) pct_immed
       from    v$latchname n,v$latch l
       where   n.latch# = l.latch#
       and     (misses*100/(gets+1) > 0
       or      immediate_misses*100/(immediate_gets+1) > 0);
rem ttitle off
prompt .
prompt =================================================================================================
prompt .               LATCH CONTENTION RPT 2 (v$latch)
prompt =================================================================================================

 column name format a30 heading "LATCH TYPE" 
 column sleep_rate format a10 heading "SLEEP RATE" 
 column impact format 9999999999 heading "IMPACT" 

rem ttitle  'Analysis Report: Misses, Sleeps, Spin_gets' skip

 select 
 name, 
 sleeps * sleeps / (misses - spin_gets) impact, 
 lpad(to_char(100 * sleeps / gets, '990.00') || '%', 10) sleep_rate, 
 waits_holding_latch waits, 
 level# 
 from 
 sys.v_$latch
 where 
 sleeps > 0 
 order by 
 3 desc; 
rem ttitle off

prompt .
prompt =================================================================================================
prompt .               ROLLBACK REPORT (v$rollstat) (v$rollname)
prompt =================================================================================================


rem ttitle 'GET WAIT RATIO ROLLBACK REPORT'
       column "Ratio" format 999.9999
       column name format A15
       column "PERCENT" format 999.9999
       select  name, 
               waits, 
               gets, 
               100-(waits/gets) "Ratio",
               (waits/gets)*100 "PERCENT"
       from    v$rollstat a, v$rollname b  
       where   a.usn = b.usn;  
rem ttitle off



prompt .
prompt =================================================================================================
prompt .               ROLLBACK GENERAL INFORMATION (v$rollstat)
prompt =================================================================================================
rem ttitle 'SHRINKS WRAPS EXTENDS INFORMATION' skip 
       select   rssize,
                optsize,
                hwmsize,
                shrinks,
                wraps,  
                extends,
                aveactive  
       from   v$rollstat  
       order  by rownum;
rem ttitle off


set feedback off

prompt .
prompt =================================================================================================
prompt .               REDO LOG FILE REPORT (v$log)
prompt =================================================================================================

select * from v$log;

prompt .

declare
cursor c3 is
	select a.name,gets,misses,immediate_gets,immediate_misses
	from v$latch a, v$latchname b
	where b.name in ('redo allocation','redo copy')
	and a.latch#=b.latch#;

	h_char	varchar2(100);
	result1	varchar2(50);
	h_num1	INTEGER;
	h_num2  INTEGER;
	h_num3  INTEGER;
	h_num4  INTEGER;
	h_num5  INTEGER;
	h_num6  INTEGER;

begin
    dbms_output.put_line
    	('=================================================================================================');
	dbms_output.put_line('.                      REDO LOG BUFFER LATCHES (V$LATCH, V$SYSSTAT)');
    dbms_output.put_line
    	('=================================================================================================');
	dbms_output.put_line('.');
	dbms_output.put_line('.         Goal: Redo log space request should be near 0' );
	SELECT 'Current setting: '||substr(value,1,30) INTO result1
	FROM V$PARAMETER
	WHERE NUM = 195;
	dbms_output.put_line('.          Recommendation: Increase LOG_BUFFER (5% increments) '||result1);
	dbms_output.put_line('.');
	select value into h_num1
	from sys.v_$sysstat
	where name ='redo log space requests';

	dbms_output.put_line('.');
	dbms_output.put_line('.            Redo log space request: '||h_num1);
	dbms_output.put_line('.');
	open c3;
	loop
		fetch c3 into h_char,h_num1,h_num2,h_num3,h_num4;
		exit when c3%notfound;

		dbms_output.put_line('--------------------------------------------------------------------');
		dbms_output.put_line('.           '||upper(h_char));
		dbms_output.put_line('.');
		dbms_output.put_line('.         Goal: Ratio < 1%' );
		dbms_output.put_line('.         Recommendation: Check Oracle tuning book for more detail ');
		dbms_output.put_line('.');
		dbms_output.put_line('.');
		dbms_output.put_line('.                              gets: '||h_num1);
		dbms_output.put_line('.                            misses: '||h_num2);
		dbms_output.put_line('.                    immediate_gets: '||h_num3);
		dbms_output.put_line('.                  immediate_misses: '||h_num4);
		dbms_output.put_line('.');

		if h_num1 =0 or h_num2 =0 then
			h_num5:=0;
		else
			h_num5:=round((h_num2/h_num1)*100,4);
		end if;

		if h_num4=0 or (h_num3+h_num4)=0 then
			h_num6:=0;
		else
			h_num6:=round((h_num4/(h_num3+h_num4))*100,4);
		end if;

                dbms_output.put_line('.           Ratio (miss)/(gets): '||h_num5||'%');
		dbms_output.put_line('.           Ratio (imm_miss)/(imm_get+imm_miss): '||h_num6||'%');
		dbms_output.put_line('.');
	end loop;
	close c3;
end;
/

prompt .
prompt =================================================================================================
prompt .               REDO LOG SPACE REQUEST RATIO RPT (v$sysstat)
prompt =================================================================================================

rem ttitle  'Redo Log Space Request' skip
column ratio format 9999.9999 heading '.           Ratio: Redo Log Space Request To Redo Entries'

select (req.value*5000)/entries.value ratio
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries';
rem ttitle off


prompt .
prompt =================================================================================================
prompt .                      DATABASE STATISTIC (DBA_DATA_FILES)
prompt =================================================================================================
column Tablespaces		format 9,999
column "Datafiles added"	format 9,999
column "Total Size"		format 999,999,999,999
column "Total Used"		format 999,999,999,999
column "Total Free"		format 999,999,999,999
column "% Used"			format 999.99
select	count(distinct ddf.tablespace_name)				"Tablespaces",
	count(ddf.tablespace_name) - count(distinct ddf.tablespace_name) "Datafiles added",
	sum(ddf.bytes)							"Total Size",
	sum(ddf.bytes) - dfs.free					"Total Used",
	dfs.free							"Total Free",
	((sum(ddf.bytes) - dfs.free) / sum(ddf.bytes)) * 100	"% Used"
from	sys.dba_data_files ddf,
	(select sum(bytes) free from sys.dba_free_space) dfs
group by dfs.free;

prompt .
prompt =================================================================================================
prompt .                      DATABASE FILE - READ AND WRITE STATUS (V$DATAFILE, V$FILESTAT)
prompt =================================================================================================
column Datafile		format a50
column phyrds		format 999,999,999,999 
column phyblkrd		format 999,999,999,999 
column phywrts		format 999,999,999,999 
column phyblkwrt	format 999,999,999,999 
select	name		"Datafile",
	phyrds,
	phyblkrd,
	phywrts,
	phyblkwrt
  from	v$datafile a,
	v$filestat b
 where	a.file#=b.file#
 order by name;

prompt .
prompt =================================================================================================
prompt .                    DATABASE DEVICE - READ AND WRITE STATUS (V$DATAFILE, V$FILESTAT)
prompt =================================================================================================
column Device		format a40
column phyrds		format 999,999,999,999 
column phyblkrd		format 999,999,999,999 
column phywrts		format 999,999,999,999 
column phyblkwrt	format 999,999,999,999 
select	substr(name,1,21)        "Device",
	sum(phyrds) phyrds,
	sum(phyblkrd) phyblkrd,
	sum(phywrts) phywrts,
	sum(phyblkwrt) phyblkwrt
  from	v$datafile a,
	v$filestat b
 where	a.file#=b.file#
 group by substr(name,1,21);


prompt .
prompt =================================================================================================
prompt .                      TABLESPACE USAGE (DBA_DATA_FILES, DBA_FREE_SPACE)
prompt =================================================================================================
column Tablespace	format a30
column Size		format 999,999,999,999
column Used		format 999,999,999,999
column Free		format 999,999,999,999
column "% Used"		format 999.99
select	tablespace_name		"Tablesapce",
        bytes			"Size",
       	nvl(bytes-free,bytes)	"Used",
       	nvl(free,0)		"Free",
       	nvl(100*(bytes-free)/bytes,100)	"% Used"
  from(
	select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
	FROM (select tablespace_name, sum(bytes) bytes
	from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
	where ddf.tablespace_name = dfs.tablespace_name(+)
	group by ddf.tablespace_name, ddf.bytes)
  order by 5 desc;

set feedback off
set heading off
select	rpad('Total',30,'.')		"Tablespace",
  	sum(bytes)			"Size",
       	sum(nvl(bytes-free,bytes))	"Used",
       	sum(nvl(free,0))		"Free",
       	(100*(sum(bytes)-sum(free))/sum(bytes))	"% Used"
  from(
	select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
	FROM (select tablespace_name, sum(bytes) bytes
	from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
	where ddf.tablespace_name = dfs.tablespace_name(+)
	group by ddf.tablespace_name, ddf.bytes);

set feedback on
set heading on

prompt .
prompt =================================================================================================
prompt .                      TABLESPACE FRAGMENTATION (DBA_SEGMENTS)
prompt =================================================================================================
column Tablespace	format a30
column Segments		format 99,999
column Extents		format 99,999
column Total		format 99,999
column "% Growth"	format 99,999

select	a.tablespace_name	"Tablespace",
	count(*)		"Segments",
	sum(extents) - count(*)	"Extents",
	sum(extents)		"Total",
	(decode(sum(extents)-count(*),0,0,(sum(extents)-count(*))/count(*)))*100 "% Growth"
  from	sys.dba_segments a
 group by a.tablespace_name
 order by 3 desc;

prompt .
prompt =================================================================================================
prompt .                      FREE SPACE FRAGMENTATION (DBA_FREE_SPACE)
prompt =================================================================================================
column Tablespace	format a30
column "Available Size"	format 99,999,999,999
column "Fragmentation"	format 99,999
column "Average Size"	format 9,999,999,999
column "   Max"		format 9,999,999,999
column "   Min"		format 9,999,999,999
select tablespace_name Tablespace, 
	count(*) Fragmentation, 
	sum(bytes) "Available Size",
	avg(bytes) "Average size",
	max(bytes) Max, 
	min(bytes) Min
from dba_free_space
group by tablespace_name
order by 3 desc ;

set feedback off
prompt =================================================================================================
prompt .                      SUMMARY OF OBJECTS (excluding SYS and SYSTEM) (DBA_OBJECTS)
prompt =================================================================================================

column Objects	format a35
column Count	format 9,999,999

select	rpad(obj_name,35,'.') "Objects",
	obj_count "Count"
from (select  0 col1, 'Oracle Users' obj_name,
		to_char(count(*) ,'999,999') obj_count
      from  sys.dba_users
      where  username not in ('SYS','SYSTEM')
      group by 'Oracle Users'
UNION
      select  decode(object_type, 'TABLE', 1,'INDEX', 2,'TRIGGER', 3,
		'VIEW',5,'SYNONYM',6,'PACKAGE',7,'PACKAGE BODY',8,
                'PROCEDURE',9,'FUNCTION',10, 100),
	      decode(object_type,     'INDEX','     INDEX',
        	'TRIGGER','     TRIGGER', object_type),
              to_char(count(*) ,'999,999')
       from  sys.dba_objects
       where  owner not in ('SYS','SYSTEM')
       and  object_name != 'TUNE_OBJECT'
       and  object_name not like 'TEMPRPT_%'
       group by object_type
UNION
       select  4, '     CONSTRAINT('||decode(constraint_type, 'C','Check)',
                                                'P','Primary)',
                                                'U','Unique)',
                                                'R','Referential)',
                                                'V','Check View)',
                                                constraint_type||'(' ),
               to_char(count(*) ,'999,999')
        from sys.dba_constraints
        where owner not in ('SYS','SYSTEM')
        group by constraint_type)
 order by col1;

prompt .
prompt ============================================================================================
prompt .                   SUMMARY OF INVALID OBJECTS (DBA_OBJECTS)
prompt ============================================================================================

select owner, object_type, substr(object_name,1,30) object_name, status
from dba_objects
where status='INVALID'
order by object_type;

prompt .
prompt ============================================================================================
prompt .                   LAST REFRESH OF SNAPSHOTS (DBA_SNAPSHOTS)
prompt ============================================================================================

select owner, name, last_refresh 
from dba_snapshots 
where last_refresh < (SYSDATE - 1);


prompt .
prompt ============================================================================================
prompt .                   LAST JOBS SCHEDULED (DBA_JOBS)
prompt ============================================================================================

set arraysize 10
set linesize 65
col what format a65
col log_user format a10
col job format 9999
select job, log_user, last_date, last_sec, next_date, next_sec,
failures, what 
from dba_jobs
where failures > 0;

set linesize 100

prompt .
prompt =================================================================================================
prompt .                      ERROR- These segments will fail during NEXT EXTENT (DBA_SEGMENTS)
prompt =================================================================================================
column Tablespaces	format a30
column Segment		format a40
column "NEXT Needed"	format 999,999,999
column "MAX Available"	format 999,999,999
select	a.tablespace_name	"Tablespaces",
	a.owner			"Owner",
	a.segment_name		"Segment",
	a.next_extent		"NEXT Needed",
	b.next_ext		"MAX Available"
  from	sys.dba_segments a,
	(select tablespace_name,max(bytes) next_ext
	from sys.dba_free_space 
	group by tablespace_name) b
 where	a.tablespace_name=b.tablespace_name(+)
   and	b.next_ext < a.next_extent;

prompt =================================================================================================
prompt .                      WARNING- These segments > 70% of MAX EXTENT (DBA_SEGMENTS)
prompt =================================================================================================
column Tablespace	format a30
column Segment		format a40
column Used		format 9999
column Max		format 9999
select	tablespace_name	"Tablespace",
	owner		"Owner",
	segment_name	"Segment",
	extents		"Used",
	max_extents	"Max"
  from	sys.dba_segments
 where	(extents/decode(max_extents,0,1,max_extents))*100 > 70
   and	max_extents >0;

prompt =================================================================================================
prompt .                      LIST OF OBJECTS HAVING > 12 EXTENTS (DBA_EXTENTS)
prompt =================================================================================================
column Tablespace_ext	format a30
column Segment		format a40
column Count		format 9999
break on "Tablespace_ext" skip 1
select	tablespace_name "Tablespace_ext" ,
	owner		"Owner",
	segment_name    "Segment",
	count(*)        "Count"
  from	sys.dba_extents
 group by tablespace_name,owner,segment_name
 having count(*)>12
 order by 1,3 desc;

prompt .
prompt =================================================================================================
prompt .                      LIST OF INIT PARAMETER SETTING (V$PARAMETER)
prompt =================================================================================================
column Parameter format a41
column Value format a40
column Default format a7
column ID format 999
select  NAME            "Parameter",
        VALUE           "Value",
        ISDEFAULT       "Default",
        NUM             "ID"
  from  sys.v_$parameter
 order by 1;

prompt =================================================================================================
prompt End of Report

spool off

/* Remove temp spool scripts */
# host rm tmp7_*.sql

exit;