rem Script: dbb_overview.sql
rem Adapted from script by Julie Dunnam/SES/SCT from 1998 SCT Summit.
rem Used by dbbackup.shl to produce overview statistics for PROD.
rem
rem Purpose: This script can help with getting a quick overview of the
rem physical structure of a client's database.  It gives detail on:
rem 	
rem   Database layout (redo logs, datafiles, controlfiles)
rem   Tablespace Stats  
rem   Extent Problems
rem
set show off echo off timing off time off ver off feed off 
set heading on
col segment_name format a25
col segment_type format a12
col tablespace_name format a15
col file_name format a40
col member format a43
col free format 9,999,999,999
col "Tot Size" format 9,999,999,999
col bytes format 9,999,999,999
set termout off
column dbsvalue new_value dbsp
select value dbsvalue from v$parameter where name ='db_block_size';
set termout on
spool overview.lst
select * from v$database
/
select rpad(c.name||':',11) ||
       rpad(' current logons=' || (to_number(b.sessions_current)-1),20) ||
       'cumulative logons=' || rpad(substr(a.value,1,10),10) ||
       'highwater mark=' || b.sessions_highwater "Daily Login Information"
from v$sysstat a, v$license b, v$database c
where a.name = 'logons cumulative'
/
select * from v$sga
/
set numw 8
set heading off
select '=========================================' from dual;
select  name, ' - Database Structure' from v$database;
select '=========================================' from dual;
set heading on
col name format a40
select file_name, file_id, bytes from sys.dba_data_files
order by substr(file_name,6)
/
select member, a.group#, bytes, a.status
from v$logfile a, v$log b
where a.group# = b.group#
order by substr(member,6)
/
select * from v$controlfile
order by substr(name,6)
/
set heading off
select '===================================================' from dual;
select '  Possible extent problems (>50 or extent+5 >= max)' from dual;
select '===================================================' from dual;
set heading on
select segment_name, segment_type, tablespace_name, extents, max_extents,
  decode(sign(extents-90),1,' * ','   ') ">90"
from sys.dba_segments
where extents + 5 > max_extents
  or  extents > 50
order by segment_name, segment_type desc
/
set heading off
select '===================================================' from dual;
select '  Database Fragmentation Statistics' from dual;
select '===================================================' from dual;
set heading on
column ID format 99
col free format 9,999,999
select substr(ts.name,1,11) TABLESPACE,
       TF.FILE# ID,
       MAX(TF.BLOCKS)  BLOCKS,
       SUM(F.LENGTH)  FREE,
       COUNT(*)  PIECES,
       MAX(F.LENGTH)  BIGGEST,
       MIN(F.LENGTH)  SMALLEST,
       ROUND(AVG(F.LENGTH))  AVERAGE,
       SUM(DECODE(SIGN(F.LENGTH-5), -1, F.LENGTH, 0)) DEAD
FROM SYS.FET$  F,  SYS.FILE$ TF, SYS.TS$ TS
WHERE ts.ts# = f.ts#
  AND tf.file# = f.file#
GROUp by ts.name, tf.file#
/
col free format 9,999,999,999
set heading off
select '===================================================' from dual;
select '  Database Space Statistics' from dual;
select '===================================================' from dual;
set heading on
select substr(d.tablespace_namE,1,15) TABLESPACE,
       D.FILE_ID FILE_ID,
       D.BYTES/1024/1024  TOT_MBs,
       d.bytes/&dbsp  ORACLE_BLKS,
       sum(e.blocks)  TOT_USED,
       round(sum(e.blocks)/(d.bytes/&dbsp),4) *100 PCT_USED,
       decode(sign(round(sum(e.blocks)/(d.bytes/&dbsp),4) *100-90),1,
       ' *  ','    ') ">90%"
from sys.dba_extents e,
     sys.dba_data_files d
where d.file_id = e.file_id (+)
group by d.tablespace_name, d.file_id, d.byteS
order by d.tablespace_name, pct_used
/
select df.tablespace_name, sum(df.bytes) "Tot Size",
min(dt.next_extent) "Next Extent",
floor((sum(df.bytes)-(3*&dbsp))/min(dt.next_extent)) "Next Extents"
from dba_data_files df, dba_tablespaces dt
where df.tablespace_name = dt.tablespace_name
group by df.tablespace_name
/
set head off
select '               ',sum(bytes) bytes from dba_data_files
/
set head on
select df.tablespace_name, sum(df.bytes) "Free",
min(dt.next_extent) "Next Extent",
sum(floor(df.bytes/dt.next_extent)) "Next Extents"
from dba_free_space df, dba_tablespaces dt
where df.tablespace_name = dt.tablespace_name
group by df.tablespace_name
/
set head off
select '               ',sum(bytes) bytes from dba_free_space
/
spool off
set heading on timing on feedback 6 verify on echo on showmode both
rem !pr3 overview.lst
exit