Set Linesize 80
Set Pagesize 60
Set Feedback Off
Set Echo Off

Set Heading Off

Column Instance        New_Value _Instance NoPrint
Select Global_Name Instance From Global_Name;

spool &&_instance..doc
 
Select 'Instance   : ' || Global_Name , 'Report Date: '||
       To_char(Sysdate,'Month DD')||', '||
       To_char(Sysdate,'YYYY')||' at '||
       To_char(Sysdate,'HH24:MM:SS')
From Global_Name;

Set Heading On
Define  HitRatio  = 98 (Number)

-- DATA CACHE Hit Ratio INFORMATION

Column Dsk_Rds  Format 999,999,999,990     Heading "Physical Reads"
Column Blk_Gts  Format 999,999,999,999,999,999,990 Heading "DB Block Gets"
Column Con_Gts  Format 999,999,999,990     Heading "Consistent Gets"
Column Hit_Rate Format 990.999              Heading "Hit Ratio"

Prompt Data Cache Hit Ratio Information
Prompt (Increase DB_Block_Buffers if Hit Ratio < &HitRatio%)

Select Sum(Decode(Name, 'physical reads', value, 0)) Dsk_Rds,
       Sum(Decode(Name, 'db block gets', value, 0)) Blk_Gts,
       Sum(Decode(Name, 'consistent gets', value, 0)) Con_Gts,
       ((1 - (Sum(Decode(Name, 'physical reads', value, 0)) /
        (Sum(Decode(Name, 'db block gets', value, 0)) +
         Sum(Decode(Name, 'consistent gets', value, 0))))) * 100) Hit_Rate
From V$SysStat;

-- LIBRARY CACHE Hit Ratio INFORMATION

Column Pins        Format 999,999,999,990 Heading 'Pins'
Column PinHits     Format 999,999,999,990 Heading 'PinHits'
Column PHitRat     Format 990.99      Heading 'Pin Hit|Ratio'
Column Reloads     Format 999,999,999,990 Heading 'Misses'
Column HitRat      Format 990.99      Heading 'Hit |Ratio'

Prompt
Prompt Library Cache Hit Ratio Information
Prompt (Increase Shared Pool if Hit Ratio < &HitRatio%)

Select Sum(Pins) Pins, Sum(PinHits) PinHits, 
       ((Sum(PinHits) / Sum(Pins)) * 100) PHitRat,
       Sum(Reloads) Reloads,
       ((Sum(Pins) / (Sum(Pins) + Sum(Reloads))) * 100)  HitRat
From V$LibraryCache;

-- ROW CACHE Hit Ratio INFORMATION

Column Gets        Format 999,999,999,990 Heading 'Gets'
Column Misses      Format 999,999,999,990 Heading 'Misses'
Column Hit_Ratio   Format 990.99          Heading 'Hit |Ratio'

Prompt
Prompt Row Cache Hit Ratio Information
Prompt (Increase Shared Pool if Hit Ratio < &HitRatio%)

Select Sum(gets) Gets, Sum(getmisses) Misses,
       (1-(Sum(getmisses)/Sum(gets))) * 100 Hit_Ratio
from V$RowCache
;

-- ROLLBACK CONTENTION

set serveroutput on
begin
dbms_output.put_line('');
end;
/

Set Heading Off

Select 'Instance   : ' || Global_Name , 'Report Date: '||
       To_char(Sysdate,'Month DD')||', '||
       To_char(Sysdate,'YYYY')||' at '||
       To_char(Sysdate,'HH24:MM:SS')
From Global_Name;

Set Heading On

Column Class    Format A20                   Heading 'Header Type'
Column Count    Format 999,999,999           Heading 'Number |of waits'
Column Con_Get  Format 9,999,999,999,999.999 Heading 'Logical Reads|In Billions'
Column Pct      Format 990.99                Heading 'Pct of  |Contention'

Define Wait_Ratio = 1 (Number)
 
Prompt RollBack Contention
Prompt (Create more Rollbacks If Pct >1%)

