DBA-Scripts

801388

AdamN-Oracle

Hi,
Thank you for sharing these great scripts!

While most can be determined based on the script names,
could you give a short description of what each does?

I'm sure it would go a long way in helping members


Regards,
Adam

User_2025-09-24-16-30-42-213

This is very useful thanks very much for sharing this.

However I do have some scripts which I will share. Hope that will also help all of us:

Adding to this post one more script to find the File IO

----------------------------------------------------------------------------------------------------------------------

rem NAME: fileio.sql
rem
rem FUNCTION: Reports on the file io status of all of the
rem           datafiles in the database.

column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
column Percent format 999.999 heading 'Percent|Of IO'
column brratio format 999.99 heading 'Block|Read|Ratio'
column bwratio format 999.99 heading 'Block|Write|Ratio'
column phyrds heading 'Physical | Reads'
column phywrts heading 'Physical | Writes'
column phyblkrd heading 'Physical|Block|Reads'
column phyblkwrt heading 'Physical|Block|Writes'
column name format a30 heading 'File|Name'
column file# format 9999 heading 'File'
column dt new_value today noprint
TTITLE 'FILE I/O Status'
select to_char(sysdate,'ddmonyyyyhh24miss') dt from dual;
set feedback off verify off lines 132 pages 60 sqlbl on trims on
rem
select
    nvl(sum(a.phyrds+a.phywrts),0) sum_io1
from
    sys.v_$filestat a;
select nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from
        sys.v_$tempstat b;
select &st1+&st2 sum_io from dual;
rem
title 'File IO Statistics Report'
spool fileio&&today
select
    a.file#,b.name, a.phyrds, a.phywrts,
    (100*(a.phyrds+a.phywrts)/&divide_by) Percent,
    a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio,
      (a.phyblkwrt/greatest(a.phywrts,1)) bwratio
from
    sys.v_$filestat a, sys.v_$dbfile b
where
    a.file#=b.file#
union
select
    c.file#,d.name, c.phyrds, c.phywrts,
    (100*(c.phyrds+c.phywrts)/&divide_by) Percent,
    c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio,
      (c.phyblkwrt/greatest(c.phywrts,1)) bwratio
from
    sys.v_$tempstat c, sys.v_$tempfile d
where
    c.file#=d.file#
order by
    1
/
spool off
set feedback on verify on lines 80 pages 22
clear columns
ttitle off
----------------------------------------------------------------------------------------------------------------------