Script: Free Space Report for all Datafiles and if AUTOEXTENT is ON

-- ################################################################

--

-- %Purpose: Free Space Report for all Datafiles and if AUTOEXTENT is ON

--

--################################################################

--

clear columns -

      breaks -

      computes

set pagesize 100

column file_name format a32

column tablespace_name format a15

column status format a3 trunc

column t format 999,999.000 heading "Total MB"

column a format a4 heading "Aext"

column p format 990.00 heading "% Free"

SELECT  df.file_name,

        df.tablespace_name,

        df. status,

        (df.bytes/1024000) t,

        (fs.s/df.bytes*100) p,

        decode (ae.y,1,'YES','NO') a

  FROM  dba_data_files df,

        (SELECT file_id,SUM(bytes) s

           FROM dba_free_space

           GROUP BY file_id) fs,

        (SELECT file#, 1 y

           FROM sys.filext$

           GROUP BY file#) ae

  WHERE df.file_id = fs.file_id

    AND ae.file#(+) = df.file_id

ORDER BY df.tablespace_name, df.file_id;

column file_name clear

column tablespace_name clear

column status clear

column t clear

column a clear

column p clear

ttitle off

Script: DB Access Monitoring Report

-- ################################################################

--

-- %Purpose: DB Access Monitoring Report 

--           (Full Table and Index Scans, Chained Rows)

--

-- Use:      Needs Oracle DB Access

--

-- ################################################################

--

set feed off;

set pagesize 10000;

set wrap off;

set linesize 200;

set heading on;

set tab on;

set scan on;

set verify off;

set termout on;

column  STATISTIC#  form 999        head 'Id'

column  NA          form a32        head 'Statistic'

column  RIA         form 990.90     head 'Row Access via|Index [%]'

column  RTS         form 990.90     head 'Row Access via|Table Scan [%]'

column  RA          form 9999999990 head 'Rows accessed'

column  PCR         form 990.90     head 'Chained|Rows [%]'

colum   CL          form 990.90     head 'Cluster|Length'

ttitle  left  'DB Access Monitoring Report'  skip 2

spool   db_access.log

select  rpad (NAME, 32, '.') as NA,

        VALUE

  from  V$SYSSTAT

 where  NAME like '%table scan%'

    or  NAME like '%table fetch%'

    or  NAME like '%cluster%';

ttitle  off

select  A.VALUE + B.VALUE                     as RA,

        A.VALUE / (A.VALUE + B.VALUE) * 100.0 as RIA,

        B.VALUE / (A.VALUE + B.VALUE) * 100.0 as RTS,

        C.VALUE / (A.VALUE + B.VALUE) * 100.0 as PCR,

        E.VALUE / D.VALUE                     as CL

  from  V$SYSSTAT A,

        V$SYSSTAT B,

        V$SYSSTAT C,

        V$SYSSTAT D,

        V$SYSSTAT E

 where  A.NAME = 'table fetch by rowid'

   and  B.NAME = 'table scan rows gotten'

   and  C.NAME = 'table fetch continued row'

   and  D.NAME = 'cluster key scans'

   and  E.NAME = 'cluster key scan block gets'

/

Script: ANALYZE TABLE with Estimate or Compute Statistic, depending on table size

###############################################################

--

-- %Purpose: This script will help us in ANALYZE table

--                 with estimate or compute statistic, 

--                 depending on table size, see SIGN(n)

--

-- Use:    Any table less than 10 MB in total size has statistics computed

--           while tables larger than 10 MB have statistics estimated.

--           SIGN(n) ==> if n <>

--                       if n = 0 the functions returns 0

--                       if n > 0 the functions returns 1

--

-- #############################################################

--

set feed off;

set pagesize 10000;

set wrap off;

set linesize 200;

set heading on;

set tab on;

set scan on;

set verify off;

spool compute_or_estimate_stat.sql

--

SELECT 'ANALYZE TABLE '||owner||'.'||table_name||' '||

       DECODE(SIGN(10485760 - initial_extent),1,'COMPUTE STATISTICS;',

                                    'ESTIMATE STATISTICS;')

FROM   sys.dba_tables

WHERE  owner NOT IN ('SYS','SYSTEM');

/

--

spool off;

set feed on;

@compute_or_estimate_stat.sql