Oracle Performance Script

From: Dominik Geiger <dominik.geiger_at_www-germany.de>
Date: Mon, 22 Feb 1999 13:31:21 +0100
Message-ID: <36D14E12.FAD4EE16_at_www-germany.de>

Hallo to all,

this is a Script to Perform the Database

snapshot.sql (PSL Shareware Release R10)
Notes
------------------------------------------------------------
Temparary Objects Created By This Script.
Object Name Type Description
---------------------- -------
opt_tot_read_writes table Summary of total physical reads & writes from v$filestat
opt_fts view Summary of total table scan stats by user process
opt_bh_temp table Used for determining access freq. of buffer cache objects
opt_user_hr table
opt_user_cursors view
opt_nit view
opt_objext_warn view
opt_min_bw_checkpoints view
opt_dfs view DBA_DATA_FILES View
opt_tss view DBA_FREE_SPACE VIEW
Site Specific Value Defaults (change to match current site)
-----------------------------------------------------------
** NOT USED IN PSL PUBLIC (pw) ** define dblksize=2048 -- db block size define ips=50 -- i/o's per second on disk drives define ips2=500 --
Runtime Notes
---------------------------------------------------------------------
1. Reports which may take some time to run:
1. Inconsistent Column Names
2. The v$bh view which is used by this script may not have been created.
You must run the script $ORACLE_HOME/rdbms/admin/catparr.sql
Begin Scripts
----------------------------------

set echo off
set feedback off
set verify off
set heading off;
ttitle off
btitle off

set termout off
set define off

set pagesize 999
set linesize 500
set trimspool on
set pause off

set termout on;
select 'Start Time:'||to_char(sysdate,'MM/DD/YY HH:MI:SS') from dual; set termout off;

Misc Views
---------------------------------------------- create or replace view opt_dfs as select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name; create or replace view opt_tss as select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name;
Database Info (V$database)
------------------------------------------------------------------ set termout on prompt Retreiving Database Info... set termout off spool e:\info\database.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Database Info (v$database)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=database_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=database_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Database Name</b></td> prompt <td bgcolor=#6699cc><b>Creation Data</b></td> prompt <td bgcolor=#6699cc><b>Log Mode</b></td> prompt <td bgcolor=#6699cc align=center><b>CheckPoint<br>Change #</b></td> prompt <td bgcolor=#6699cc align=center><b>Archive<br>Change #</b></tr> prompt </tr> select '<tr><td>'||name||'</td><td>'||created||'</td><td>'||log_mode||'</td><td align=right>'|| checkpoint_change#||'</td><td align=right>'||archive_change#||'</td></tr>' from v$database; prompt </table><br> prompt </center> prompt </body></html> spool off;
Version Info (v$version)
------------------------------------------------------------------ set termout on prompt Retreiving Version Info... set termout off spool e:\info\version.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Database Products and Versions (v$version)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=version_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=version_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <pre> select '<li>'||banner from v$version; prompt </pre><br> prompt </body></html> spool off;
INIT.ORA Parameters
----------------------------------------------------------------- set termout on prompt Retreiving Init.ora Parameters... set termout off spool e:\info\initora.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>INIT.ORA Parameters</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=initora_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=initora_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>PARAMETER</b></td><td width=500 bgcolor=#6699cc> prompt <b>Value</b></td></tr> select '<tr><td>'|| decode(name, 'db_block_size','<font color=red><b>'||name||'</b></font>',

             'cursor_space_for_time','<font
color=red><b>'||name||'</b></font>',
             'db_block_buffers','<font
color=red><b>'||name||'</b></font>',
             'control_files','<font color=red><b>'||name||'</b></font>',

             'db_file_multiblock_read_count','<font
color=red><b>'||name||'</b></font>',
             'log_buffer','<font color=red><b>'||name||'</b></font>',
             'log_simultaneous_copies','<font
color=red><b>'||name||'</b></font>',
             'optimizer_mode','<font
color=red><b>'||name||'</b></font>',
             'sort_write_buffers','<font
color=red><b>'||name||'</b></font>',
             'shared_pool_reserved_min_alloc','<font
color=red><b>'||name||'</b></font>',
             'shared_pool_reserved_size','<font
color=red><b>'||name||'</b></font>',
             'db_block_multiblock_read_count','<font
color=red><b>'||name||'</b></font>',
             'parallel_max_servers','<font
color=red><b>'||name||'</b></font>',
             'parallel_min_percent','<font
color=red><b>'||name||'</b></font>',
             'shared_pool_size','<font
color=red><b>'||name||'</b></font>',
             'sort_area_size','<font
color=red><b>'||name||'</b></font>',
             'sort_area_retained_size','<font
color=red><b>'||name||'</b></font>',
             'sort_read_fac','<font color=red><b>'||name||'</b></font>',

             'sort_direct_writes','<font
color=red><b>'||name||'</b></font>',
             'sort_write_buffer_size','<font
color=red><b>'||name||'</b></font>',
             'hash_join_enabled','<font
color=red><b>'||name||'</b></font>',
             'hash_area_size','<font
color=red><b>'||name||'</b></font>',
             'hash_multiblock_io_count','<font
color=red><b>'||name||'</b></font>',
             'always_anti_join','<font
color=red><b>'||name||'</b></font>',
             'row_cache_cursors','<font
color=red><b>'||name||'</b></font>',
             'rollback_segments','<font
color=red><b>'||name||'</b></font>',
             'log_buffers','<font color=red><b>'||name||'</b></font>',
             'log_archive_start','<font
color=red><b>'||name||'</b></font>',
             'log_archive_buffer_size','<font
color=red><b>'||name||'</b></font>',
             'db_file_simultaneous_writes','<font
color=red><b>'||name||'</b></font>',
             'db_writers','<font color=red><b>'||name||'</b></font>',
             'enqueue_resources','<font
color=red><b>'||name||'</b></font>',
             'processes','<font color=red><b>'||name||'</b></font>',
             'small_table_threshold','<font
color=red><b>'||name||'</b></font>',
             'timed_statistics','<font
color=red><b><blink>'||name||'</blink></b></font>',
             'user_dump_dest','<font
color=red><b>'||name||'</b></font>',
             'sql_trace','<font
color=red><b><blink>'||name||'</blink></b></font>',
              name)
       ||'</td><td>'||value||'</td></tr>' from v$parameter

order by name;

prompt </table><br>
prompt <i>Highlight's indicate commonly reviewed parameters</i><br><br>
prompt </body></html>

spool off;

Undocumented INIT2.ORA Parameters
----------------------------------------------------------------- set termout on prompt Retreiving Undocumented INIT.ora Parameters... set termout off spool e:\info\init2ora.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Undocumented INIT.ORA Parameters</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=init2ora_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=init2ora_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>PARAMETER</b></td> prompt <td bgcolor=#6699cc><b>Value</b></td> prompt <td bgcolor=#6699cc><b>Desc</b></td></tr> select '<tr><td>'||ksppinm||'</td><td>'||ksppstvl||'</td><td>'||ksppdesc||'</td></tr>'

from x$ksppi x, x$ksppcv y
where x.indx = y.indx and

translate(ksppinm,'_','#') like '#%' order by ksppinm;
prompt </table><br>
prompt <font color=red size=3><b><blink>Do NOT change any of these values<b>unless
prompt instructed by ORACLE support.</blink></b></font> prompt </body></html>
spool off;

SGA Statistics
----------------------------------------------------------------- set termout on prompt Retreiving SGA Statistics... set termout off spool e:\info\sgastat.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>SGA Statistics</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sgastat_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sgastat_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Statistic Name</b></td><td bgcolor=#6699cc> prompt <b>Bytes</b></td></tr> select '<tr><td>'||name||'</td><td align=right>'||bytes||'</td></tr>' from v$sgastat order by bytes desc; prompt </table></center> prompt </body></html> spool off;
HIT Statistics
---------------------------------------------------------------- set termout on prompt Retreiving Hit Statistics... set termout off spool e:\info\hitstat.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Hit Statistics</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=hitstat_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=hitstat_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Consistent Gets</b></td> prompt <td bgcolor=#6699cc><b>DB Blk Gets</b></td> prompt <td bgcolor=#6699cc><b>Physical Reads</b></td> prompt <td bgcolor=#6699cc><b>Hit Ratio</b></td></tr> select '<tr><td>'||sum(decode(name, 'consistent gets',value, 0))||'</td>', '<td>'||sum(decode(name, 'db block gets',value, 0))||'</td>', '<td>'||sum(decode(name, 'physical reads',value, 0))||'</td>', '<td>'||round((sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) - sum(decode(name, 'physical reads',value, 0))) / (sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) ) * 100,2)||'</td></tr>' from v$sysstat st; prompt </table></center> prompt </body></html> spool off;
Dictionary Cache Stats
----------------------------------------------------------------- set termout on prompt Retreiving Data Dictionary Cache Performance... set termout off spool e:\info\dictcache.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Data Dictionary Cache Statistics</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=dictcache_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=dictcache_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellpadding=0 cellspacing=0><tr> prompt <td bgcolor=#6699cc><b>Parameter</b></td> prompt <td bgcolor=#6699cc><b>Gets</b></td> prompt <td bgcolor=#6699cc><b>GetMisses</b></td> prompt <td bgcolor=#6699cc><b>% Cache Misses</b></td> prompt </tr> select '<tr><td>'||parameter||'</td><td align=right>'||gets||'</td><td align=right>'||getmisses|| '</td><td align=right>'||to_char(round(getmisses/gets,2)*100,'990.90')||'</td></tr>'

from v$rowcache
where gets > 0
order by (getmisses/gets)*100;

prompt </table>
prompt </center>
prompt <pre>

set heading on
col "Overall Cache Misses %" format a30; SELECT to_char(SUM(gets),'999,999,999') "Total Gets", to_char(SUM(getmisses),'999,999,999') "Total Misses", to_char(round((sum(getmisses)/sum(gets))*100,2),'990.90') "Overall Cache Misses %"
FROM v$rowcache;
set heading off
prompt </body></html>
spool off;

Disk I/O By Data File
----------------------------------------------------------------- set termout on prompt Retreiving Disk I/O By Data File... set termout off

drop table opt_tot_read_writes;

create table opt_tot_read_writes
as select sum(phyrds) phys_reads, sum(phywrts) phys_wrts

from v$filestat;
spool e:\info\datafileio.htm
prompt <html><body bgcolor=white><center> prompt <table width=90% cellpadding=0 cellspacing=0>

prompt  <tr><td align=left valign=bottom><font size=5>Disk I/O By
Datafile</font></td></tr>
prompt  <tr><td colspan=2 valign=bottom><img src=blackpixel.gif