Select A.Class, Count, Sum(Value)/1000000000 Con_Get, 
       ((Count / Sum(Value)) * 100) pct
From V$WaitStat A, V$SysStat B
Where Name In ('db block gets', 'consistent gets')
Group by A.Class, Count 
;

Prompt
Prompt RollBack Tuning 
Prompt Add more RollBacks, If Xacts >1 regularly for any RBS
Prompt OR Waits >0 for any RBS

Column Name      Format A10          Heading 'RBS Name'
Column Status    Format A7           Heading 'Status'
Column Extents   Format 99999        Heading 'Xtnts'
Column Rssize    Format 99,999,999   Heading 'Total Size'
Column Optsize   Format 99,999,999   Heading 'Optimal Size'
Column Xacts     Format 99999        Heading 'Xacts'
Column Gets      Format 999999       Heading 'Gets'
Column Waits     Format 99999        Heading 'Waits'
Column Pct       Format 990.99       Heading '%Gets'

select a.name, status, b.extents, b.rssize, optsize, b.xacts, 
       b.gets, b.waits, ((b.waits / b.gets) * 100) pct
from v$rollname a, v$rollstat b
where a.usn = b.usn;

-- Tablespace Fragmentation report
set serveroutput on
begin
dbms_output.put_line('');
end;
/

Set Heading Off

Select 'Instance   : ' || Global_Name , 'Report Date: '||
       To_char(Sysdate,'Month DD')||', '||
       To_char(Sysdate,'YYYY')||' at '||
       To_char(Sysdate,'HH24:MM:SS')
From Global_Name;

set serveroutput on size 10240
set feedback off
set heading off
column name new_value dbnm noprint

declare
  dbnm    varchar2(8);
  files   number;
  sizem   number;
  freem   number;
  pctf    number;
  pieces  number;
  biggest number;
  lt100k  number;
  dead    number;
  mexts   number;
  nsegs   number;
  gt10e   number;
  gt100e  number;
  moste	  number;