width=100% height=4></td></tr>

prompt </table>
prompt <table width=90% cellpadding=0 cellspacing=0><tr>
prompt <td align=left valign=top>
prompt <a href=datafileio_notes.htm><img src=notes.gif  border=0

alt="Notes"></a>
prompt <a href=datafileio_script.htm><img src=tools.gif border=0 alt="SQL Source"></a>
prompt </td>
prompt <td align=center valign=top>
select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual;
select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database;

prompt </tr>
prompt </table>
prompt <table><tr><td bgcolor=#6699cc><b>File Name</b></td>
prompt <td bgcolor=#6699cc align=center><b>Physical<br>Reads</b></td>
prompt <td bgcolor=#6699cc align=center><b>Read<br>%</b></td>
prompt <td bgcolor=#6699cc align=center><b>Physical<br>Writes</b></td>
prompt <td bgcolor=#6699cc align=center><b>Write<br>%</b></td>
prompt <td bgcolor=#6699cc align=center><b>Total Block<br>I/O's</b></td>

prompt </tr>
column name format a30;
column phyrds format 999,999,999;
column phywrts format 999,999,999;
column read_pct format 999.99;
column write_pct format 999.99;
select '<tr><td>'||name||'</td>', '<td align=right>'||phyrds||'</td>', '<td align=right>'||round((phyrds/ trw.phys_reads)*100,2)||'</td>' read_pct,

'<td align=right>'||phywrts||'</td>', '<td align=right>'||round(phywrts * 100 / trw.phys_wrts,2)||'</td>' write_pct,

'<td
align=right>'||to_number(fs.phyblkrd+fs.phyblkwrt)||'</td></tr>' from opt_tot_read_writes trw, v$datafile df, v$filestat fs where df.file# = fs.file#
order by fs.phyblkrd+fs.phyblkwrt desc;
prompt </table></center>
prompt </body></html>
spool off;

V$SYSSTAT Table Scan Statistics
----------------------------------------------------------------- set termout on prompt Retreiving Table Scan Statistics... set termout off spool e:\info\sysstat1.htm prompt <html><body bgcolor=white><center> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>System Statistics (Table Related)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sysstat1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sysstat1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <table><tr><td bgcolor=#6699cc><b>Statistic Name</b></td><td bgcolor=#6699cc> prompt <b>Bytes</b></td></tr> select '<tr><td>'||name||'</td><td align=right>'||value||'</td></tr>' from v$sysstat where name like '%table %'; prompt </table></center> prompt </body></html> spool off;
V$SYSSTAT (Parallel Server Related)
-----------------------------------------------------------------
see OPS 21.14 for stat definitions set termout on prompt Retreiving Parallel Server Specific Statistics... set termout off spool e:\info\sysstatp.htm prompt <html><body bgcolor=white><center> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>System Statistics (Parallel Server)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sysstatp_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sysstatp_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <table><tr><td bgcolor=#6699cc><b>Statistic Name</b></td><td bgcolor=#6699cc> prompt <b>Bytes</b></td></tr> select '<tr><td>'||name||'</td><td align=right>'||value||'</td></tr>' from v$sysstat where class = 32 or class = 40; prompt </table></center> prompt </body></html> spool off;
User Table Scan Info
----------------------------------------------------------------- create or replace view opt_fts as select ss.username||'('||se.sid||') ' "User Process", sum(decode(name,'table scans (short tables)',value)) "Short Scans", sum(decode(name,'table scans (long tables)', value)) "Long Scans", sum(decode(name,'table scan rows gotten',value)) "Rows Retreived" from v$session ss, v$sesstat se, v$statname sn where se.statistic# = sn.statistic# and ( name like '%table scans (short tables)%' OR name like '%table scans (long tables)%' OR name like '%table scan rows gotten%' ) and se.sid = ss.sid and ss.username is not null group by ss.username||'('||se.sid||') ' /

column  "User Process"     format a20;
column  "Long Scans"       format 999,999,999;
column  "Short Scans"      format 999,999,999;
column  "Rows Retreived"   format 999,999,999;
column  "Average Long Scan Length" format 999,999,999;

spool e:\info\scans1.htm
prompt <html><body bgcolor=white><center> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Table Scan Statistics By User Process</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr>

prompt </table>
prompt <table width=90% cellpadding=0 cellspacing=0><tr>
prompt <td align=left valign=top>
prompt <a href=scans1_notes.htm><img src=notes.gif  border=0

alt="Notes"></a>
prompt <a href=scans1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a>
prompt </td>
prompt <td align=center valign=top>
select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual;
select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database;

prompt </tr>
prompt </table>
prompt <table><tr><td bgcolor=#6699cc><b>User Process</b></td>
prompt <td bgcolor=#6699cc><b>Long Scans</b></td>
prompt <td bgcolor=#6699cc><b>Short Scans</b></td>
prompt <td bgcolor=#6699cc><b>Rows Retreived</b></td>
prompt </tr>

select '<tr><td>'||"User Process"||'</td><td align=right>'||"Long Scans"||'</td><td align=right>'||"Short Scans"||'</td><td align=right>'||"Rows Retreived"||'</td></tr>' from opt_fts
order by "Long Scans" desc;

prompt </table></center>
prompt </body></html>
spool off;

Average Scan Length of Full Table Scans by User
------------------------------------------------------------------- set termout on prompt Retreiving Full Table Scan Stat's By User set termout off spool e:\info\scans2.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Avg. Full Table Scan By User</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=scans2_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=scans2_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt<center> prompt <table><tr><td bgcolor=#6699cc><b>User Process</b></td> prompt <td bgcolor=#6699cc><b>Avg. Long Scan Length</b></td> prompt </tr>

select '<tr><td>'||"User Process"||'</td><td align=right>'|| round(("Rows Retreived" - ("Short Scans" * 5)) / ( "Long Scans" ),2) ||'</td></tr>'
from opt_fts
where "Long Scans" != 0
order by "Long Scans" desc;

prompt </table></center>
prompt </body></html>
spool off;

Disk Sorts
----------------------------------------------------------------- set termout on prompt Retreiving Disk Sort Statistics... set termout off spool e:\info\sorts1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Disk Sort Statistics</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sorts1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sorts1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Sort Parameter</b></td><td bgcolor=#6699cc> prompt <b>Value</b></td></tr> select '<tr><td>'||name||'</td><td align=right>'||value||'</td></tr>' from v$sysstat where name like 'sort%'; prompt </table></center> prompt </body></html> spool off;
Rollback Segments
------------------------------------------------------------------ set termout on prompt Retreiving Rollback Segment Info... set termout off spool e:\info\rollback.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Rollback Segment Info</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=rollback_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=rollback_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Segment Name</b></td> prompt <td bgcolor=#6699cc><b>Owner</b></td> prompt <td bgcolor=#6699cc><b>Tablespace</b></td> prompt <td bgcolor=#6699cc><b>Segment ID</b></td> prompt <td bgcolor=#6699cc><b>File ID</b></td> prompt <td bgcolor=#6699cc><b>Block ID</b></td> prompt <td bgcolor=#6699cc><b>Initial Extent</b></td> prompt <td bgcolor=#6699cc><b>Next Extent</b></td> prompt <td bgcolor=#6699cc><b>Min Extents</b></td> prompt <td bgcolor=#6699cc><b>Max Extents</b></td> prompt <td bgcolor=#6699cc><b>PCT Increase</b></td> prompt <td bgcolor=#6699cc><b>Status</b></td> prompt <td bgcolor=#6699cc><b>Instance #</b></td> prompt </tr> select '<tr><td>'||segment_name||'</td><td>'||owner||'</td><td>'||tablespace_name||

'</td><td align=right>'||segment_id||'</td><td align=right>'||file_id||'</td><td align=right>'||block_id||

'</td><td align=right>'||initial_extent||'</td><td align=right>'||next_extent||'</td><td align=right>'||min_extents||

       '</td><td align=right>'||max_extents||'</td><td