begin

  select
    name
  into dbnm
  from v$database;

  dbms_output.put_line('FREE SPACE - '||RPAD(dbnm,8)||' +------------- FREE ------------+ +------  USED ------+');
  dbms_output.put_line('Tspace       Fl SizeM FreeM %Fre Piecs BigsM <100k Dead NmSegs >10e >100 Most');
  dbms_output.put_line('------------ -- ----- ----- ---- ----- ----- ----- ---- ------ ---- ---- ----');

  for r in (
    select *
    from sys.ts$
    where online$ = 1
    order by name
  ) loop

     select
       count(*),
       sum(blocks)*r.blocksize
     into files, sizem
     from sys.file$ fl, v$dbfile vf
     where fl.ts# = r.ts#
     and fl.file# = vf.file#;	-- make sure dropped files don't show

     select
       sum(length)*r.blocksize,
       decode(sum(length), 0, 100, sum(length)*r.blocksize*100/sizem),
       count(*),
       max(length)*r.blocksize,
       sum(decode(sign(length*r.blocksize-100*1024), 1, 0, 1)),
       sum(decode(sign(length-5), 1, 0, 1))
     into freem, pctf, pieces, biggest, lt100k, dead
     from sys.fet$ fs
     where fs.ts# = r.ts#;

       select
         sum(count(*)),
         sum(decode(sign(max(ext#)-10), -1, 0, 1)),
         sum(decode(sign(max(ext#)-100), -1, 0, 1)),
         max(max(ext#))
       into nsegs, gt10e, gt100e, moste
       from sys.uet$ us
       where us.ts# = r.ts#
       group by segfile#, segblock#;

       dbms_output.put_line(
         rpad(r.name, 12)||' '||
         lpad(ltrim(to_char(files, '99')), 2)||' '||
         lpad(ltrim(to_char(sizem/(1024*1024), '9,990')), 5)||' '||
         lpad(ltrim(to_char(freem/(1024*1024), '9,990')), 5)||' '||
         lpad(ltrim(to_char(pctf, '990'))||'%', 4)||' '||
         lpad(ltrim(to_char(pieces, '9,990')), 5)||' '||
         lpad(ltrim(to_char(biggest/(1024*1024), '9,990')), 5)||' '||
         lpad(ltrim(to_char(lt100k, '9,990')), 5)||' '||
         lpad(ltrim(to_char(dead, '9990')), 4)||' '||
         lpad(ltrim(to_char(nsegs, '99,990')), 6)||' '||
         lpad(ltrim(to_char(gt10e, '9990')), 4)||' '||
         lpad(ltrim(to_char(gt100e, '9990')), 4)||' '||
         lpad(ltrim(to_char(moste, '990')), 4)
       );
  end loop;

end;
/

-- DATA FILE I/O LOAD BALANCING (THE NUMBERS ARE IN MILLIONS)

set Linesize 80
set Pagesize 60
set Feedback off
set Echo off
set Heading on

Column TableSpace_Name Format A10        Heading "Tablespace" wrap
Column Name            Format A20        Heading "File Name" wrap print
Column Phyrds          Format 990.99     Heading "Phys|Reads"
Column Phywrts         Format 990.99     Heading "Phys|Writes"
Column Phyblkrd        Format 990.99     Heading "Phys|Blk Rds"
Column Phyblkwrt       Format 990.99     Heading "Phys|Blk Wrt"
Column Avg_Rd_Time     Format 90.999     Heading "Av Rd |Tim/Blk"
Column Avg_Wrt_Time    Format 90.999     Heading "Av Wrt|Tim/Blk"

Prompt 
Prompt Data File I/O Load Balancing (THE NUMBERS ARE IN MILLIONS)
Prompt (Distribute the datafiles to balance I/O load)

Select C.TableSpace_Name,
       B.Name,
       A.Phyrds/1000000 Phyrds,
       A.Phywrts/1000000 Phywrts,
       A.Phyblkrd/1000000 Phyblkrd,
       A.Phyblkwrt/1000000 Phyblkwrt,
       ((A.ReadTim / Decode(A.Phyrds, 0, 1, A.Phyblkrd)) / 100) Avg_Rd_Time,
       ((A.WriteTim / Decode(A.PhyWrts, 0, 1, A.PhyblkWrt)) / 100) Avg_Wrt_Time
From V$FileStat A, V$DataFile B, Sys.DBA_Data_Files C
Where B.File# = A.File#
  And B.File# = C.File_Id
Order By TableSpace_Name
;

-- USER OBJECTS IN THE SYSTEM TABLESPACE
set serveroutput on
begin
dbms_output.put_line('');
end;
/

Set Heading Off

Select 'Instance   : ' || Global_Name , 'Report Date: '||
       To_char(Sysdate,'Month DD')||', '||
       To_char(Sysdate,'YYYY')||' at '||
       To_char(Sysdate,'HH24:MM:SS')
From Global_Name;

Set Heading On


Column Segment_Name  Format A25         Heading 'Segment'
Column Segment_Type  Format A15         Heading 'Type'
Column Owner         Format A20         Heading 'Owner'

Prompt
Prompt User objects in the SYSTEM tablespace
Prompt (Drop/Move them to other Tablespaces)

Select Segment_Name, Segment_Type, Owner
From   Sys.DBA_Segments
Where  TableSpace_Name = 'SYSTEM'
  And  Owner Not In ('SYS', 'SYSTEM', 'OUTLN')
;

-- SEGMENTS WITH MORE THAN 10 EXTENTS

Column Owner           Format A10           Heading "Owner" 
Column Segment_Name    Format A15           Heading "Segment" wrap
Column Segment_Type    Format A10           Heading "Type"
Column TableSpace_Name Format A15           Heading "Tablespace" wrap
Column Extents         Format 9,990         Heading "# Of|Xtnts" 
Column Max_Extents     Format 9,999,999,990     Heading "Maximum|Xtnts" 

Prompt
Prompt Segments with more than 10 Extents
Prompt (Export, Recreate with larger sizing params and Import)

Select Owner,
       Segment_Name,
       Segment_Type,
       TableSpace_Name,
       Extents,
       Max_Extents
From Sys.DBA_Segments
Where Extents > 10
AND Owner not in ('SYS', 'SYSTEM')
Order By TableSpace_Name
;

-- SEGMENTS THAT ARE WITHIN 15 EXTENTS OF THEIR MAXIMUM

Column Segment_Name    Format A30     Heading "Segment"
Column Segment_Type    Format A10     Heading "Type"
Column TableSpace_Name Format A15     Heading "Tablespace"
Column Extents         Format 999,990 Heading "# Of  |Extents"
Column Max_Extents     Format 999,990 Heading "Maximum|Extents"
 
Prompt
Prompt Segments that are within 15 extents of their maximum
Prompt (Export, Recreate with larger sizing params and Import)

Select Segment_Name,
       Segment_Type,
       TableSpace_Name,
       Extents,
       Max_Extents
From Sys.DBA_Segments
Where Max_Extents - 15 < Extents 
  And Max_Extents != 0
Order By TableSpace_Name
;

-- OBJECTS THAT WILL NOT BE ABLE TO EXTEND DUE TO INSUFFIENT FREE SPACE

Column Segment_Name    Format A30     Heading "Segment"
Column Owner           Format A30     Heading "Owner"
Column Segment_Type    Format A20     Heading "Type"
Column Tablespace_Name Format A30     Heading "Tablespace"
Column Nxt_Ext         Format A10     Heading "   Next|  Extent"
 
Prompt
Prompt Objects that will not be able to extend Due to insuffient Free Space
Prompt (Extend/Add Datafiles to TableSpace)

Select TableSpace_Name, Owner, Segment_Name, Segment_Type,
       To_Char((Next_Extent / 1024), '999,999') || 'K' Nxt_Ext
From  Sys.DBA_Segments A
Where Not Exists (Select 'x'
                  From Sys.DBA_Free_Space B
                  Where A.TableSpace_Name = B.TableSpace_Name
                    And B.Bytes >= A.Next_Extent)
Order By TableSpace_Name, Segment_Name
;

-- Statements in SQL Area with Block Reads > 100000

Prompt
Prompt Statements in SQL Area with Block Reads > 100000

col parsing_user_id	format 9999	heading "Id"
col command_type	format 9999	heading "CmdT"
col disk_reads		format 999,999  heading "Disk|Reads"
col buffer_gets         format 999,999  heading "Buffer|Gets"
col executions		format 999,999	heading "Exe"
col sql_text		format a48	heading "Statement"

select parsing_user_id, command_type,
       disk_reads, buffer_gets, executions, sql_text
from v$sqlarea
where disk_reads > 100000
order by disk_reads;

-- LIST OF INVALID OBJECTS
set serveroutput on
begin
dbms_output.put_line('');
end;
/

Set Heading Off

Select 'Instance   : ' || Global_Name , 'Report Date: '||
       To_char(Sysdate,'Month DD')||', '||
       To_char(Sysdate,'YYYY')||' at '||
       To_char(Sysdate,'HH24:MM:SS')
From Global_Name;

Set Heading On


Column Owner         Format A12 Heading 'Owner'
Column Object_Name   Format A30 Heading 'Name'
Column Object_Type   Format A12 Heading 'Type'
Column Created       Format A9  Heading 'Created'
Column Last_DDL_Time Format A9  Heading 'Last DDL'
 
Prompt 
Prompt List of INVALID objects
Prompt (Compile and/or Recreate)

Select Owner, Object_Name, Object_Type, Created, Last_DDL_Time
From Sys.DBA_Objects
Where Status = 'INVALID'
Order By Owner, Object_Type
;

-- REDO log information

set serveroutput on
begin
dbms_output.put_line('');
end;
/

Set Heading Off

Select 'Instance   : ' || Global_Name , 'Report Date: '||
       To_char(Sysdate,'Month DD')||', '||
       To_char(Sysdate,'YYYY')||' at '||
       To_char(Sysdate,'HH24:MM:SS')
From Global_Name;

Set Heading On

Prompt Redo log Files and Switching Information

column group#        format 999 heading "Group|Number"
column thread#       format 999  heading "Thread|Number"
column sequence#     format 999,999 heading "Sequence|Number"
column bytes         format 999,999,999 heading "Size|KBytes"
column members       format 999 heading "Members"
column archived      format a3 heading "Archived?"
column status        format a8 heading "Status"
column first_change# format 999,999 heading "First|Change|Number" 
column first_time               heading "First Time Switch"
column member        format a35 heading "Group Members"

set feedback off

select f.group#, f.status, f.member, l.bytes/1024 bytes
from   v$logfile f, v$log l
where  f.group# = l.group#
order  by f.group#;

select group#, thread#, sequence#, members,
       archived, status, first_change#, first_time
from   v$log
order  by group#;

set heading off

select 'Switches occur at a rate of 1 switch every '||
       round((7*24*60)/decode(count(*),count(*),-1))||' minutes over the past week'
from v$loghist
where first_time >= sysdate-7;

-- Archive log Distribution

set serveroutput on
begin
dbms_output.put_line('');
end;
/

Set Heading Off

Select 'Instance   : ' || Global_Name , 'Report Date: '||
       To_char(Sysdate,'Month DD')||', '||
       To_char(Sysdate,'YYYY')||' at '||
       To_char(Sysdate,'HH24:MM:SS')
From Global_Name;

Set Heading On

set pagesize 50000
set line 132
set veri off

set termout off
def time="time"                    -- Oracle7
col time new_value time noprint
select 'to_char(first_time,''DD/MM/YY HH24:MI:SS'')' time
from   dual
where  &&_O_RELEASE like '8%'      -- Oracle8
;
set termout on

Prompt Archive log Distribution Information

select substr(&&time, 1, 5) day,
       to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'99') "00",
       to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'99') "01",
       to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'99') "02",
       to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'99') "03",
       to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'99') "04",
       to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'99') "05",
       to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'99') "06",
       to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'99') "07",
       to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'99') "08",
       to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'99') "09",
       to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'99') "10",
       to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'99') "11",
       to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'99') "12",
       to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'99') "13",
       to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'99') "14",
       to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'99') "15",
       to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'99') "16",
       to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'99') "17",
       to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'99') "18",
       to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'99') "19",
       to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'99') "20",
       to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'99') "21",
       to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'99') "22",
       to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'99') "23"