align=right>'||pct_increase||'</td><td align=right>'||
       decode(status,'OFFLINE','<font
color=red>OFFLINE</font>',status)||
       '</td><td align=right>'||instance_num||'</td></tr>'

from dba_rollback_segs
order by segment_name;
prompt </table></center>
prompt </body></html>
spool off;

Amount of Rollbacks on Transaction Tables
----------------------------------------------------------------- set termout on prompt Retreiving Rollback on Transaction Table Info... set termout off spool e:\info\rbs1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Rollback Statistics on Transaction Tables</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=rbs1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=rbs1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellpadding=0 cellspacing=0><tr><td bgcolor=#6699cc><b>Statistic</b></td> prompt <td bgcolor=#6699cc><b>Value</b></td></tr> select '<tr><td>'||name||'</td><td align=right>'||value||'</td></tr>' from v$sysstat where name in ('consistent gets', 'consistent changes', 'transaction tables consistent reads - undo records applied', 'transaction tables consistent read rollbacks');

prompt <tr><td colspan=2 bgcolor=#6699cc>Note 1 </td></tr> prompt <tr><td colspan=2 width=50%>

prompt </td></tr>
select '<tr><td>'||name||'</td><td align=right>'||value||'</td></tr>' from v$sysstat
where name in
('data blocks consistent reads - undo records applied',

     'no work - consistent read gets',
     'cleanouts only - consistent read gets',
     'rollbacks only - consistent read gets',
     'cleanouts and rollbacks - consistent read gets');



prompt </table></center>

prompt <b>Other Info:</b><br>
prompt <pre>
select 'Tran Table Consistent Read Rollbacks > 1% of Consistent Gets' aa,

'Action: Create more Rollback Segments' from v$sysstat
where decode (name,'transaction tables consistent read rollbacks',value)

100 / decode (name,'consistent gets',value) > 0.1 and name in ('transaction tables consistent read rollbacks', 'consistent gets') and value > 0;

select 'Undo Records Applied > 10% of Consistent Changes' aa,

'Action: Create more Rollback Segments' from v$sysstat
where decode

(name,'transaction tables consistent reads - undo records applied',value)

100 / decode (name,'consistent changes',value) > 10 and name in ('transaction tables consistent reads - undo records applied', 'consistent changes') and value > 0; prompt </pre><br>

prompt </body></html>
spool off;

Rollback Contention
------------------------------------------------------------------ set termout on prompt Retreiving Rollback Contention Statistics... set termout off spool e:\info\rbs2.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Rollback Contention Statistics</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=rbs2_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=rbs2_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Rollback Table</b></td> prompt <td bgcolor=#6699cc><b>Gets</b></td> prompt <td bgcolor=#6699cc><b>Waits</b></td> prompt <td bgcolor=#6699cc><b>Active Transactions</b></td> prompt <td bgcolor=#6699cc><b>Hit Ratio</b></td> prompt </tr>

select '<tr><td>'||b.name||' ('||a.usn||')</td><td
align=right>'||gets||'</td><td align=right>'||waits||'</td><td
align=right>'||xacts||
       '</td><td>'||round( ((gets-waits)*100)/gets ,2)||'</td></tr>'

from v$rollstat a,v$rollname b
where a.usn = b.usn;

prompt </table></center>
prompt </body></html>
spool off;

Rollback Contention 2
------------------------------------------------------------------ set termout on prompt Retreiving Rollback Contention Statistics II... set termout off spool e:\info\rbs2b.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Rollback Contention Statistics 2</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=rbs2b_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=rbs2b_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Wait Statistic</b></td> prompt <td bgcolor=#6699cc><b>Count</b></td> prompt </tr> SELECT '<tr><td>'||CLASS||'</td><td>'||COUNT||'</td></tr>' from v$waitstat where class in ('system undo header','system undo block','undo header','undo block'); prompt </table></center>
if total # of waits of any class of block is greater than 1% of sum requests
increase rollback segments.
SELECT SUM(VALUE) FROM V$SYSSTAT
2 WHERE name IN
3 (’db block gets’, ’consistent gets’); prompt </body></html> spool off;
Rollback Extending and Shrinkage
------------------------------------------------------------------ set termout on prompt Retreiving Rollback Extends and Shrinkage Info... set termout off spool e:\info\rbs3.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Rollback Extending and Shrinkage</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=rbs3_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=rbs3_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Rollback Table</b></td> prompt <td bgcolor=#6699cc><b>USN</b></td> prompt <td bgcolor=#6699cc><b>OptSize</b></td> prompt <td bgcolor=#6699cc><b>Extends</b></td> prompt <td bgcolor=#6699cc><b>Wraps</b></td> prompt <td bgcolor=#6699cc><b>Shrinks</b></td> prompt <td bgcolor=#6699cc><b>Hwmsize</b></td> prompt <td bgcolor=#6699cc><b>Avg. Shrink</b></td> prompt </tr>

select '<tr><td>'||name||'</td><td>'||a.usn||'</td><td align=right>'||

        optsize||'</td><td>'||extends||'</td><td>'||wraps
       ||'</td><td align=right>'||shrinks
       ||'</td><td

align=right>'||hwmsize||'</td><td>'||aveshrink||'</td></tr>' from V$ROLLSTAT a , V$ROLLNAME b
where a.USN=b.USN;

prompt </table></center>
prompt </body></html>
spool off;

System Statistics (ALL)
------------------------------------------------------------------ set termout on prompt Retreiving System Statistics (ALL)... set termout off spool e:\info\sysstat2.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>System Statistics (All)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sysstat2_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sysstat2_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellspacing=0 cellpadding=0><tr><td bgcolor=#6699cc><b>Stat #</b></td> prompt <td bgcolor=#6699cc width=300><b>Name</b></td> prompt <td bgcolor=#6699cc><b>Class</b></td> prompt <td bgcolor=#6699cc><b>Value</b></td> prompt </tr>

select '<tr><td>'||statistic#||'</td><td>'|| decode(name,

'recursive calls','<font color=red><b>recursive calls</b></font>',

       'enqueue timeouts','<font color=red><b>'||name||'</b></font>',
       'enqueue waits','<font color=red><b>'||name||'</b></font>',
       'enqueue requests','<font color=red><b>'||name||'</b></font>',
       'enqueue deadlocks','<font color=red><b>'||name||'</b></font>',
       'db block gets','<font color=red><b>'||name||'</b></font>',
       'consistent gets','<font color=red><b>'||name||'</b></font>',
       'physical reads','<font color=red><b>'||name||'</b></font>',
       'redo log space requests','<font
color=red><b>'||name||'</b></font>',
       'sorts (memory)','<font color=red><b>'||name||'</b></font>',
       'sorts (disk)','<font color=red><b>'||name||'</b></font>',
       'sorts (rows)','<font color=red><b>'||name||'</b></font>',
       'consistent changes','<font color=red><b>'||name||'</b></font>',
       'db block changes','<font color=red><b>'||name||'</b></font>',
       '','<font color=red><b>'||name||'</b></font>',
        name)

||'</td><td align=right>'||class||'</td><td align=right>'||value||'</td></tr>'
from v$sysstat;

prompt </table></center>
prompt </body></html>
spool off;

System Events (ALL)
------------------------------------------------------------------ set termout on prompt Retreiving System Events (v$system_event)... set termout off spool e:\info\sysevents.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>System Events</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sysevents_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sysevents_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table width=90%><tr><td bgcolor=#6699cc><b>Event Name</b></td> prompt <td bgcolor=#6699cc><b>Total Waits</b></td> prompt <td bgcolor=#6699cc><b>Total Timeouts</b></td> prompt <td bgcolor=#6699cc><b>Time Waited</b></td> prompt <td bgcolor=#6699cc><b>Average Wait</b></td> prompt </tr>

select '<tr><td>'||decode(event,

       'buffer busy waits','<font color=red><b>'||event||'</b></font>',
        event)
        ||'</td><td align=right>'||
        total_waits||'</td><td align=right>'||
        total_timeouts||'</td><td align=right>'||
        time_waited||'</td><td

align=right>'||round(average_wait,2)||'</td></tr>' from v$system_event
order by total_waits;

prompt </table></center>

select '<hr>Note:<b>Time Waited</b>,<b>Average Wait</b> are not available since <i>timed_statistics</i> has not been set to TRUE.' from dual
where 'FALSE' in (select value from v$parameter where name='timed_statistics');
prompt </body></html>
spool off;

Wait Statistics
------------------------------------------------------------------ set termout on prompt Retreiving Wait Statistics... set termout off spool e:\info\waitstat1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Wait Statistics</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=waitstat1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=waitstat1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Class</b></td> prompt <td bgcolor=#6699cc><b>Count</b></td> prompt <td bgcolor=#6699cc><b>Time</b></td> prompt </tr>

select '<tr><td>'||class||'</td><td align=right>'||count||'</td><td align=right>'||time||'</td></tr>'
from v$waitstat;

prompt </table></center>
prompt </body></html>
spool off;

Sessions Experiencing Block Level Contention
------------------------------------------------------------------ set termout on prompt Retreiving Info on Sessions Experiencing Block Level Contention... set termout off spool e:\info\sesblk1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Sessions Experiencing Block Level Contention</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sesblk1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sesblk1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>User</b></td> prompt <td bgcolor=#6699cc><b>File</b></td> prompt <td bgcolor=#6699cc><b>Block</b></td> prompt </tr>

select '<tr><td>'||ss.username||'</td><td align=right>'||sw.p1||'</td><td align=right>'||sw.p2||'</td></tr>' from v$session_wait sw, v$session ss
where event = 'buffer busy waits'
and ss.sid = sw.sid;

prompt </table></center>
prompt </body></html>
spool off;

Objects Experiencing Contention
------------------------------------------------------------------ set termout on prompt Retreiving Info on Objects Experiencing Contention... set termout off spool e:\info\objcont1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Objects Experiencing Contention</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=objcont1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=objcont1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Owner</b></td> prompt <td bgcolor=#6699cc><b>Segment Name</b></td> prompt </tr>

select '<tr><td>'||owner||'</td><td
align=right>'||segment_name||'</td></tr>' from dba_extents
where (file_id, block_id)
in
(select file_id, block_id

   from dba_extents, v$session_wait vw
  where file_id = vw.p1
  and (p1 , p2 )

       in
        (select p1   , p2
           from v$session_wait
          where event = 'buffer busy waits')
  and   block_id= (select max(block_id)
                    from dba_extents
                   where block_id < vw.p2      ));

prompt </table></center>
prompt </body></html>
spool off;

Transactions Experiencing Lock Contention
------------------------------------------------------------------ set termout on prompt Retreiving Info on Transactions Experiencing Lock Contention... set termout off spool e:\info\transcont1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Transactions Experiencing Lock Contention</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=transcont1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=transcont1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <pre> set heading on; select * from v$lock where type='TX'; prompt </pre> prompt <br><font size=2><i>No data indicates no such contention</i></font><br> set heading off; prompt <br><br>

prompt </body></html>
spool off;

Current Buffer Cache Usage
------------------------------------------------------------------ set termout on prompt Retreiving Current Buffer Cache Usage... set termout off spool e:\info\bfusage.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Current Buffer Cache Usage</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=bfusage_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=bfusage_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc width=200><b>Status</b></td><td bgcolor=#6699cc width=100><b>Count</b></td></tr>

select '<tr><td>'||
decode(status,

          'CR','CR (consistent read)',
          'XCUR','XCUR (exclusive)',
          'FREE','FREE (not currently in use)',
          'SCUR','SCUR (shared current)',
          'READ','READ (being read from disk)',
          'MREC','MREC (in media recovery mode)',
          'IREC','IREC (in instance recovery mode)',
          status)

||'</td><td align=right>'||count(*)||'</td></tr>' from v$bh
group by status;

prompt </table>
prompt </center> <br><br><hr>
prompt <font size=1><p>Note:To create the view v$bh, which is used by

this script, you must run the script
$ORACLE_HOME/rdbms/admin/catparr.sql</p> prompt </font>
prompt <br></body></html>
spool off;

Detailed Buffer Cache Usage By Object
------------------------------------------------------------------ set termout on prompt Retreiving Detailed Buffer Cache Usage By Object... set termout off drop table opt_bh_temp;
*** UNCOMMENT THIS NEXT LINE TO USE THIS QUERY **
--------------------------------------------------
create table opt_bh_temp as select * from v$bh; spool e:\info\bcobject.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Buffer Cache Usage By Object</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=bcobject_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=bcobject_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Owner</b></td> prompt <td bgcolor=#6699cc width=300><b>Segment Type</b></td> prompt <td bgcolor=#6699cc><b>Segment Name</b></td> prompt <td bgcolor=#6699cc><b>Tablespace</b></td> prompt <td bgcolor=#6699cc><b>Buffer Count</b></td> prompt </tr>

prompt <tr><td colspan=5 align=center><b>Not Active In This Demo<br>Due To Large Output </b></td></tr>

select '<tr><td>'||e.owner||'</td><td>'|| e.segment_type||'</td><td>'||
e.segment_name||'</td><td>'||tablespace_name||'</td><td>'|| count(*) "Num Buffers"
from dba_extents e , opt_bh_temp b
where file# / 4 = e.file_id
and b.block# between e.block_id and e.block_id + e.blocks
group by e.owner, e.segment_type, e.segment_name, tablespace_name;

prompt
prompt </table></center>
prompt </body></html>
spool off;

Buffer Clean Out Frequency
----------------------------------------------------------------- set termout on prompt Retreiving Buffer Clean Out Frequency... set termout off spool e:\info\bufclean.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Buffer Clean Out Frequency</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=bufclean_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=bufclean_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellpadding=0 cellspacing=0><tr> prompt <td bgcolor=#6699cc><b>Name</b></td> prompt <td bgcolor=#6699cc><b>Value</b></td> prompt </tr> select '<tr><td>'||name||'</td><td align=right>'||to_char(value,'999,999,999,999')||'</td></tr>' from v$sysstat where name like 'DBW%'; prompt </table></center> prompt </body></html> spool off;
Average Length Write Request Queue
----------------------------------------------------------------- set termout on prompt Retreiving Average Length of Write Request Queue... set termout off spool e:\info\avgwrque.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Average Length Write Request Queue</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=avgwrque_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=avgwrque_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Write Request Queue Length</b></td></tr> select '<tr><td>'|| to_char(decode (name, 'summed dirty queue length', value) / decode (name, 'write requests', value),'999,999,999,999.90')|| '</td></tr>' from v$sysstat where name in ( 'summed dirty queue length','write requests') and value > 0; prompt </table> prompt </body></html> spool off;
Lazy DBWR Indicators - Buffers Inspected
----------------------------------------------------------------- set termout on prompt Retreiving Lazy DBWR Indicators... set termout off spool e:\info\dbwrlazy.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Lazy DBWR Indicators - Buffers Inspected</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=dbwrlazy_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=dbwrlazy_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellpadding=0 cellspacing=0><tr> prompt <td bgcolor=#6699cc><b>Dirty Buffers Inspected</b></td> prompt <td bgcolor=#6699cc><b>Free Buffers Inspected </b></td> prompt </tr> select '<tr><td align=right>'||sum(decode (name, 'dirty buffers inspected', value))||'</td><td align=right>'||sum(decode (name, 'free buffer inspected', value))||'</td></tr>' from v$sysstat where name in ( 'dirty buffers inspected','free buffer inspected') and value > 0; prompt </table></center> prompt </body></html> spool off;
User Hit Ratios
---------------------------------------------------------------- set termout on prompt Retreiving User Hit Ratios... set termout off spool e:\info\userhit1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>User Hit Ratio's</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=userhit1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=userhit1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>User Session</b></td> prompt <td bgcolor=#6699cc><b>Consistent Gets</b></td> prompt <td bgcolor=#6699cc><b>DB Block Gets</b></td> prompt <td bgcolor=#6699cc><b>Physical Reads</b></td> prompt <td bgcolor=#6699cc><b>Hit Ratio</b></td> prompt </tr> col "Hit Ratio" format 999.00 select '<tr><td>'||nvl(se.username,'ORACLE PROC')||'('|| se.sid||')</td>' "User Session", '<td align=right>'||sum(decode(name, 'consistent gets',value, 0))||'</td>' "Consis Gets", '<td align=right>'|| sum(decode(name, 'db block gets',value, 0))||'</td>' "DB Blk Gets", '<td align=right>'|| sum(decode(name, 'physical reads',value, 0))||'</td>' "Phys Reads", '<td align=right>'||round((sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) - sum(decode(name, 'physical reads',value, 0))) / (sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) ) * 100,2)||'</td></tr>' "Hit Ratio" from v$sesstat ss, v$statname sn, v$session se where ss.sid = se.sid and sn.statistic# = ss.statistic# and value != 0 and sn.name in ('db block gets', 'consistent gets', 'physical reads') group by se.username, se.sid;

prompt </table></center>
prompt </body></html>
spool off;

Objects Being Used by Users with Hit Ratio < 60
---------------------------------------------------------------- set termout on prompt Retreiving Objects With User Hit Ration < 60% set termout off drop table opt_user_hr; create table opt_user_hr as (select nvl(se.username,'ORACLE PROC')||'('|| se.sid||')' "User Session", sum(decode(name, 'consistent gets',value, 0)) "Consistent Gets",

        sum(decode(name, 'db block gets',value, 0))  "DB Block Gets",
        sum(decode(name, 'physical reads',value, 0))  "Physical Reads",
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  -
        sum(decode(name, 'physical reads',value, 0)))
                       /
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  )  * 100 "Hit

Ratio"
  from v$sesstat ss, v$statname sn, v$session se where ss.sid = se.sid
  and sn.statistic# = ss.statistic#
  and value != 0
  and sn.name in ('db block gets', 'consistent gets', 'physical reads')
group by se.username, se.sid);

break on "User Session"

spool e:\info\userhit2.htm
prompt <html><body bgcolor=white>
prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Objects Being Used by Users with Low Hit Ratio(&lt 60%)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr>

prompt </table>
prompt <table width=90% cellpadding=0 cellspacing=0><tr>
prompt <td align=left valign=top>
prompt <a href=userhit2_notes.htm><img src=notes.gif  border=0

alt="Notes"></a>
prompt <a href=userhit2_script.htm><img src=tools.gif border=0 alt="SQL Source"></a>
prompt </td>
prompt <td align=center valign=top>
select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual;
select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database;

prompt </tr>
prompt </table>
prompt <center>
prompt <table><tr><td bgcolor=#6699cc><b>User Session</b></td>
prompt <td bgcolor=#6699cc><b>Object Owner</b></td>
prompt <td bgcolor=#6699cc><b>Object</b></td>
prompt <td bgcolor=#6699cc><b>Hit Ratio</b></td>
prompt </tr>

col "Hit Ratio" format 999.00

select '<tr><td>'||nvl(se0.username,'ORACLE PROC')||'('|| se0.sid||')</td>' "User Session",

       '<td>'||substr(owner, 1,12)||'</td>' "Object Owner",
       '<td>'||substr(object,1,30)||'</td>' "Object"

  from v$access ac, v$session se0
where ac.sid = se0.sid
  and ac.type = 'TABLE'
  and 60 >
  (select "Hit Ratio" from opt_user_hr
   where nvl(se0.username,'ORACLE PROC')||'('|| se0.sid||')' = "User Session")
order by username, se0.sid, owner ;

prompt </table></center>
prompt </body></html>
spool off;

Cursors That Users Currently have Open Where User Hit Ratio < 60%
---------------------------------------------------------------- set termout on prompt Retreiving Open User Cursors with Hit Ratios < 60%... set termout off spool e:\info\userhit3.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Open Cursors With Low Hit Ratio (&lt 60%)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=userhit3_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=userhit3_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>User Session</b></td> prompt <td bgcolor=#6699cc><b>SQL TEXT</b></td> prompt </tr>

select nvl(se0.username,'ORACLE PROC')||'('||se0.sid||')</td>' "User Session",

'<td>'||sql_text||'</td></tr>'
from v$open_cursor oc0, v$session se0

  where se0.saddr       = oc0.saddr
        and    60 >
              (select "Hit Ratio" from opt_user_hr
                where nvl(se0.username,'ORACLE PROC')||'('||se0.sid||')'

= "User Session")
order by nvl(se0.username,'ORACLE'), se0.sid;

prompt </table></center>
prompt </body></html>
spool off;

Cursors That Users Currently have RUNNING Where User Hit Ratio < 60%
---------------------------------------------------------------- set termout on prompt Retreiving Running User Cursors with Hit Ratio < 60%... set termout off spool e:\info\userhit4.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Running Cursors With Low Hit Ratio (&lt 60%)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=userhit4_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=userhit4_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>User Session</b></td> prompt <td bgcolor=#6699cc><b>SQL TEXT</b></td> prompt </tr>

select nvl(se0.username,'ORACLE PROC')||'('||se0.sid||')</td>' "User Session",

       '<td>'||sql_text||'</td></tr>'
  from v$open_cursor oc0, v$session se0
  where se0.sql_address = oc0.address

         and se0.sql_hash_value = oc0.hash_value
        and    60 >
              (select "Hit Ratio" from opt_user_hr
                where nvl(se0.username,'ORACLE PROC')||'('||se0.sid||')'

= "User Session")
order by nvl(se0.username,'ORACLE'), se0.sid;

prompt </table></center>
prompt </body></html>
spool off;

Disk Intensive SQL
----------------------------------------------------------------
The SQL_TEXT output can actually contain html code which will mess
with the browsers display. Used replace() to change them to html meta.(ah) set termout on prompt Retreiving Disk Intensive SQL... set termout off spool e:\info\listsql1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Shared Pool SQL with Most Disk Reads</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=listsql1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=listsql1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center><br> prompt <i>Execution time</i> based on 50 I/O's per second<br> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td bgcolor=#6699cc><b>Executions</b></td> prompt <td bgcolor=#6699cc><b>Execution Time</b></td> prompt <td width=300 bgcolor=#6699cc><b>SQL Text</b></td> prompt </tr> select '<tr><td valign=top>'||a.username||'</td><td valign=top>'||executions||'</td><td valign=top>' ||round(disk_reads / decode(executions, 0, 1, executions) / 50,2) ||'</td><td>' ||replace(replace(sql_text,'<','<'),'>','>') ||'</td></tr>'

from dba_users a, v$session, v$sqlarea where parsing_user_id=user_id and address=sql_address(+) and

disk_reads / decode(executions,0,1, executions) / 50 > 50*2 order by executions desc;
-- 50*2 returns sql with execution times totally > 2 seconds
prompt </table></center>
prompt </body></html>
spool off;