from   sys.v_$log_history
group  by substr(&&time,1,5)
;

-- Information about all segments in the database

set serveroutput on
begin
dbms_output.put_line('');
end;
/

Set Heading Off

Select 'Instance   : ' || Global_Name , 'Report Date: '||
       To_char(Sysdate,'Month DD')||', '||
       To_char(Sysdate,'YYYY')||' at '||
       To_char(Sysdate,'HH24:MM:SS')
From Global_Name;

Set Heading On
set line 80

Prompt Segment Counts by User and by Type

column owner		format a10 heading "OWNER" wrap
column segment_type 	format a10 heading "TYPE" wrap
column tablespace_name	format a15 heading "TABLESPACE" wrap
column cntsegs		format 9990 heading "SEGS"
column avgexts		format 90.9  heading "AVGEXT"
column maxexts		format 9990  heading "MAXEXT"
column sumblks		format 999,990 heading "BLOKS"
column sumbyts   	format 9,999,990 heading "MBYTES"

select
s.owner
,s.segment_type
,s.tablespace_name
,count(*) cntsegs
,avg(s.extents) avgexts
,max(s.extents) maxexts
,sum(s.blocks) sumblks
,sum(s.bytes/1048576) sumbyts
from dba_segments s
where owner not in ('SYS', 'SYSTEM', 'OUTLN')
group by s.owner,s.segment_type,s.tablespace_name
;

Exit
/