Buffer Scan Intensive SQL
----------------------------------------------------------------
The SQL_TEXT output can actually contain html code which will mess
with the browsers display. Used replace() to change them to html meta.(ah) set termout on prompt Retreiving Buffer Scan Intensive SQL... set termout off spool e:\info\listsql2.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Shared Pool SQL with Most Buffer Scans</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=listsql2_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=listsql2_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Executions</b></td> prompt <td bgcolor=#6699cc><b>Execution Time</b></td> prompt <td width=300 bgcolor=#6699cc><b>SQL Text</b></td> prompt </tr> select '<tr><td valign=top>'||executions||'</td>', '<td valign=top>'||round(disk_reads / decode(executions, 0, 1, executions) / 400,2) ||'</td>' "Response" , '<td>'||replace(replace(sql_text,'<','<'),'>','>') ||'</td></tr>' -- from v$sqlarea where buffer_gets / decode(executions,0,1, executions) / 400 > 10 order by executions desc; prompt </table></center> prompt </body></html> spool off;
Shared Pool SQL With Most Loads
----------------------------------------------------------------
The SQL_TEXT output can actually contain html code which will mess
with the browsers display. Used replace() to change them to html meta.(ah) set termout on prompt Retreiving Shared Pool SQL with Most Loads... set termout off spool e:\info\listsql3.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Shared Pool SQL with Most Loads (>50)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=listsql3_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=listsql3_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr><td bgcolor=#6699cc><b>Loads</b></td> prompt <td width=300 bgcolor=#6699cc><b>SQL Text</b></td> prompt </tr> select '<tr><td valign=top>'||executions||'</td>', '<td>'||replace(replace(sql_text,'<','<'),'>','>') ||'</td></tr>' -- from v$sqlarea where loads > 50 order by executions desc; prompt </table></center> prompt </body></html> spool off;
Resource Usage Statistics By User
---------------------------------------------------------------- set termout on prompt Retreiving Resource Usage Statistics by User... set termout off spool e:\info\resource1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Resource Usage Statistics By User (Detail)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=resource1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=resource1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellspacing=0 cellpadding=0><tr><td bgcolor=#6699cc><b>SID</b></td> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td bgcolor=#6699cc><b>Statistic</b></td> prompt <td bgcolor=#6699cc><b>Value</b></td> prompt </tr> column ses.sid format 999999; select '<tr><td align=right>'||ses.sid||'</td><td>'||nvl(ses.username,'ORACLE PROC')||'</td>', '<td>'||sn.name||'</td><td align=right>'|| sest.value||'</td></tr>' from v$session ses, v$statname sn, v$sesstat sest where ses.sid=sest.sid and sn.statistic# = sest.statistic# and sest.value is not null and sest.value != 0 order by ses.username, ses.sid, sn.name; prompt </table></center> prompt </body></html> spool off;
Session I/O By User
---------------------------------------------------------------- set termout on prompt Retreiving Session I/O By User... set termout off spool e:\info\sessio.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Session I/O By User</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sessio_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sessio_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellspacing=0 cellpadding=0><tr> prompt <td bgcolor=#6699cc><b>Username/OSUser</b></td> prompt <td bgcolor=#6699cc><b>Process ID</b></td> prompt <td bgcolor=#6699cc><b>SID/Serial #</b></td> prompt <td bgcolor=#6699cc><b>Physical Reads</b></td> prompt <td bgcolor=#6699cc><b>Block Gets</b></td> prompt <td bgcolor=#6699cc><b>Consistent<br>Gets</b></td> prompt <td bgcolor=#6699cc><b>Block Changes</b></td> prompt <td bgcolor=#6699cc><b>Consistent<br>Changes</b></td> prompt </tr> column ses.sid format 999999; select '<tr><td>'||nvl(ses.username,'ORACLE PROC')||'/'||osuser||'</td>'|| '<td align=right>'||process||'</td><td align=right>'||ses.sid||'/'||serial#||'</td><td align=right>'||physical_reads|| '</td><td align=right>'||block_gets||'</td><td align=right>'||consistent_gets||'</td><td align=right>'||block_changes||

'</td><td align=right>'||Consistent_changes||'</td></tr>' from v$session ses, v$sess_io sio
where ses.sid = sio.sid
order by physical_reads,ses.username;
prompt </table></center>
prompt </body></html>
spool off;

Cursors Open By User
---------------------------------------------------------------- set termout on prompt Retreiving Cursors Open By User... set termout off spool e:\info\resource2.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Open Cursors By User</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=resource2_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=resource2_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellspacing=0 cellpadding=0><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td width=300 bgcolor=#6699cc><b>SQL</b></td> prompt </tr> select '<tr><td>'||nvl(username,'ORACLE PROC')||'('||v$session.sid||')</td>' "User Session", '<td>'||replace(replace(sql_text,'<','<'),'>','>') ||'</td></tr>' from v$open_cursor , v$session where v$session.saddr = v$open_cursor.saddr order by 1; prompt </table></center> prompt </body></html> spool off;
Cursors RUNNING By User
---------------------------------------------------------------- set termout on prompt Retreiving Cursors Running By User... set termout off spool e:\info\resource3.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Running Curosrs By User</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=resource3_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=resource3_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table cellspacing=0 cellpadding=0><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td width=300 bgcolor=#6699cc><b>SQL</b></td> prompt </tr> select '<tr><td>'||nvl(username,'ORACLE PROC')||'('||v$session.sid||')</td>' "User Session", '<td>'||replace(replace(sql_text,'<','<'),'>','>') ||'</td></tr>' from v$open_cursor , v$session where v$session.sql_address = v$open_cursor.address and v$session.sql_hash_value = v$open_cursor.hash_value order by 1; prompt </table></center> prompt </body></html> spool off;
Total Shared Pool Reload Statistics
---------------------------------------------------------------- set termout on prompt Retreiving Shared Pool V$librarycache Statistics... set termout off spool e:\info\shpool1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Library Cache Statistics</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=shpool1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=shpool1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Namespace</b></td> prompt <td bgcolor=#6699cc><b>Reload</b></td> prompt <td bgcolor=#6699cc><b>Gets</b></td> prompt <td bgcolor=#6699cc><b>GetHits</b></td> prompt <td bgcolor=#6699cc><b>GetHit<br>Ratio</b></td> prompt <td bgcolor=#6699cc><b>Pins</b></td> prompt <td bgcolor=#6699cc><b>PinHits</b></td> prompt <td bgcolor=#6699cc><b>PinHit<br>Ratio</b></td> prompt </tr> select '<tr><td>'||namespace||'</td><td align=right>'||reloads||'</td><td align=right>'||gets||'</td><td align=right>'|| gethits||'</td><td align=right>'|| to_char(round(gethitratio*100,2),'990.90')||'</td><td align=right>'||pins||'</td><td align=right>'||pinhits||'</td><td align=right>'|| to_char(round(pinhitratio*100,2),'990.90')||'</td></tr>' from v$librarycache; prompt </table> prompt <pre> select 'PINS= '||sum(pins)||' Reloads='|| sum(reloads)||' Percentage='|| round(sum(reloads)/(sum(pins)+sum(reloads))*100,2) from v$librarycache; select 'Pins(HITS)= '||sum(pins)||' Reloads (MISSES)='||sum(reloads)|| ' Hit Ratio % ='||round((sum(pins)/(sum(pins)+sum(reloads)))*100,2) from v$librarycache; prompt </pre> prompt </table> prompt </center> prompt </body></html> spool off;
Memory Usage Of the Shared Pool
---------------------------------------------------------------- set termout on prompt Retreiving Memory Usage of the Shared Pool... set termout off spool e:\info\shpool2.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Memory Usage of Shared Pool (>10000)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=shpool2_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=shpool2_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Owner</b></td> prompt <td bgcolor=#6699cc><b>Object</b></td> prompt <td bgcolor=#6699cc><b>Sharable Memory</b></td> prompt </tr>

select '<tr><td>'||owner||'</td><td>'|| name||' - '||type||'</td>',

'<td align=right>'||sharable_mem||'</td></tr>' from v$db_object_cache
where sharable_mem > 10000 and

type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') order by sharable_mem desc;

prompt </table></center>
prompt </body></html>
spool off;

Loads Into The Shared Pool
---------------------------------------------------------------- set termout on prompt Retreiving Loads into the Shared Pool... set termout off spool e:\info\shpool3.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Loads Into The Shared Pool (>3)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=shpool3_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=shpool3_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Owner</b></td> prompt <td bgcolor=#6699cc><b>Object</b></td> prompt <td bgcolor=#6699cc><b>Loads</b></td> prompt </tr>

select '<tr><td>'||owner||'</td><td>'|| name||' - '||type||'</td>',

'<td align=right>'||loads||'</td></tr>' from v$db_object_cache
where loads > 3 and

type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') order by loads desc;

prompt </table></center>
prompt </body></html>
spool off;

Shared Pool Executions
---------------------------------------------------------------- set termout on prompt Retreiving Shared Pool Executions... set termout off spool e:\info\shpool4.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Shared Pool Executions (>100)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=shpool4_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=shpool4_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Owner</b></td> prompt <td bgcolor=#6699cc><b>Object</b></td> prompt <td bgcolor=#6699cc><b>Executions</b></td> prompt </tr>

select '<tr><td>'||owner||'</td><td>'|| name||' - '||type||'</td>',

'<td align=right>'||executions||'</td></tr>' from v$db_object_cache
where executions > 100 and

type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') order by executions desc;

prompt </table></center>
prompt </body></html>
spool off;

Per Session Current Cursor Usage
---------------------------------------------------------------- set termout on prompt Retreiving Per Session Current Cursor Usage... set termout off

create or replace view opt_user_cursors as select

nvl(ss.username,'ORACLE PROC')||'('||se.sid||') ' user_process,

          sum(decode(name,'recursive calls',value)) "Recursive Calls",
          sum(decode(name,'opened cursors cumulative',value)) "Opened
Cursors",
          sum(decode(name,'opened cursors current',value)) "Current

Cursors"
from v$session ss, v$sesstat se, v$statname sn where se.statistic# = sn.statistic#

   and (     name  like '%opened cursors current%'
          OR name  like '%recursive calls%'
          OR name  like '%opened cursors cumulative%')

and se.sid = ss.sid
and ss.username is not null
group by nvl(ss.username,'ORACLE PROC')||'('||se.sid||') ';

spool e:\info\sescurusage1.htm
prompt <html><body bgcolor=white>
prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Per Session Current Cursor Usage</font></td></tr>
prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr>

prompt </table>
prompt <table width=90% cellpadding=0 cellspacing=0><tr>
prompt <td align=left valign=top>
prompt <a href=sescurusage1_notes.htm><img src=notes.gif  border=0

alt="Notes"></a>
prompt <a href=sescurusage1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a>
prompt </td>
prompt <td align=center valign=top>
select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual;
select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database;

prompt </tr>
prompt </table>
prompt <center>
prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Username</b></td>
prompt <td bgcolor=#6699cc><b>Recursive Calls</b></td>
prompt <td bgcolor=#6699cc><b>Opened Cursors</b></td>
prompt <td bgcolor=#6699cc><b>Current Cursors</b></td>
prompt </tr>

select '<tr><td>'||user_process||'</td><td align=right>'||"Recursive Calls"||'</td>',

'<td align=right>'||"Opened Cursors"||'</td><td align=right>'||"Current Cursors"||'</td></tr>' from opt_user_cursors
order by 1,2;

prompt </table></center>
prompt </body></html>
spool off;

CPU Usage By Session
------------------------------------------------------------- set termout on prompt Retreiving CPU Usage By Session... set termout off spool e:\info\sescpu1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>CPU Usage By Session</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sescpu1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sescpu1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td bgcolor=#6699cc><b>CPU Usage Rating</b></td> prompt </tr> col parameter format a30; select '<tr><td>'||nvl(ss.username,'ORACLE PROC')||'('||se.sid||')</td>' user_process, '<td align=right>'||value||'</td></tr>' from v$session ss, v$sesstat se, v$statname sn where se.statistic# = sn.statistic# and name like '%CPU used by this session%' and se.sid = ss.sid order by user_process,value desc; prompt </table></center> select '<hr>Note: Requires <i>timed_statistics</i> be set to TRUE.' from dual where 'FALSE' in (select value from v$parameter where name='timed_statistics');

prompt </body></html>
spool off;

Statistics Usage Grouped By Session
------------------------------------------------------------- set termout on prompt Retreiving System Statistics Groups By by Session... set termout off spool e:\info\sesstat1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Session Stats Grouped By Session</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=sesstat1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=sesstat1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td bgcolor=#6699cc><b>Usage</b></td> prompt <td bgcolor=#6699cc><b>Parameter</b></td> prompt </tr> col parameter format a30; select '<tr><td>'||nvl(ss.username,'ORACLE PROC')||'('||se.sid||')</td>' user_process, '<td align=right>'||value||'</td><td>'||sn.name||'</td></tr>' from v$session ss, v$sesstat se, v$statname sn where se.statistic# = sn.statistic# and se.sid = ss.sid and se.value > 0 order by sn.name,se.value desc;

prompt </table></center>
prompt </body></html>
spool off;

Oracle Locking Check
------------------------------------------------------------- set termout on prompt Retreiving Oracle Locking Check Info (Enqueue Timeouts)... set termout off spool e:\info\lockcheck1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Check For Locking Problems</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=lockcheck1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=lockcheck1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <pre>
select '<font color=red>Warning:</font> Enqueue Timeouts are '||value||'.<br>Investigate increasing the ENQUEUE_RESOURCES parameter.'

from v$sysstat
where name = 'enqueue timeouts'
and value > 0;
prompt </pre>

prompt </body></html>
spool off;

Tables Without Indexes (can be long output)
------------------------------------------------------------- set termout on prompt Retreiving Tables Without Indexes... set termout off create or replace view opt_nit as select owner, table_name from all_tables MINUS select owner, table_name from all_indexes;

spool e:\info\noindex1.htm
prompt <html><body bgcolor=white>
prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Tables Without Indexes (by Owner)</font></td></tr>
prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr>

prompt </table>
prompt <table width=90% cellpadding=0 cellspacing=0><tr>
prompt <td align=left valign=top>
prompt <a href=noindex1_notes.htm><img src=notes.gif  border=0

alt="Notes"></a>
prompt <a href=noindex1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a>
prompt </td>
prompt <td align=center valign=top>
select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual;
select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database;

prompt </tr>
prompt </table>
prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Username</b></td>

prompt <td bgcolor=#6699cc><b>Tablename</b></td> prompt </tr>

select '<tr><td>'||owner||'</td><td>'||table_name||'</td></tr>' from opt_nit
order by owner,table_name;

prompt </table></center>
prompt </body></html>
spool off;

Tables With Questionable Indexes
------------------------------------------------------------- set termout on prompt Retreiving Tables With Questionable Indexes... set termout off spool e:\info\unsureidx1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Tables With Questionable Indexes</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=unsureidx1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=unsureidx1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Owner</b></td>
prompt <td bgcolor=#6699cc><b>Tablename</b></td>
prompt <td bgcolor=#6699cc><b>Column</b></td>
prompt </tr>

select
'<tr><td>'||table_owner||'</td><td>'||table_name||'</td><td>'||column_name||'</td></tr>'

from all_ind_columns
where column_position =1
group by table_owner, table_name , column_name having count(*) > 1 ;

prompt </table></center>
prompt </body></html>
spool off;

Foreign Constraints and Columns Without an Index on Child Table
--------------------------------------------------------------- set termout on prompt Retreiving Foreign Constaints and Columns Without an Index On Child Table... set termout off spool e:\info\fcproblem1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Foreign Constraints and Columns Without an Index on Child Table</font></td></tr>

prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr>

prompt </table>
prompt <table width=90% cellpadding=0 cellspacing=0><tr>
prompt <td align=left valign=top>
prompt <a href=fcproblem1_notes.htm><img src=notes.gif  border=0

alt="Notes"></a>
prompt <a href=fcproblem1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a>
prompt </td>
prompt <td align=center valign=top>
select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual;
select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database;

prompt </tr>
prompt </table>
prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Owner</b></td>
prompt <td bgcolor=#6699cc><b>Constraint Name</b></td>

prompt <td bgcolor=#6699cc><b>Column/Position</b></td> prompt <td bgcolor=#6699cc><b>Problem</b></td>

prompt </tr>

select
'<tr><td>'||acc.owner||'</td><td>'||acc.constraint_name||'</td><td>'||acc.column_name

||'['||acc.position||']</td><td>'||'No Index</td></tr>' from all_cons_columns acc, all_constraints ac where ac.constraint_name = acc.constraint_name and ac.constraint_type = 'R'
and (acc.owner, acc.table_name, acc.column_name, acc.position) in (select acc.owner, acc.table_name, acc.column_name, acc.position

    from all_cons_columns acc, all_constraints ac    where ac.constraint_name = acc.constraint_name      and ac.constraint_type = 'R'
  MINUS
  select table_owner, table_name, column_name, column_position     from all_ind_columns)
order by acc.owner, acc.constraint_name, acc.column_name, acc.position;

prompt </table></center>
prompt </body></html>
spool off;

List All Segments with More than 5 Extents
--------------------------------------------------------------- set termout on prompt Retreiving List of All Segments with More than 5 extents... set termout off spool e:\info\segfrag1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Segments With More than 5 Extents</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=segfrag1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=segfrag1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Owner</b></td>
prompt <td bgcolor=#6699cc><b>Tablespace Name</b></td>
prompt <td bgcolor=#6699cc><b>Segment Name / Type<b></b></td>
prompt <td bgcolor=#6699cc><b>Size (bytes)<b></b></td>
prompt <td bgcolor=#6699cc><b>Extents</b></td>
prompt </tr>

col segment_name format a50;
select '<tr><td>'||owner||'</td>','<td>'||tablespace_name||'</td>',

'<td>'||segment_name||decode(segment_type,'TABLE','[T]', 'INDEX', '[I]',

                           'ROLLBACK','[R]', '[O]')||'</td>'
segment_name,
        '<td align=right>'||to_char(sum(bytes),'999,999,999')||'</td>'

sizing,

'<td align=right>'||count(*)||'</td></tr>' from dba_extents
group by '<tr><td>'||owner||'</td>', '<td>'||tablespace_name||'</td>',

'<td>'||segment_name||decode(segment_type,'TABLE','[T]', 'INDEX', '[I]',
'ROLLBACK','[R]', '[O]')||'</td>' having count(*) > 5;

prompt </table></center>
prompt </body></html>
spool off;

Tablespace Free Space Report
--------------------------------------------------------------- set termout on prompt Retreiving Tablespaces Free Space Info... set termout off spool e:\info\tsfree1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Tablespace Percent Full Report</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=tsfree1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=tsfree1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Tablespace Name</b></td>
prompt <td bgcolor=#6699cc><b>Allocated</b></td>
prompt <td bgcolor=#6699cc><b>Free <b></b></td>
prompt <td bgcolor=#6699cc><b>PCT Full<b></b></td>

prompt </tr>
col segment_name format a50;

select '<tr><td>'||a.tablespace_name||'</td><td
align=right>'||to_char(a.bytes,'999,999,999,999')||'</td><td
align=right>'||to_char(b.bytes,'999,999,999,999')||'</td><td
align=right>'||

to_char(round(((a.bytes-b.bytes)/a.bytes)*100,2),'990.90')||'</td></tr>'

from opt_dfs a,opt_tss b
where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc; prompt </table></center>
prompt </body></html>
spool off;

User Creations
--------------------------------------------------------------- set termout on prompt Retreiving Info on User Objects,Procedures,Functions,Triggers,Sequences,etc... set termout off spool e:\info\usrcreat1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>User Creations</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=usrcreat1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=usrcreat1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Username</b></td>
prompt <td bgcolor=#6699cc><b>Tables</b></td>
prompt <td bgcolor=#6699cc><b>Indexes</b></td>
prompt <td bgcolor=#6699cc><b>Synonyms</b></td>
prompt <td bgcolor=#6699cc><b>Views</b></td>
prompt <td bgcolor=#6699cc><b>Sequences</b></td>
prompt <td bgcolor=#6699cc><b>Proc.</b></td>
prompt <td bgcolor=#6699cc><b>Func.</b></td>
prompt <td bgcolor=#6699cc><b>Pkg's</b></td>
prompt <td bgcolor=#6699cc><b>Triggers</b></td>
prompt <td bgcolor=#6699cc><b>Dep</b></td>
prompt </tr>
select '<tr><td>'||username||'</td>',
       '<td align=right>'||count(decode(O.type, 2,O.obj#,''))||'</td>'
Tab,
       '<td align=right>'||count(decode(O.type, 1,O.obj#,''))||'</td>'
Ind,
       '<td align=right>'||count(decode(O.type, 5,O.obj#,''))||'</td>'
Syn,
       '<td align=right>'||count(decode(O.type, 4,O.obj#,''))||'</td>'
Vew,
       '<td align=right>'||count(decode(O.type, 6,O.obj#,''))||'</td>'
Seq,
       '<td align=right>'||count(decode(O.type, 7,O.obj#,''))||'</td>'
Prc,
       '<td align=right>'||count(decode(O.type, 8,O.obj#,''))||'</td>'
Fun,
       '<td align=right>'||count(decode(O.type, 9,O.obj#,''))||'</td>'
Pck,
       '<td align=right>'||count(decode(O.type,12,O.obj#,''))||'</td>'
Trg,
       '<td align=right>'||count(decode(O.type,10,O.obj#,''))||'</td>'

Dep
from sys.obj$ O,

sys.dba_users U
where U.user_id = O.owner# (+)
group by '<tr><td>'||username||'</td>' order by 1;

prompt </table></center>
prompt </body></html>
spool off;

System Datafiles
--------------------------------------------------------------- set termout on prompt Retreiving List of System Datafiles... set termout off spool e:\info\datafiles.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Oracle Datafiles</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=datafiles_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=datafiles_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Datafile</b></td> prompt <td bgcolor=#6699cc><b>Bytes</b></td> prompt </tr>

select '<tr><td>'||file_name||'</td><td
align=right>'||to_char(bytes,'999,999,999')||'</td></tr>' from dba_data_files
order by bytes;

prompt </table></center>
prompt </body></html>
spool off;

Additional Extent Information
--------------------------------------------------------------- set termout on prompt Retreiving Miscellaneous Extent Info... set termout off spool e:\info\addextent.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Miscellaneous Extent Info</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=addextent_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=addextent_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Tablespace</b></td>
prompt <td bgcolor=#6699cc><b>Total Free Space</b></td>
prompt <td bgcolor=#6699cc><b>Default Initial Extent</b></td>

prompt </tr>

select '<tr><td>'||name||'</td>',
   '<td>'||(sum(length)*2048)||'</td>' "Total Free Space",
   '<td>'||(max(length)*2048)||'</td>' "Largest Free Extent",

'<td>'||(dflinit*2048)||'</td></tr>' "Default Initial Extent" from sys.fet$ a, sys.ts$ b
where a.ts# =b.ts#
group by name, dflinit;

prompt </table></center>
prompt </body></html>
spool off;

Objects Extent Warnings
--------------------------------------------------------------- set termout on prompt Retreiving Object List Where Next Extent May Cause Error... set termout off spool e:\info\objextwarn.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Object Extent Warnings<br>Objects Which May Error On Next Extent</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=objextwarn_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=objextwarn_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Username</b></td>
prompt <td bgcolor=#6699cc><b>Segment Name</b></td>
prompt <td bgcolor=#6699cc><b>Tablespace</b></td>
prompt <td bgcolor=#6699cc><b>Next Extent</b></td>
prompt </tr>

create or replace view opt_objext_warn as SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       t.next_extent
   FROM sys.dba_segments seg,
        sys.dba_tables   t

WHERE (seg.segment_type = 'TABLE'
AND seg.segment_name = t.table_name AND seg.owner = t.owner
AND NOT EXISTS

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  t.tablespace_name
                and bytes               >=  t.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'CLUSTER',  c.next_extent)
   FROM sys.dba_segments seg,
        sys.dba_clusters c
WHERE   (seg.segment_type = 'CLUSTER'
  AND    seg.segment_name = c.cluster_name
  AND    seg.owner        = c.owner
  AND    NOT EXISTS
            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  c.tablespace_name
                and bytes               >=  c.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'INDEX',    i.next_extent )
   FROM sys.dba_segments seg,
        sys.dba_indexes  i

WHERE (seg.segment_type = 'INDEX'
AND seg.segment_name = i.index_name AND seg.owner = i.owner
AND NOT EXISTS

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  i.tablespace_name
                and bytes               >=  i.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'ROLLBACK', r.next_extent)
   FROM sys.dba_segments seg,
        sys.dba_rollback_segs r

where (seg.segment_type = 'ROLLBACK'
AND seg.segment_name = r.segment_name AND seg.owner = r.owner
AND NOT EXISTS

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  r.tablespace_name
                and bytes               >=  r.next_extent     ));

select
'<tr><td>'||owner||'</td><td>'||segment_name||'</td><td>'||segment_type||

'</td><td>'||tablespace_name||'</td><td align=right>'||next_extent||'</td></tr>' from opt_objext_warn
order by owner,segment_name;

prompt </table></center>
prompt </body></html>
spool off;

Latch Gets and Misses
--------------------------------------------------------------- set termout on prompt Retreiving Latch Gets and Misses Stats... set termout off spool e:\info\latch1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Latch Gets & Misses</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=latch1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=latch1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Latch Name</b></td>
prompt <td bgcolor=#6699cc><b>Gets</b></td>
prompt <td bgcolor=#6699cc><b>Misses</b></td>
prompt <td bgcolor=#6699cc><b>Immediate Gets</b></td>
prompt <td bgcolor=#6699cc><b>Immediate Misses</b></td>
prompt </tr>

select '<tr><td>'||substr(name,1,25)||'</td><td align=right>'||gets||'</td><td align=right>'||misses

||'</td><td align=right>'||immediate_gets||'</td><td align=right>'||immediate_misses||'</td></tr>' from v$latch
where misses > 0

or immediate_misses > 0;
prompt </table></center>
prompt </body></html>
spool off;

Checkpoint Intervals : Shortest & Average
--------------------------------------------------------------- set termout on prompt Retreiving Checkpoint Intervals (Shortest & Average)... set termout off spool e:\info\cpinterval1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>CheckPoint Interval (Min & Avg)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=cpinterval1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=cpinterval1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Shortest Checkpoint Interval</b></td>
prompt <td bgcolor=#6699cc><b>Average Checkpoint Interval</b></td>
prompt </tr>

select '<tr><td
align=center>'||round(min(to_number(to_date(lh2.first_time,'mm/dd/yy hh24:mi:ss')

to_date(lh1.first_time,'mm/dd/yy hh24:mi:ss') )
- 24 * 60),2)||'</td>' "Min Minutes BW Checkpoints", '<td align=center>'||round(avg(to_number(to_date(lh2.first_time,'mm/dd/yy hh24:mi:ss')
to_date(lh1.first_time,'mm/dd/yy hh24:mi:ss') )
- 24 * 60),2)||'</td></tr>' "Avg Minutes BW Checkpoints" from v$loghist lh1, v$loghist lh2 where lh1.sequence# + 1 = lh2.sequence# and lh1.sequence# < (select max (sequence#) from v$loghist );

prompt </table></center>
prompt </body></html>
spool off;

Checkpoint Intervals
--------------------------------------------------------------- set termout on prompt Retreiving Detail List of Time Between Checkpoints... set termout off drop view opt_min_bw_checkpoints; create or replace view opt_min_bw_checkpoints as select to_number(to_date(lh2.first_time,'mm/dd/yy hh24:mi:ss')
- to_date(lh1.first_time,'mm/dd/yy hh24:mi:ss') )
  - 24 * 60 "Minutes BW Checkpoints" from v$loghist lh1, v$loghist lh2 where lh1.sequence# + 1 = lh2.sequence# and lh1.sequence# < (select max (sequence#) from v$loghist ); spool e:\info\cpinterval2.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Checkpoint Interval Detail</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=cpinterval2_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=cpinterval2_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Minutes Between Checkpoints</b></td>
prompt </tr>

-- select '<tr><td align=right>'||round("Minutes BW
Checkpoints",2)||'</td></tr>'
-- from opt_min_bw_checkpoints

order by "Minutes BW Checkpoints" asc; prompt <tr><td><b>Report Commented Out Due To Long Runtime</b></td></tr>

prompt </table></center>
prompt </body></html>
spool off;

REDO Specific Info
--------------------------------------------------------------- set termout on prompt Retreiving Redo Buffer Statistics... set termout off spool e:\info\redostat1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Redo Buffer Statistics</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=redostat1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=redostat1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Parameter</b></td>
prompt <td bgcolor=#6699cc><b>Value</b></td>
prompt </tr>

select '<tr><td>'||name||'</td><td align=right>'|| value ||'</td></tr>'

from v$sysstat
where name like 'redo%'
and value > 0;

prompt </table></center>
prompt </body></html>
spool off;

REDO Log Buffer Contention
--------------------------------------------------------------- set termout on prompt Retreiving Redo Log Buffer Contention Info... set termout off spool e:\info\redocont1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Redo Log Buffer Contention</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=redocont1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=redocont1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Redo Latch Name</b></td> prompt <td bgcolor=#6699cc><b>Gets</b></td> prompt <td bgcolor=#6699cc><b>Misses</b></td> prompt <td bgcolor=#6699cc><b>Immediate Gets</b></td> prompt <td bgcolor=#6699cc><b>Immediate Misses</b></td> prompt </tr> select '<tr><td>'||name||'</td><td align=right>'||gets||'</td><td align=right>'||misses||'</td><td align=right>'|| immediate_gets||'</td><td align=right>'||immediate_misses||'</td></tr>' from v$latch where name in ('redo allocation','redo copy'); prompt </table></center> prompt <br><p>Heavy access to the redo log buffer can result in contention for redo log prompt buffer latches. The above stats are from v$latch.</p> prompt </body></html> spool off;
Reserve Pool Settings
--------------------------------------------------------------- set termout on prompt Retreiving Reserve Pool Settings... set termout off spool e:\info\reservepool1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Reserve Pool Settings for the Shared Pool Area</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=reservepool1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=reservepool1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center>

prompt <table><tr>
prompt <td bgcolor=#6699cc><b>Parameter</b></td>
prompt <td bgcolor=#6699cc><b>Value</b></td>
prompt </tr>

select '<tr><td>'||name||'</td><td align=right>'||value||'</td></tr>' from v$parameter
where name like '%reser%';

prompt </table></center>
prompt </body></html>
spool off;

Base Shared Pool Analysis
--------------------------------------------------------------- set termout on prompt Retreiving Shared Pool Size Info with short analysis... set termout off spool e:\info\shpool5.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Quick Check of Shared Pool Size</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=shpool5_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=shpool5_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <pre> prompt <b>Shared Pool Reserved Size Recommendation</b><br> select 'You may need to increase the SHARED_POOL_RESERVED_SIZE<br>', 'Request Failures = '||request_failures||'<br>' from v$shared_pool_reserved where request_failures > 0 and 0 != ( select to_number(value) from v$parameter where name = 'shared_pool_reserved_size' ); prompt <br></br> select 'You may be able to decrease the SHARED_POOL_RESERVED_SIZE<br>' ,

'Request Failures = '||request_failures||'<br>' from v$shared_pool_reserved
where request_failures < 5
and 0 != ( select to_number(value)

               from v$parameter
              where name = 'shared_pool_reserved_size' );
prompt </pre>

prompt <hr>
prompt <p><i>Note: An empty report indicates that the v$shared_pool_reserver.request_failures
prompt is within tolerance</p><br><br>
prompt </body></html>
spool off;

Database Files (datafiles,archive)
--------------------------------------------------------------- set termout on prompt Retreiving Database Files (datafiles,archive log dest,redo member files)... set termout off spool e:\info\dbfiles1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Database Files (datafile/archived log/redo)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=dbfiles1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=dbfiles1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Filename</b></td> prompt <td bgcolor=#6699cc><b>Usage</b></td> prompt </tr>

select '<tr><td>Archived Log Directory</td><td>'||value||'</td></tr>'   from v$parameter
where name = 'log_archive_dest'
UNION
select '<tr><td>Datafile</td><td>'||name ||'</td></tr>'   from v$datafile
UNION
select '<tr><td>LogFile Member</td><td>'||member||'</td></tr>'   from v$logfile ;

prompt </table></center>
prompt </body></html>
spool off;

Inconsistent ColumnDatatype
--------------------------------------------------------------- set termout on prompt Retreiving Info on Inconsistent Column Datatypes Amount User Tables... set termout off spool e:\info\colconsistent.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Inconsistent Column Datatypes (by Owner)</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=colconsistent_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=colconsistent_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td bgcolor=#6699cc><b>Column</b></td> prompt <td bgcolor=#6699cc><b>Table / Datatype</b></td> prompt </tr>
select '<tr><td>'||owner||'</td><td>'||column_name||
'</td><td>'|| table_name||' '||data_type||'('||
decode(data_type, 'NUMBER', data_precision, data_length)||')</td></tr>' "Characteristics"
from all_tab_columns
where (column_name, owner)
IN
(select column_name, owner
from all_tab_columns
group by column_name, owner
having min(decode(data_type, 'NUMBER', data_precision, data_length))
< max(decode(data_type, 'NUMBER', data_precision, data_length)) )
and owner not in ('SYS', 'SYSTEM')
order by column_name,table_name; prompt <tr><td colspan=3><b>Query Commented Out Due To Long Runtime</b></td></tr> prompt </table></center>

prompt </body></html>
spool off;

Invalid Objects
--------------------------------------------------------------- set termout on prompt Retreiving List of Current Invalid Objects... set termout off spool e:\info\badobjects.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Invalid Objects By Owner</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=badobjects_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=badobjects_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td bgcolor=#6699cc><b>Object Type</b></td> prompt <td bgcolor=#6699cc><b>Object Name</b></td> prompt <td bgcolor=#6699cc><b>Status</b></td> prompt </tr>

select
'<tr><td>'||owner||'</td><td>'||object_type||'</td><td>'||object_name||'</td><td>'|| status||'</td></tr>'
from all_objects
where status = 'INVALID'
ORDER BY owner, object_type, object_name;

prompt </table></center>
prompt </body></html>
spool off;

List All Triggers
--------------------------------------------------------------- set termout on prompt Retreiving List of All Triggers... set termout off spool e:\info\triggers1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Trigger Listing By Tablename</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=triggers1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=triggers1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Tablename</b></td> prompt <td bgcolor=#6699cc><b>Trigger Name</b></td> prompt <td bgcolor=#6699cc><b>Status</b></td> prompt </tr>

select '<tr><td>'||table_name||'</td><td>'||trigger_name||'</td><td>'|| status||'</td></tr>'
from all_triggers
order by table_name, trigger_name;

prompt </table></center>
prompt </body></html>
spool off;

List All Pinned Packages
--------------------------------------------------------------- set termout on prompt Retreiving List of All Pinned Packages... set termout off spool e:\info\pin1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Current Pinned Packages</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=pin1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=pin1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Object Name</b></td> prompt <td bgcolor=#6699cc><b>Kept Status</b></td> prompt </tr> select '<tr><td>'||name||'</td><td>'||kept||'</td></tr>' from v$db_object_cache where kept not like 'N%'; prompt </table></center> prompt </body></html> spool e:\info\off;
Tablespace Details
--------------------------------------------------------------- set termout on prompt Retreiving Tablespace Details... set termout off spool e:\info\tsinfo.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Tablespace Parameters</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=tsinfo_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=tsinfo_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Tablespace Name</b></td> prompt <td bgcolor=#6699cc><b>Initial Extent</b></td> prompt <td bgcolor=#6699cc><b>Next Extent</b></td> prompt <td bgcolor=#6699cc><b>Min Extents</b></td> prompt <td bgcolor=#6699cc><b>Max Extents</b></td> prompt <td bgcolor=#6699cc><b>PCT Increase</b></td> prompt <td bgcolor=#6699cc><b>Status</b></td> prompt <td bgcolor=#6699cc><b>Contents</b></td> prompt </tr> select '<tr><td>'||tablespace_name||'</td><td align=right>'||initial_extent||'</td><td align=right>'||next_extent|| '</td><td align=right>'||min_extents|| '</td><td align=right>'||max_extents||'</td><td align=right>'|| decode(pct_increase,0,'0','<font color=red><b>'||pct_increase||'</b></font>') ||'</td><td>'||status|| '</td><td>'||contents||'</td></tr>' from dba_tablespaces order by tablespace_name; prompt </table></center> prompt </body></html> spool off;
Tablespace Coalesced Extents (dba_free_space_coalesced)
--------------------------------------------------------------- set termout on prompt Retreiving Tablespace Coalesced Info... set termout off spool e:\info\tcoal.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Tablespace Coalesced Extents</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=tcoal_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=tcoal_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Tablespace Name</b></td> prompt <td bgcolor=#6699cc><b>Total Extents</b></td> prompt <td bgcolor=#6699cc><b>Extents<br>Coalesced</b></td> prompt <td bgcolor=#6699cc><b>% Extents<br>Coalesced</b></td> prompt <td bgcolor=#6699cc><b>Total Bytes</b></td> prompt <td bgcolor=#6699cc><b>Bytes Coalesced</b></td> prompt <td bgcolor=#6699cc><b>Total Blocks</b></td> prompt <td bgcolor=#6699cc><b>Blocks Coalesced</b></td> prompt <td bgcolor=#6699cc><b>% Blocks<br>Coalesced</b></td>

prompt </tr>

select '<tr><td>'||tablespace_name||'</td><td align=right>'||total_extents||'</td><td align=right>'||

extents_coalesced||'</td><td
align=right>'||percent_extents_coalesced||'</td><td align=right>'||

total_bytes||'</td><td align=right>'||bytes_coalesced||'</td><td align=right>'||total_blocks||

'</td><td align=right>'||blocks_coalesced||'</td><td align=right>'||percent_blocks_coalesced||'</td></tr>' from dba_free_space_coalesced
order by tablespace_name;

prompt </table></center>
prompt </body></html>
spool off;

Users With SYSTEM Tablespace + Temp
--------------------------------------------------------------- set termout on prompt Retreiving List of All Users Using SYSTEM Tablespace... set termout off spool e:\info\usertssys.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Users Improperly Using The SYSTEM Tablespace</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=usertssys_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=usertssys_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td bgcolor=#6699cc><b>Default Tablespace</b></td> prompt <td bgcolor=#6699cc><b>Temporary Tablespace</b></td> prompt </tr>

select
'<tr><td>'||username||'</td><td>'||default_tablespace||'</td><td>'||

temporary_tablespace||'</td></tr>' from dba_users
where default_tablespace = 'SYSTEM' OR temporary_tablespace='SYSTEM' order by username;

prompt </table></center>
prompt </body></html>
spool off;

JOBS
--------------------------------------------------------------- set termout on prompt Retreiving Scheduled Jobs... set termout off spool e:\info\jobs.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Registered Jobs</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=jobs_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=jobs_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Log User</b></td> prompt <td bgcolor=#6699cc><b>Schema</b></td> prompt <td bgcolor=#6699cc><b>Job #</b></td> prompt <td bgcolor=#6699cc><b>Failures</b></td> prompt <td bgcolor=#6699cc><b>Interval</b></td> prompt <td bgcolor=#6699cc><b>What</b></td> prompt </tr> select '<tr><td>'||log_user||'</td><td>'||schema_user||'</td><td>'|| job||'</td><td>'||failures||'</td><td>'||interval||'</td><td>'||

substr(what,1,100)||'</td></tr>' from dba_jobs
order by log_user;
prompt </table></center>
prompt </body></html>
spool off;

Clusters
--------------------------------------------------------------- set termout on prompt Retreiving Table Clusters... set termout off spool e:\info\cluster1.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Table Clusters</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=cluster1_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=cluster1_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Username</b></td> prompt <td bgcolor=#6699cc><b>Cluster Name</b></td> prompt <td bgcolor=#6699cc><b>Table Name</b></td> prompt </tr> select '<tr><td>'||owner||'</td><td>'||cluster_name||'</td><td>'|| table_name||'</td></tr>' from dba_tables where cluster_name is not null order by owner,cluster_name,table_name; prompt </table></center> prompt </body></html> spool off;
DB Links
--------------------------------------------------------------- set termout on prompt Retreiving Database Links... set termout off spool e:\info\dblinks.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Database Links</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=dblinks_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=dblinks_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Owner ID</b></td> prompt <td bgcolor=#6699cc><b>DBLink</b></td> prompt </tr> select '<tr><td>'||owner_id||'</td><td>'||db_link||'</td></tr>' from v$dblink order by owner_id,db_link; prompt </table></center> prompt </body></html> spool off;
Snapshots
--------------------------------------------------------------- set termout on prompt Retreiving Database Snapshots... set termout off spool e:\info\dbsnap.htm prompt <html><body bgcolor=white> prompt <table width=90% cellpadding=0 cellspacing=0> prompt <tr><td align=left valign=bottom><font size=5>Database Snapshots</font></td></tr> prompt <tr><td colspan=2 valign=bottom><img src=blackpixel.gif width=100% height=4></td></tr> prompt </table> prompt <table width=90% cellpadding=0 cellspacing=0><tr> prompt <td align=left valign=top> prompt <a href=dbsnap_notes.htm><img src=notes.gif border=0 alt="Notes"></a> prompt <a href=dbsnap_script.htm><img src=tools.gif border=0 alt="SQL Source"></a> prompt </td> prompt <td align=center valign=top> select '<b>Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS')||'</b></td>' from dual; select '<td valign=top align=right><b>Database: '||name||'</b></td>' from v$database; prompt </tr> prompt </table> prompt <center> prompt <table><tr> prompt <td bgcolor=#6699cc><b>Owner</b></td> prompt <td bgcolor=#6699cc><b>Snapshot Name</b></td> prompt <td bgcolor=#6699cc><b>Tablename</b></td> prompt </tr> select '<tr><td>'||owner||'</td><td>'||name||'</td><td>'||table_name||'</td></tr>'

from dba_snapshots
order by owner,name;
prompt </table></center>
prompt </body></html>
spool off;

Script Cleanup
--------------------------------------------------
drop tables and views

drop table opt_tot_read_writes;
drop view  opt_fts;
drop table opt_bh_temp;
drop table opt_user_hr;
drop view  opt_user_cursors;
drop view  opt_nit;
drop view  opt_objext_warn;
drop view  opt_min_bw_checkpoints;
drop view  opt_dfs;
drop view  opt_tss;

set termout on;
select 'Stop Time:'||to_char(sysdate,'MM/DD/YY HH:MI:SS') from dual; set termout off; Received on Mon Feb 22 1999 - 13:31:21 CET

This message: [ Message body ]
Next message: Jurij Modic: "Re: sql loader char limit ?"
Previous message: JR: "sql loader char limit ?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message