Overview Report 10g P1

+++++++

-- |Jeffrey M. Hunter
-- |jhunter@idevelopment.info
-- |www.idevelopment.info
 
-- |Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved.
 
-- |DATABASE : Oracle
-- |FILE  : dba_snapshot_database_10g.sql
-- |CLASS  : Database Administration
-- |PURPOSE  : This SQL script provides a detailed report (in HTML format) on
-- |all database metrics including installed options, storage,
-- |performance data, and security.
-- |VERSION  : This script was designed for Oracle Database 10g Release 2.
-- |Although this script will also work with Oracle Database 10g
-- |Release 1, several sections will error out from missing tables
-- |or columns.
-- |USAGE  :
-- ||
-- |sqlplus -s <dba>/<password>@<TNS string> @dba_snapshot_database_10g.sql
-- ||
-- |TESTING  : This script has been successfully tested on the following
-- |platforms:
-- ||
-- |Linux  : Oracle Database 10.2.0.3.0
-- |Linux  : Oracle RAC 10.2.0.3.0
-- |Solaris  : Oracle Database 10.2.0.2.0
-- |Solaris  : Oracle Database 10.2.0.3.0
-- |Windows XP : Oracle Database 10.2.0.3.0
-- ||
-- |NOTE  : As with any code, ensure to test this script in a development
-- |environment before attempting to run it in production.
 
prompt 
prompt +-----------------------------------------------------------------------------------------+
prompt |  Snapshot Database 10g Release 2
prompt |-----------------------------------------------------------------------------------------+
prompt | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. (www.idevelopment.info)
prompt +-----------------------------------------------------------------------------------------+
prompt
prompt Creating database report.
prompt This script must be run as a user with SYSDBA privileges.
prompt This process can take several minutes to complete.
prompt 
 
define reportHeader="<font size=+3 color=darkgreen><b>Snapshot Database 10<i>g</i> Release 2</b></font><hr>Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. (<a target=""_blank"" href=""http://www.idevelopment.info"">www.idevelopment.info</a>)<p>"
 
-- |SCRIPT SETTINGS
 
set termout  off
set echo  off
set feedback  off
set heading  off
set verify  off
set wrap  on
set trimspool  on
set serveroutput  on
set escape  on
 
set pagesize 50000
set linesize 175
set long  2000000000
 
clear buffer computes columns breaks
 
define fileName=dba_snapshot_database_10g
define versionNumber=5.3
 
-- |GATHER DATABASE REPORT INFORMATION
 
COLUMN tdate NEW_VALUE _date NOPRINT
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') tdate FROM dual;
 
COLUMN time NEW_VALUE _time NOPRINT
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual;
 
COLUMN date_time NEW_VALUE _date_time NOPRINT
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual;
 
COLUMN date_time_timezone NEW_VALUE _date_time_timezone NOPRINT
SELECT TO_CHAR(systimestamp, 'Mon DD, YYYY (') || TRIM(TO_CHAR(systimestamp, 'Day')) || TO_CHAR(systimestamp, ') "at" HH:MI:SS AM') || TO_CHAR(systimestamp, ' "in Timezone" TZR') date_time_timezone
FROM dual;
 
COLUMN spool_time NEW_VALUE _spool_time NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;
 
COLUMN dbname NEW_VALUE _dbname NOPRINT
SELECT name dbname FROM v$database;
 
COLUMN dbid NEW_VALUE _dbid NOPRINT
SELECT dbid dbid FROM v$database;
 
COLUMN platform_id NEW_VALUE _platform_id NOPRINT
SELECT platform_id platform_id FROM v$database;
 
COLUMN platform_name NEW_VALUE _platform_name NOPRINT
SELECT platform_name platform_name FROM v$database;
 
COLUMN global_name NEW_VALUE _global_name NOPRINT
SELECT global_name global_name FROM global_name;
 
COLUMN blocksize NEW_VALUE _blocksize NOPRINT
SELECT value blocksize FROM v$parameter WHERE name='db_block_size';
 
COLUMN startup_time NEW_VALUE _startup_time NOPRINT
SELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance;
 
COLUMN host_name NEW_VALUE _host_name NOPRINT
SELECT host_name host_name FROM v$instance;
 
COLUMN instance_name NEW_VALUE _instance_name NOPRINT
SELECT instance_name instance_name FROM v$instance;
 
COLUMN instance_number NEW_VALUE _instance_number NOPRINT
SELECT instance_number instance_number FROM v$instance;
 
COLUMN thread_number NEW_VALUE _thread_number NOPRINT
SELECT thread# thread_number FROM v$instance;
 
COLUMN cluster_database NEW_VALUE _cluster_database NOPRINT
SELECT value cluster_database FROM v$parameter WHERE name='cluster_database';
 
COLUMN cluster_database_instances NEW_VALUE _cluster_database_instances NOPRINT
SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances';
 
COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINT
SELECT user reportRunUser FROM dual;
 
-- |GATHER DATABASE REPORT INFORMATION
 
set heading on
 
set markup html on spool on preformat off entmap on -
head ' -
  <title>Database Report</title> -
  <style type="text/css"> -
  body  {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -
  p  {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -
  table,tr,td  {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#C0C0C0; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
  th  {font:bold 9pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} -
  h1  {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
  h2  {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
  a  {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.link  {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLink  {font:9pt Arial,Helvetica,sans-serif; color:#663300; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkDarkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkRed  {font:9pt Arial,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkDarkRed  {font:9pt Arial,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkGreen  {font:9pt Arial,Helvetica,sans-serif; color:#00ff00; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkDarkGreen {font:9pt Arial,Helvetica,sans-serif; color:#009900; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  </style>' -
body  'BGCOLOR="#C0C0C0"' -
table  'WIDTH="90%" BORDER="1"' 
 
spool &FileName._&_dbname._&_spool_time..html
 
set markup html on entmap off
 
-- |- REPORT HEADER -
 
prompt <a name=top></a>
prompt &reportHeader
 
-- |- REPORT INDEX -
 
prompt <a name="report_index"></a>
 
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Report Index</b></font><hr align="center" width="250"></center> -
<table width="90%" border="1"> -
<tr><th colspan="4">Database and Instance Information</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#report_header">Report Header</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#version">Version</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#options">Options</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#database_registry">Database Registry</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#feature_usage_statistics">Feature Usage Statistics</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#high_water_mark_statistics">High Water Mark Statistics</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#instance_overview">Instance Overview</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#database_overview">Database Overview</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#initialization_parameters">Initialization Parameters</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#control_files">Control Files</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#control_file_records">Control File Records</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#online_redo_logs">Online Redo Logs</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#redo_log_switches">Redo Log Switches</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#outstanding_alerts">Outstanding Alerts</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#statistics_level">Statistics Level</a></td> -
<td nowrap align="center" width="25%"><br></td> -
</tr>
 
prompt -
<tr><th colspan="4">Scheduler / Jobs</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#jobs">Jobs</a></td> -
<td nowrap align="center" width="25%"><br></td> -
<td nowrap align="center" width="25%"><br></td> -
<td nowrap align="center" width="25%"><br></td> -
</tr> -
<tr><th colspan="4">Storage</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#tablespaces">Tablespaces</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#data_files">Data Files</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#database_growth">Database Growth</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#tablespace_extents">Tablespace Extents</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#tablespace_to_owner">Tablespace to Owner</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#owner_to_tablespace">Owner to Tablespace</a></td> -
<td nowrap align="center" width="25%"><br></td> -
<td nowrap align="center" width="25%"><br></td> -
</tr>
 
prompt -
<tr><th colspan="4">UNDO Segments</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#undo_segments">UNDO Segments</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#undo_segment_contention">UNDO Segment Contention</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#undo_retention_parameters">UNDO Retention Parameters</a></td> -
<td nowrap align="center" width="25%"><br></td> -
</tr>
 
prompt -
<tr><th colspan="4">Backups</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#rman_backup_jobs">RMAN Backup Jobs</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#rman_configuration">RMAN Configuration</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#rman_backup_sets">RMAN Backup Sets</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#rman_backup_pieces">RMAN Backup Pieces</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#rman_backup_control_files">RMAN Backup Control Files</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#rman_backup_spfile">RMAN Backup SPFILE</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#archiving_mode">Archiving Mode</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#archive_destinations">Archive Destinations</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#archiving_instance_parameters">Archiving Instance Parameters</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#archiving_history">Archiving History</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#flash_recovery_area_parameters">Flash Recovery Area Parameters</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#flash_recovery_area_status">Flash Recovery Area Status</a></td> -
</tr>
 
prompt -
<tr><th colspan="4">Flashback Technologies</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#undo_retention_parameters">UNDO Retention Parameters</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#flashback_database_parameters">Flashback Database Parameters</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#flashback_database_status">Flashback Database Status</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#flashback_database_redo_time_matrix">Flashback Database Redo Time Matrix</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_recycle_bin">Recycle Bin</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
</tr>
 
prompt -
<tr><th colspan="4">Performance</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#sga_information">SGA Information</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#sga_target_advice">SGA Target Advice</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#sga_asmm_dynamic_components">SGA (ASMM) Dynamic Components</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#pga_target_advice">PGA Target Advice</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#file_io_statistics">File I/O Statistics</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#file_io_timings">File I/O Timings</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#average_overall_io_per_sec">Average Overall I/O per Second</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#redo_log_contention">Redo Log Contention</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#full_table_scans">Full Table Scans</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#sorts">Sorts</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_outlines">Outlines</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_outline_hints">Outline Hints</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#sql_statements_with_most_buffer_gets">SQL Statements With Most Buffer Gets</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#sql_statements_with_most_disk_reads">SQL Statements With Most Disk Reads</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_enabled_traces">Enabled Traces</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_enabled_aggregations">Enabled Aggregations</a></td> -
</tr>
 
prompt -
<tr><th colspan="4">Automatic Workload Repository - (AWR)</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#awr_workload_repository_information">Workload Repository Information</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#awr_snapshot_settings">AWR Snapshot Settings</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#awr_snapshot_list">AWR Snapshot List</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#awr_snapshot_size_estimates">AWR Snapshot Size Estimates</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#awr_baselines">AWR Baselines</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
</tr> -
<tr><th colspan="4">Sessions</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#current_sessions">Current Sessions</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#user_session_matrix">User Session Matrix</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_enabled_traces">Enabled Traces</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_enabled_aggregations">Enabled Aggregations</a></td> -
</tr>
 
prompt -
<tr><th colspan="4">Security</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#user_accounts">User Accounts</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#users_with_dba_privileges">Users With DBA Privileges</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#roles">Roles</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#default_passwords">Default Passwords</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#db_links">DB Links</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
</tr>
 
prompt -
<tr><th colspan="4">Objects</th></tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#object_summary">Object Summary</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#segment_summary">Segment Summary</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#top_100_segments_by_size">Top 100 Segments (by size)</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#top_100_segments_by_extents">Top 100 Segments (by number of extents)</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_directories">Directories</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_directory_privileges">Directory Privileges</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_libraries">Libraries</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_types">Types</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_type_attributes">Type Attributes</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_type_methods">Type Methods</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_collections">Collections</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_lob_segments">LOB Segments</a></td> -
</tr>
 
prompt -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#objects_unable_to_extend">Objects Unable to Extend</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#objects_which_are_nearing_maxextents">Objects Which Are Nearing MAXEXTENTS</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#invalid_objects">Invalid Objects</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#procedural_object_errors">Procedural Object Errors</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#objects_without_statistics">Objects Without Statistics</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#tables_suffering_from_row_chaining_migration">Tables Suffering From Row Chaining/Migration</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#users_with_default_tablespace_defined_as_system">Users With Default Tablespace - (SYSTEM)</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#users_with_default_temporary_tablespace_as_system">Users With Default Temp Tablespace - (SYSTEM)</a></td> -
</tr> -
<tr> -
<td nowrap align="center" width="25%"><a class="link" href="#objects_in_the_system_tablespace">Objects in the SYSTEM Tablespace</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#dba_recycle_bin">Recycle Bin</a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
<td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
</tr>
 
</table>
 
prompt <p>
 
-- +============================================================================+
-- ||
-- |<<<<<  Database and Instance Information  >>>>>
-- ||
-- +============================================================================+
 
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Database and Instance Information</u></b></font></center>
 
-- |- REPORT HEADER -
 
prompt 
prompt <a name="report_header"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Report Header</b></font><hr align="left" width="460">
 
prompt <table width="90%" border="1"> -
<tr><th align="left" width="20%">Report Name</th><td width="80%"><tt>&FileName._&_dbname._&_spool_time..html</tt></td></tr> -
<tr><th align="left" width="20%">Snapshot Database Version</th><td width="80%"><tt>&versionNumber</tt></td></tr> -
<tr><th align="left" width="20%">Run Date / Time / Timezone</th><td width="80%"><tt>&_date_time_timezone</tt></td></tr> -
<tr><th align="left" width="20%">Host Name</th><td width="80%"><tt>&_host_name</tt></td></tr> -
<tr><th align="left" width="20%">Database Name</th><td width="80%"><tt>&_dbname</tt></td></tr> -
<tr><th align="left" width="20%">Database ID</th><td width="80%"><tt>&_dbid</tt></td></tr> -
<tr><th align="left" width="20%">Global Database Name</th><td width="80%"><tt>&_global_name</tt></td></tr> -
<tr><th align="left" width="20%">Platform Name / ID</th><td width="80%"><tt>&_platform_name / &_platform_id</tt></td></tr> -
<tr><th align="left" width="20%">Clustered Database?</th><td width="80%"><tt>&_cluster_database</tt></td></tr> -
<tr><th align="left" width="20%">Clustered Database Instances</th><td width="80%"><tt>&_cluster_database_instances</tt></td></tr> -
<tr><th align="left" width="20%">Instance Name</th><td width="80%"><tt>&_instance_name</tt></td></tr> -
<tr><th align="left" width="20%">Instance Number</th><td width="80%"><tt>&_instance_number</tt></td></tr> -
<tr><th align="left" width="20%">Thread Number</th><td width="80%"><tt>&_thread_number</tt></td></tr> -
<tr><th align="left" width="20%">Database Startup Time</th><td width="80%"><tt>&_startup_time</tt></td></tr> -
<tr><th align="left" width="20%">Database Block Size</th><td width="80%"><tt>&_blocksize</tt></td></tr> -
<tr><th align="left" width="20%">Report Run User</th><td width="80%"><tt>&_reportRunUser</tt></td></tr> -
</table>
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- SET TIMING ON
 
-- |- VERSION -
 
prompt <a name="version"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Version</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN banner  FORMAT a120  HEADING 'Banner'
 
SELECT * FROM v$version;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- OPTIONS -
 
prompt <a name="options"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Options</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN parameter  HEADING 'Option Name'  ENTMAP off
COLUMN value  HEADING 'Installed?'  ENTMAP off
 
SELECT
  DECODE(  value
  , 'FALSE'
  , '<b><font color="#336699">' || parameter || '</font></b>'
  , '<b><font color="#336699">' || parameter || '</font></b>') parameter
  , DECODE(  value
  , 'FALSE'
  , '<div align="center"><font color="#990000"><b>' || value || '</b></font></div>'
  , '<div align="center">' || value || '</div>' ) value
FROM v$option
ORDER BY parameter;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- DATABASE REGISTRY -
 
prompt <a name="database_registry"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Database Registry</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN comp_id  FORMAT a75  HEADING 'Component ID'  ENTMAP off
COLUMN comp_name  FORMAT a75  HEADING 'Component Name'  ENTMAP off
COLUMN version  HEADING 'Version'  ENTMAP off
COLUMN status  FORMAT a75  HEADING 'Status'  ENTMAP off
COLUMN modified  FORMAT a75  HEADING 'Modified'  ENTMAP off
COLUMN control  HEADING 'Control'  ENTMAP off
COLUMN schema  HEADING 'Schema'  ENTMAP off
COLUMN procedure  HEADING 'Procedure'  ENTMAP off
 
SELECT
  '<font color="#336699"><b>' || comp_id  || '</b></font>' comp_id
  , '<div nowrap>' || comp_name || '</div>'  comp_name
  , version
  , DECODE(  status
  , 'VALID',  '<div align="center"><b><font color="darkgreen">' || status || '</font></b></div>'
  , 'INVALID', '<div align="center"><b><font color="#990000">'  || status || '</font></b></div>'
  ,  '<div align="center"><b><font color="#663300">'  || status || '</font></b></div>' ) status
  , '<div nowrap align="right">' || modified || '</div>'  modified
  , control
  , schema
  , procedure
FROM dba_registry
ORDER BY comp_name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- FEATURE USAGE STATISTICS -
 
prompt <a name="feature_usage_statistics"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Feature Usage Statistics</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN feature_name  FORMAT a115  HEADING 'Feature|Name'
COLUMN version  FORMAT a75  HEADING 'Version'
COLUMN detected_usages  FORMAT a75  HEADING 'Detected|Usages'
COLUMN total_samples  FORMAT a75  HEADING 'Total|Samples'
COLUMN currently_used  FORMAT a60  HEADING 'Currently|Used'
COLUMN first_usage_date  FORMAT a95  HEADING 'First Usage|Date'
COLUMN last_usage_date  FORMAT a95  HEADING 'Last Usage|Date'
COLUMN last_sample_date  FORMAT a95  HEADING 'Last Sample|Date'
COLUMN next_sample_date  FORMAT a95  HEADING 'Next Sample|Date'
 
SELECT
  '<div align="left"><font color="#336699"><b>' || name || '</b></font></div>'  feature_name
  , DECODE(  detected_usages
  , 0
  , version 
  , '<font color="#663300"><b>' || version || '</b></font>')  version
  , DECODE(  detected_usages
  , 0
  , '<div align="right">' || NVL(TO_CHAR(detected_usages), '<br>') || '</div>'
  , '<div align="right"><font color="#663300"><b>' || NVL(TO_CHAR(detected_usages), '<br>') || '</b></font></div>') detected_usages
  , DECODE(  detected_usages
  , 0
  , '<div align="right">' || NVL(TO_CHAR(total_samples), '<br>') || '</div>'
  , '<div align="right"><font color="#663300"><b>' || NVL(TO_CHAR(total_samples), '<br>') || '</b></font></div>')  total_samples
  , DECODE(  detected_usages
  , 0
  , '<div align="center">' || NVL(currently_used, '<br>') || '</div>'
  , '<div align="center"><font color="#663300"><b>' || NVL(currently_used, '<br>') || '</b></font></div>')  currently_used
  , DECODE(  detected_usages
  , 0
  , '<div align="right">' || NVL(TO_CHAR(first_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'
  , '<div align="right"><font color="#663300"><b>' || NVL(TO_CHAR(first_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</b></font></div>')  first_usage_date
  , DECODE(  detected_usages
  , 0
  , '<div align="right">' || NVL(TO_CHAR(last_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'
  , '<div align="right"><font color="#663300"><b>' || NVL(TO_CHAR(last_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</b></font></div>')  last_usage_date
  , DECODE(  detected_usages
  , 0
  , '<div align="right">' || NVL(TO_CHAR(last_sample_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'
  , '<div align="right"><font color="#663300"><b>' || NVL(TO_CHAR(last_sample_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</b></font></div>')  last_sample_date
  , DECODE(  detected_usages
  , 0
  , '<div align="right">' || NVL(TO_CHAR((last_sample_date+SAMPLE_INTERVAL/60/60/24), 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'
  , '<div align="right"><font color="#663300"><b>' || NVL(TO_CHAR((last_sample_date+SAMPLE_INTERVAL/60/60/24), 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</b></font></div>')  next_sample_date
FROM dba_feature_usage_statistics
ORDER BY name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- HIGH WATER MARK STATISTICS -
 
prompt <a name="high_water_mark_statistics"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>High Water Mark Statistics</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN statistic_name  FORMAT a115  HEADING 'Statistic Name'
COLUMN version  FORMAT a62  HEADING 'Version'
COLUMN highwater  FORMAT 9,999,999,999,999,999  HEADING 'Highwater'
COLUMN last_value  FORMAT 9,999,999,999,999,999  HEADING 'Last Value'
COLUMN description  FORMAT a120  HEADING 'Description'
 
SELECT
  '<div align="left"><font color="#336699"><b>' || name || '</b></font></div>'  statistic_name
  , '<div align="right">' || version || '</div>'  version
  , highwater  highwater
  , last_value  last_value
  , description  description
FROM dba_high_water_mark_statistics
ORDER BY name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- INSTANCE OVERVIEW -
 
prompt <a name="instance_overview"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Instance Overview</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name_print  FORMAT a75  HEADING 'Instance|Name'  ENTMAP off
COLUMN instance_number_print  FORMAT a75  HEADING 'Instance|Num'  ENTMAP off
COLUMN thread_number_print  HEADING 'Thread|Num'  ENTMAP off
COLUMN host_name_print  FORMAT a75  HEADING 'Host|Name'  ENTMAP off
COLUMN version  HEADING 'Oracle|Version'  ENTMAP off
COLUMN start_time  FORMAT a75  HEADING 'Start|Time'  ENTMAP off
COLUMN uptime  HEADING 'Uptime|(in days)'  ENTMAP off
COLUMN parallel  FORMAT a75  HEADING 'Parallel - (RAC)'  ENTMAP off
COLUMN instance_status  FORMAT a75  HEADING 'Instance|Status'  ENTMAP off
COLUMN database_status  FORMAT a75  HEADING 'Database|Status'  ENTMAP off
COLUMN logins  FORMAT a75  HEADING 'Logins'  ENTMAP off
COLUMN archiver  FORMAT a75  HEADING 'Archiver'  ENTMAP off
 
SELECT
  '<div align="center"><font color="#336699"><b>' || instance_name || '</b></font></div>'  instance_name_print
  , '<div align="center">' || instance_number || '</div>'  instance_number_print
  , '<div align="center">' || thread#  || '</div>'  thread_number_print
  , '<div align="center">' || host_name  || '</div>'  host_name_print
  , '<div align="center">' || version  || '</div>'  version
  , '<div align="center">' || TO_CHAR(startup_time,'mm/dd/yyyy HH24:MI:SS') || '</div>'  start_time
  , ROUND(TO_CHAR(SYSDATE-startup_time), 2)  uptime
  , '<div align="center">' || parallel  || '</div>'  parallel
  , '<div align="center">' || status  || '</div>'  instance_status
  , '<div align="center">' || logins  || '</div>'  logins
  , DECODE(  archiver
  , 'FAILED'
  , '<div align="center"><b><font color="#990000">'  || archiver || '</font></b></div>'
  , '<div align="center"><b><font color="darkgreen">' || archiver || '</font></b></div>') archiver
FROM gv$instance
ORDER BY instance_number;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- DATABASE OVERVIEW -
 
prompt <a name="database_overview"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Database Overview</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN name  FORMAT a75  HEADING 'Database|Name'  ENTMAP off
COLUMN dbid  HEADING 'Database|ID'  ENTMAP off
COLUMN db_unique_name  HEADING 'Database|Unique Name'  ENTMAP off
COLUMN creation_date  HEADING 'Creation|Date'  ENTMAP off
COLUMN platform_name_print  HEADING 'Platform|Name'  ENTMAP off
COLUMN current_scn  HEADING 'Current|SCN'  ENTMAP off
COLUMN log_mode  HEADING 'Log|Mode'  ENTMAP off
COLUMN open_mode  HEADING 'Open|Mode'  ENTMAP off
COLUMN force_logging  HEADING 'Force|Logging'  ENTMAP off
COLUMN flashback_on  HEADING 'Flashback|On?'  ENTMAP off
COLUMN controlfile_type  HEADING 'Controlfile|Type'  ENTMAP off
COLUMN last_open_incarnation_number  HEADING 'Last Open|Incarnation Num'  ENTMAP off
 
SELECT
  '<div align="center"><font color="#336699"><b>'  || name  || '</b></font></div>'  name
  , '<div align="center">' || dbid  || '</div>'  dbid
  , '<div align="center">' || db_unique_name  || '</div>'  db_unique_name
  , '<div align="center">' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '</div>'  creation_date
  , '<div align="center">' || platform_name  || '</div>'  platform_name_print
  , '<div align="center">' || current_scn  || '</div>'  current_scn
  , '<div align="center">' || log_mode  || '</div>'  log_mode
  , '<div align="center">' || open_mode  || '</div>'  open_mode
  , '<div align="center">' || force_logging  || '</div>'  force_logging
  , '<div align="center">' || flashback_on  || '</div>'  flashback_on
  , '<div align="center">' || controlfile_type  || '</div>'  controlfile_type
  , '<div align="center">' || last_open_incarnation# || '</div>'  last_open_incarnation_number
FROM v$database;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- INITIALIZATION PARAMETERS -
 
prompt <a name="initialization_parameters"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Initialization Parameters</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN spfile  HEADING 'SPFILE Usage'
 
SELECT
  'This database '||
  DECODE(  (1-SIGN(1-SIGN(count(*) - 0)))
  , 1
  , '<font color="#663300"><b>IS</b></font>'
  , '<font color="#990000"><b>IS NOT</b></font>') ||
  ' using an SPFILE.'spfile
FROM v$spparameter
WHERE value IS NOT null;
 
COLUMN pname  FORMAT a75  HEADING 'Parameter Name'  ENTMAP off
COLUMN instance_name_print  FORMAT a45  HEADING 'Instance Name'  ENTMAP off
COLUMN value  FORMAT a75  HEADING 'Value'  ENTMAP off
COLUMN isdefault  FORMAT a75  HEADING 'Is Default?'  ENTMAP off
COLUMN issys_modifiable  FORMAT a75  HEADING 'Is Dynamic?'  ENTMAP off
 
BREAK ON report ON pname
 
SELECT
  DECODE(  p.isdefault
  , 'FALSE'
  , '<b><font color="#336699">' || SUBSTR(p.name,0,512) || '</font></b>'
  , '<b><font color="#336699">' || SUBSTR(p.name,0,512) || '</font></b>' )  pname
  , DECODE(  p.isdefault
  , 'FALSE'
  , '<font color="#663300"><b>' || i.instance_name || '</b></font>'
  , i.instance_name )  instance_name_print
  , DECODE(  p.isdefault
  , 'FALSE'
  , '<font color="#663300"><b>' || SUBSTR(p.value,0,512) || '</b></font>'
  , SUBSTR(p.value,0,512) ) value
  , DECODE(  p.isdefault
  , 'FALSE'
  , '<div align="center"><font color="#663300"><b>' || p.isdefault || '</b></font></div>'
  , '<div align="center">'  || p.isdefault || '</div>')  isdefault
  , DECODE(  p.isdefault
  , 'FALSE'
  , '<div align="right"><font color="#663300"><b>' || p.issys_modifiable || '</b></font></div>'
  , '<div align="right">'  || p.issys_modifiable || '</div>')  issys_modifiable
FROM
  gv$parameter p
  , gv$instance  i
WHERE
  p.inst_id = i.inst_id
ORDER BY
  p.name
  , i.instance_name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- CONTROL FILES -
 
prompt <a name="control_files"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Control Files</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN name  HEADING 'Controlfile Name'  ENTMAP off
COLUMN status  FORMAT a75  HEADING 'Status'  ENTMAP off
COLUMN file_size  FORMAT a75  HEADING 'File Size'  ENTMAP off
 
SELECT
  '<tt>' || c.name || '</tt>'  name
  , DECODE(  c.status
  , NULL
  ,  '<div align="center"><b><font color="darkgreen">VALID</font></b></div>'
  ,  '<div align="center"><b><font color="#663300">'  || c.status || '</font></b></div>') status
  , '<div align="right">' || TO_CHAR(block_size *  file_size_blks, '999,999,999,999') || '</div>'  file_size
FROM 
  v$controlfile c
ORDER BY
  c.name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- CONTROL FILE RECORDS -
 
prompt <a name="control_file_records"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Control File Records</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN type  FORMAT  a95  HEADING 'Record Section Type'  ENTMAP off
COLUMN record_size  FORMAT  999,999  HEADING 'Record Size|(in bytes)'  ENTMAP off
COLUMN records_total  FORMAT  999,999  HEADING 'Records Allocated'  ENTMAP off
COLUMN bytes_alloc  FORMAT  999,999,999  HEADING 'Bytes Allocated'  ENTMAP off
COLUMN records_used  FORMAT  999,999  HEADING 'Records Used'  ENTMAP off
COLUMN bytes_used  FORMAT  999,999,999  HEADING 'Bytes Used'  ENTMAP off
COLUMN pct_used  FORMAT  B999  HEADING '% Used'  ENTMAP off
COLUMN first_index  HEADING 'First Index'  ENTMAP off
COLUMN last_index  HEADING 'Last Index'  ENTMAP off
COLUMN last_recid  HEADING 'Last RecID'  ENTMAP off
 
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>'  of record_size records_total bytes_alloc records_used bytes_used ON report
COMPUTE avg LABEL '<font color="#990000"><b>Average: </b></font>' of pct_used  ON report
 
SELECT
  '<div align="left"><font color="#336699"><b>' || type || '</b></font></div>'  type
  , record_size  record_size
  , records_total  records_total
  , (records_total * record_size)  bytes_alloc
  , records_used  records_used
  , (records_used * record_size)  bytes_used
  , NVL(records_used/records_total * 100, 0)  pct_used
  , first_index  first_index
  , last_index  last_index
  , last_recid  last_recid
FROM v$controlfile_record_section
ORDER BY type;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- ONLINE REDO LOGS -
 
prompt <a name="online_redo_logs"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Online Redo Logs</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name_print  FORMAT a95  HEADING 'Instance Name'  ENTMAP off
COLUMN thread_number_print  FORMAT a95  HEADING 'Thread Number'  ENTMAP off
COLUMN groupno  HEADING 'Group Number'  ENTMAP off
COLUMN member  HEADING 'Member'  ENTMAP off
COLUMN redo_file_type  FORMAT a75  HEADING 'Redo Type'  ENTMAP off
COLUMN log_status  FORMAT a75  HEADING 'Log Status'  ENTMAP off
COLUMN bytes  FORMAT 999,999,999,999  HEADING 'Bytes'  ENTMAP off
COLUMN archived  FORMAT a75  HEADING 'Archived?'  ENTMAP off
 
BREAK ON report ON instance_name_print ON thread_number_print
 
SELECT
  '<div align="center"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name_print
  , '<div align="center">' || i.thread# || '</div>'  thread_number_print
  , f.group#  groupno
  , '<tt>' || f.member || '</tt>'  member
  , f.type  redo_file_type
  , DECODE(  l.status
  , 'CURRENT'
  , '<div align="center"><b><font color="darkgreen">' || l.status || '</font></b></div>'
  , '<div align="center"><b><font color="#990000">'  || l.status || '</font></b></div>')  log_status
  , l.bytes  bytes
  , '<div align="center">'  || l.archived || '</div>'  archived
FROM
  gv$logfile  f
  , gv$log  l
  , gv$instance i
WHERE
  f.group#  = l.group#
  AND l.thread# = i.thread#
  AND i.inst_id = f.inst_id
  AND f.inst_id = l.inst_id
ORDER BY
  i.instance_name
  , f.group#
  , f.member;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- REDO LOG SWITCHES -
 
prompt <a name="redo_log_switches"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Redo Log Switches</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN DAY  FORMAT a75  HEADING 'Day / Time'  ENTMAP off
COLUMN H00  FORMAT 999,999B  HEADING '00'  ENTMAP off
COLUMN H01  FORMAT 999,999B  HEADING '01'  ENTMAP off
COLUMN H02  FORMAT 999,999B  HEADING '02'  ENTMAP off
COLUMN H03  FORMAT 999,999B  HEADING '03'  ENTMAP off
COLUMN H04  FORMAT 999,999B  HEADING '04'  ENTMAP off
COLUMN H05  FORMAT 999,999B  HEADING '05'  ENTMAP off
COLUMN H06  FORMAT 999,999B  HEADING '06'  ENTMAP off
COLUMN H07  FORMAT 999,999B  HEADING '07'  ENTMAP off
COLUMN H08  FORMAT 999,999B  HEADING '08'  ENTMAP off
COLUMN H09  FORMAT 999,999B  HEADING '09'  ENTMAP off
COLUMN H10  FORMAT 999,999B  HEADING '10'  ENTMAP off
COLUMN H11  FORMAT 999,999B  HEADING '11'  ENTMAP off
COLUMN H12  FORMAT 999,999B  HEADING '12'  ENTMAP off
COLUMN H13  FORMAT 999,999B  HEADING '13'  ENTMAP off
COLUMN H14  FORMAT 999,999B  HEADING '14'  ENTMAP off
COLUMN H15  FORMAT 999,999B  HEADING '15'  ENTMAP off
COLUMN H16  FORMAT 999,999B  HEADING '16'  ENTMAP off
COLUMN H17  FORMAT 999,999B  HEADING '17'  ENTMAP off
COLUMN H18  FORMAT 999,999B  HEADING '18'  ENTMAP off
COLUMN H19  FORMAT 999,999B  HEADING '19'  ENTMAP off
COLUMN H20  FORMAT 999,999B  HEADING '20'  ENTMAP off
COLUMN H21  FORMAT 999,999B  HEADING '21'  ENTMAP off
COLUMN H22  FORMAT 999,999B  HEADING '22'  ENTMAP off
COLUMN H23  FORMAT 999,999B  HEADING '23'  ENTMAP off
COLUMN TOTAL FORMAT 999,999,999  HEADING 'Total'  ENTMAP off
 
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total:</b></font>' avg label '<font color="#990000"><b>Average:</b></font>' OF total ON report
 
SELECT
  '<div align="center"><font color="#336699"><b>' || SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  || '</b></font></div>'  DAY
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)  TOTAL
FROM
  v$log_history  a
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
/
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- OUTSTANDING ALERTS -
 
prompt <a name="outstanding_alerts"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Outstanding Alerts</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN severity  FORMAT a75  HEADING 'Severity'  ENTMAP off
COLUMN target_name  FORMAT a75  HEADING 'Target Name'  ENTMAP off
COLUMN target_type  FORMAT a75  HEADING 'Target Type'  ENTMAP off
COLUMN category  FORMAT a75  HEADING 'Category'  ENTMAP off
COLUMN name  FORMAT a75  HEADING 'Name'  ENTMAP off
COLUMN message  FORMAT a125  HEADING 'Message'  ENTMAP off
COLUMN alert_triggered  FORMAT a75  HEADING 'Alert Triggered' ENTMAP off
 
SELECT
  DECODE(  alert_state
  , 'Critical'
  , '<div align="center"><b><font color="#990000">' || alert_state || '</font></b></div>'
  , '<div align="center"><b><font color="#336699">' || alert_state || '</font></b></div>')  severity
  , target_name  target_name
  , (CASE target_type
  WHEN 'oracle_listener' THEN 'Oracle Listener'
  WHEN 'rac_database'  THEN 'Cluster Database'
  WHEN 'cluster'  THEN 'Clusterware'
  WHEN 'host'  THEN 'Host'
  WHEN 'osm_instance'  THEN 'OSM Instance'
  WHEN 'oracle_database' THEN 'Database Instance'
  WHEN 'oracle_emd'  THEN 'Oracle EMD'
  WHEN 'oracle_emrep'  THEN 'Oracle EMREP'
  ELSE
  target_type
  END)  target_type
  , metric_label  category
  , column_label  name
  , message  message
  , '<div nowrap align="right">' || TO_CHAR(collection_timestamp, 'mm/dd/yyyy HH24:MI:SS') || '</div>'  alert_triggered
FROM
  mgmt$alert_current
ORDER BY
  alert_state
  , collection_timestamp;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- STATISTICS LEVEL -
 
prompt <a name="statistics_level"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Statistics Level</b></font><hr align="left" width="460">
 
prompt "Automatic Database Management" was first introduced in Oracle10<i>g</i> where the Oracle database
prompt can now automatically perform many of the routine monitoring and administrative activities that had
prompt to be manually executed by the DBA in previous versions. Several of the new components that make
prompt up this new feature include (1) Automatic Workload Repository (2) Automatic Database Diagnostic
prompt Monitoring (3) Automatic Shared Memory Management and (4) Automatic UNDO Retention Tuning. All
prompt of these new components can only be enabled when the STATISTICS_LEVEL initialization parameter
prompt is set to TYPICAL (the default) or ALL. A value of BASIC turns off these components and disables
prompt all self-tuning capabilities of the database. The view V$STATISTICS_LEVEL shows the statistic 
prompt component, description, and at what level of the STATISTICS_LEVEL parameter the
prompt component is enabled.
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name_print  FORMAT a95  HEADING 'Instance Name'  ENTMAP off
COLUMN statistics_name  FORMAT a95  HEADING 'Statistics Name'  ENTMAP off
COLUMN session_status  FORMAT a95  HEADING 'Session Status'  ENTMAP off
COLUMN system_status  FORMAT a95  HEADING 'System Status'  ENTMAP off
COLUMN activation_level  FORMAT a95  HEADING 'Activation Level'  ENTMAP off
COLUMN statistics_view_name  FORMAT a95  HEADING 'Statistics View Name'  ENTMAP off
COLUMN session_settable  FORMAT a95  HEADING 'Session Settable?'  ENTMAP off
 
BREAK ON report ON instance_name_print
 
SELECT
  '<div align="center"><font color="#336699"><b>' || i.instance_name  || '</b></font></div>'  instance_name_print
  , '<div align="left" nowrap>'  || s.statistics_name  || '</div>'  statistics_name
  , DECODE(  s.session_status
  , 'ENABLED'
  , '<div align="center"><b><font color="darkgreen">' || s.session_status || '</font></b></div>'
  , '<div align="center"><b><font color="#990000">'  || s.session_status || '</font></b></div>')  session_status
  , DECODE(  s.system_status
  , 'ENABLED'
  , '<div align="center"><b><font color="darkgreen">' || s.system_status || '</font></b></div>'
  , '<div align="center"><b><font color="#990000">'  || s.system_status || '</font></b></div>')  system_status
  , (CASE s.activation_level
  WHEN 'TYPICAL' THEN '<div align="center"><b><font color="darkgreen">' || s.activation_level || '</font></b></div>'
  WHEN 'ALL'  THEN '<div align="center"><b><font color="darkblue">'  || s.activation_level || '</font></b></div>'
  WHEN 'BASIC'  THEN '<div align="center"><b><font color="#990000">'  || s.activation_level || '</font></b></div>'
  ELSE
  '<div align="center"><b><font color="#663300">'  || s.activation_level || '</font></b></div>'
  END)  activation_level
  , s.statistics_view_name  statistics_view_name
  , '<div align="center">' || s.session_settable || '</div>'  session_settable
FROM
  gv$statistics_level s
  , gv$instance  i
WHERE
  s.inst_id = i.inst_id
ORDER BY
  i.instance_name
  , s.statistics_name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- +============================================================================+
-- ||
-- |<<<<<  SCHEDULER / JOBS  >>>>>
-- ||
-- +============================================================================+
 
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Scheduler / Jobs</u></b></font></center>
 
-- |- JOBS -
 
prompt <a name="jobs"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Jobs</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN job_id  FORMAT a75  HEADING 'Job ID'  ENTMAP off
COLUMN username  FORMAT a75  HEADING 'User'  ENTMAP off
COLUMN what  FORMAT a175  HEADING 'What'  ENTMAP off
COLUMN next_date  FORMAT a110  HEADING 'Next Run Date'  ENTMAP off
COLUMN interval  FORMAT a75  HEADING 'Interval'  ENTMAP off
COLUMN last_date  FORMAT a110  HEADING 'Last Run Date'  ENTMAP off
COLUMN failures  FORMAT a75  HEADING 'Failures'  ENTMAP off
COLUMN broken  FORMAT a75  HEADING 'Broken?'  ENTMAP off
 
SELECT
  DECODE(  broken
  , 'Y'
  , '<b><font color="#990000"><div align="center">' || job || '</div></font></b>'
  , '<b><font color="#336699"><div align="center">' || job || '</div></font></b>')  job_id
  , DECODE(  broken
  , 'Y'
  , '<b><font color="#990000">' || log_user || '</font></b>'
  , log_user )  username
  , DECODE(  broken
  , 'Y'
  , '<b><font color="#990000">' || what || '</font></b>'
  , what )  what
  , DECODE(  broken
  , 'Y'
  , '<div nowrap align="right"><b><font color="#990000">' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</font></b></div>'
  , '<div nowrap align="right">'  || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>')  next_date  
  , DECODE(  broken
  , 'Y'
  , '<b><font color="#990000">' || interval || '</font></b>'
  , interval )  interval
  , DECODE(  broken
  , 'Y'
  , '<div nowrap align="right"><b><font color="#990000">' || NVL(TO_CHAR(last_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</font></b></div>'
  , '<div nowrap align="right">'  || NVL(TO_CHAR(last_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>')  last_date  
  , DECODE(  broken
  , 'Y'
  , '<b><font color="#990000"><div align="center">' || NVL(failures, 0) || '</div></font></b>'
  , '<div align="center">'  || NVL(failures, 0) || '</div>')  failures
  , DECODE(  broken
  , 'Y'
  , '<b><font color="#990000"><div align="center">' || broken || '</div></font></b>'
  , '<div align="center">'  || broken || '</div>')  broken
FROM
  dba_jobs
ORDER BY
  job;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- +============================================================================+
-- ||
-- |<<<<<  STORAGE  >>>>>
-- ||
-- +============================================================================+
 
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Storage</u></b></font></center>
 
-- |- TABLESPACES -
 
prompt <a name="tablespaces"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespaces</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN status  HEADING 'Status'  ENTMAP off
COLUMN name  HEADING 'Tablespace Name'  ENTMAP off
COLUMN type  FORMAT a12  HEADING 'TS Type'  ENTMAP off
COLUMN extent_mgt  FORMAT a10  HEADING 'Ext. Mgt.'  ENTMAP off
COLUMN segment_mgt FORMAT a9  HEADING 'Seg. Mgt.'  ENTMAP off
COLUMN ts_size  FORMAT 999,999,999,999,999  HEADING 'Tablespace Size'  ENTMAP off
COLUMN free  FORMAT 999,999,999,999,999  HEADING 'Free (in bytes)'  ENTMAP off
COLUMN used  FORMAT 999,999,999,999,999  HEADING 'Used (in bytes)'  ENTMAP off
COLUMN pct_used  HEADING 'Pct. Used'  ENTMAP off
 
BREAK ON report
COMPUTE SUM label '<font color="#990000"><b>Total:</b></font>'  OF ts_size used free ON report
 
SELECT
  DECODE(  d.status
  , 'OFFLINE'
  , '<div align="center"><b><font color="#990000">'  || d.status || '</font></b></div>'
  , '<div align="center"><b><font color="darkgreen">' || d.status || '</font></b></div>') status
  , '<b><font color="#336699">' || d.tablespace_name || '</font></b>'  name
  , d.contents  type
  , d.extent_management  extent_mgt
  , d.segment_space_management  segment_mgt
  , NVL(a.bytes, 0)  ts_size
  , NVL(f.bytes, 0)  free
  , NVL(a.bytes - NVL(f.bytes, 0), 0)  used
  , '<div align="right"><b>' || 
  DECODE (
  (1-SIGN(1-SIGN(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) - 90)))
  , 1
  , '<font color="#990000">'  || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || '</font>'
  , '<font color="darkgreen">' || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || '</font>'
  )
  || '</b> %</div>' pct_used
FROM 
  sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
  from dba_data_files
  group by tablespace_name
  ) a
  , ( select tablespace_name, sum(bytes) bytes
  from dba_free_space
  group by tablespace_name
  ) f
WHERE
  d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND NOT (
  d.extent_management like 'LOCAL'
  AND
  d.contents like 'TEMPORARY'
  )
UNION ALL 
SELECT
  DECODE(  d.status
  , 'OFFLINE'
  , '<div align="center"><b><font color="#990000">'  || d.status || '</font></b></div>'
  , '<div align="center"><b><font color="darkgreen">' || d.status || '</font></b></div>') status
  , '<b><font color="#336699">' || d.tablespace_name  || '</font></b>'  name
  , d.contents  type
  , d.extent_management  extent_mgt
  , d.segment_space_management  segment_mgt
  , NVL(a.bytes, 0)  ts_size
  , NVL(a.bytes - NVL(t.bytes,0), 0)  free
  , NVL(t.bytes, 0)  used
  , '<div align="right"><b>' || 
  DECODE (
  (1-SIGN(1-SIGN(TRUNC(NVL(t.bytes / a.bytes * 100, 0)) - 90)))
  , 1
  , '<font color="#990000">'  || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || '</font>'
  , '<font color="darkgreen">' || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || '</font>'
  )
  || '</b> %</div>' pct_used
FROM
  sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
  from dba_temp_files
  group by tablespace_name
  ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
  from v$temp_extent_pool
  group by tablespace_name
  ) t
WHERE
  d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management like 'LOCAL'
  AND d.contents like 'TEMPORARY'
ORDER BY 2;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- DATA FILES -
 
prompt <a name="data_files"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Data Files</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN tablespace  HEADING 'Tablespace Name / File Class'  ENTMAP off
COLUMN filename  HEADING 'Filename'  ENTMAP off
COLUMN filesize  FORMAT 999,999,999,999,999  HEADING 'File Size'  ENTMAP off
COLUMN autoextensible  HEADING 'Autoextensible'  ENTMAP off
COLUMN increment_by  FORMAT 999,999,999,999,999  HEADING 'Next'  ENTMAP off
COLUMN maxbytes  FORMAT 999,999,999,999,999  HEADING 'Max'  ENTMAP off
 
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' OF filesize ON report
 
SELECT /*+ ordered */
  '<font color="#336699"><b>' || d.tablespace_name  || '</b></font>'  tablespace
  , '<tt>' || d.file_name || '</tt>'  filename
  , d.bytes  filesize
  , '<div align="center">' || NVL(d.autoextensible, '<br>') || '</div>' autoextensible
  , d.increment_by * e.value  increment_by
  , d.maxbytes  maxbytes
FROM
  sys.dba_data_files d
  , v$datafile v
  , (SELECT value
  FROM v$parameter 
  WHERE name = 'db_block_size') e
WHERE
  (d.file_name = v.name)
UNION
SELECT
  '<font color="#336699"><b>' || d.tablespace_name || '</b></font>'  tablespace 
  , '<tt>' || d.file_name  || '</tt>'  filename
  , d.bytes  filesize
  , '<div align="center">' || NVL(d.autoextensible, '<br>') || '</div>' autoextensible
  , d.increment_by * e.value  increment_by
  , d.maxbytes  maxbytes
FROM
  sys.dba_temp_files d
  , (SELECT value
  FROM v$parameter 
  WHERE name = 'db_block_size') e
UNION
SELECT
  '<font color="#336699"><b>[ ONLINE REDO LOG ]</b></font>'
  , '<tt>' || a.member || '</tt>'
  , b.bytes
  , null
  , null
  , null
FROM
  v$logfile a
  , v$log b
WHERE
  a.group# = b.group#
UNION
SELECT
  '<font color="#336699"><b>[ CONTROL FILE  ]</b></font>'
  , '<tt>' || a.name || '</tt>'
  , null
  , null
  , null
  , null
FROM
  v$controlfile a
ORDER BY
  1
  , 2;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- DATABASE GROWTH -
 
prompt <a name="database_growth"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Database Growth</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN month  FORMAT a75  HEADING 'Month'
COLUMN growth  FORMAT 999,999,999,999,999  HEADING 'Growth (bytes)'
 
BREAK ON report
COMPUTE SUM label '<font color="#990000"><b>Total:</b></font>' OF growth ON report
 
SELECT
  '<div align="left"><font color="#336699"><b>' || TO_CHAR(creation_time, 'RRRR-MM') || '</b></font></div>' month
  , SUM(bytes)  growth
FROM  sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- TABLESPACE EXTENTS -
 
prompt <a name="tablespace_extents"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespace Extents</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN tablespace_name  HEADING 'Tablespace Name'  ENTMAP off
COLUMN largest_ext  FORMAT 999,999,999,999,999  HEADING 'Largest Extent'  ENTMAP off
COLUMN smallest_ext  FORMAT 999,999,999,999,999  HEADING 'Smallest Extent'  ENTMAP off
COLUMN total_free  FORMAT 999,999,999,999,999  HEADING 'Total Free'  ENTMAP off
COLUMN pieces  FORMAT 999,999,999,999,999  HEADING 'Number of Free Extents'  ENTMAP off
 
break on report
compute sum label '<font color="#990000"><b>Total:</b></font>' of largest_ext smallest_ext total_free pieces on report
 
SELECT 
  '<b><font color="#336699">' || tablespace_name || '</font></b>' tablespace_name
  , max(bytes)  largest_ext
  , min(bytes)  smallest_ext
  , sum(bytes)  total_free
  , count(*)  pieces
FROM
  dba_free_space
GROUP BY
  tablespace_name
ORDER BY
  tablespace_name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- TABLESPACE TO OWNER  -
 
prompt <a name="tablespace_to_owner"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespace to Owner</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN tablespace_name  FORMAT a75  HEADING 'Tablespace Name'  ENTMAP off
COLUMN owner  FORMAT a75  HEADING 'Owner'  ENTMAP off
COLUMN segment_type  FORMAT a75  HEADING 'Segment Type'  ENTMAP off
COLUMN bytes  FORMAT 999,999,999,999,999  HEADING 'Size (in Bytes)'  ENTMAP off
COLUMN seg_count  FORMAT 999,999,999,999  HEADING 'Segment Count'  ENTMAP off
 
BREAK ON report ON tablespace_name
COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' of seg_count bytes ON report
 
SELECT
  '<font color="#336699"><b>' || tablespace_name || '</b></font>'  tablespace_name
  , '<div align="right">'  || owner  || '</div>'  owner
  , '<div align="right">'  || segment_type  || '</div>'  segment_type
  , sum(bytes)  bytes
  , count(*)  seg_count
FROM
  dba_segments
GROUP BY
  tablespace_name
  , owner
  , segment_type
ORDER BY
  tablespace_name
  , owner
  , segment_type;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- OWNER TO TABLESPACE -
 
prompt <a name="owner_to_tablespace"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Owner to Tablespace</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN owner  FORMAT a75  HEADING 'Owner'  ENTMAP off
COLUMN tablespace_name  FORMAT a75  HEADING 'Tablespace Name'  ENTMAP off
COLUMN segment_type  FORMAT a75  HEADING 'Segment Type'  ENTMAP off
COLUMN bytes  FORMAT 999,999,999,999,999  HEADING 'Size (in Bytes)'  ENTMAP off
COLUMN seg_count  FORMAT 999,999,999,999  HEADING 'Segment Count'  ENTMAP off
 
break on report on owner
compute sum label '<font color="#990000"><b>Total: </b></font>' of seg_count bytes on report
 
SELECT
  '<font color="#336699"><b>'  || owner  || '</b></font>' owner
  , '<div align="right">'  || tablespace_name || '</div>'  tablespace_name
  , '<div align="right">'  || segment_type  || '</div>'  segment_type
  , sum(bytes)  bytes
  , count(*)  seg_count
FROM
  dba_segments
GROUP BY
  owner
  , tablespace_name
  , segment_type
ORDER BY
  owner
  , tablespace_name
  , segment_type;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- +============================================================================+
-- ||
-- |<<<<<  UNDO Segments  >>>>>
-- ||
-- +============================================================================+
 
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>UNDO Segments</u></b></font></center>
 
-- |- UNDO RETENTION PARAMETERS -
 
prompt <a name="undo_retention_parameters"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>UNDO Retention Parameters</b></font><hr align="left" width="460">
 
prompt <b>undo_retention is specified in minutes</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name_print  FORMAT a95  HEADING 'Instance Name'  ENTMAP off
COLUMN thread_number_print  FORMAT a95  HEADING 'Thread Number'  ENTMAP off
COLUMN name  FORMAT a125  HEADING 'Name'  ENTMAP off
COLUMN value  HEADING 'Value'  ENTMAP off
 
BREAK ON report ON instance_name_print ON thread_number_print
 
SELECT
  '<div align="center"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name_print
  , '<div align="center">'  || i.thread#  || '</div>'  thread_number_print
  , '<div nowrap>'  || p.name  || '</div>'  name
  , (CASE p.name
  WHEN 'undo_retention' THEN '<div nowrap align="right">' || TO_CHAR(TO_NUMBER(p.value)/60, '999,999,999,999,999') || '</div>'
  ELSE
  '<div nowrap align="right">' || p.value || '</div>'
  END)  value
FROM
  gv$parameter p
  , gv$instance  i
WHERE
  p.inst_id = i.inst_id
  AND p.name LIKE 'undo%'
ORDER BY
  i.instance_name
  , p.name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- UNDO SEGMENTS -
 
prompt <a name="undo_segments"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>UNDO Segments</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name FORMAT a75  HEADING 'Instance Name'  ENTMAP off
COLUMN tablespace  FORMAT a85  HEADING 'Tablspace'  ENTMAP off
COLUMN roll_name  HEADING 'UNDO Segment Name'  ENTMAP off
COLUMN in_extents  HEADING 'Init/Next Extents'  ENTMAP off
COLUMN m_extents  HEADING 'Min/Max Extents'  ENTMAP off
COLUMN status  HEADING 'Status'  ENTMAP off
COLUMN wraps  FORMAT 999,999,999  HEADING 'Wraps'  ENTMAP off
COLUMN shrinks  FORMAT 999,999,999  HEADING 'Shrinks'  ENTMAP off
COLUMN opt  FORMAT 999,999,999,999  HEADING 'Opt. Size'  ENTMAP off
COLUMN bytes  FORMAT 999,999,999,999  HEADING 'Bytes'  ENTMAP off
COLUMN extents  FORMAT 999,999,999  HEADING 'Extents'  ENTMAP off
 
CLEAR COMPUTES BREAKS
 
BREAK ON report ON instance_name ON tablespace
-- COMPUTE sum LABEL '<font color="#990000"><b>Total:</b></font>' OF bytes extents shrinks wraps ON report
 
SELECT
  '<div nowrap><font color="#336699"><b>' ||  NVL(i.instance_name, '<br>')  || '</b></font></div>'  instance_name
  , '<div nowrap><font color="#336699"><b>' ||  a.tablespace_name  || '</b></font></div>'  tablespace
  , '<div nowrap>'  ||  a.owner || '.' || a.segment_name || '</div>'  roll_name
  , '<div align="right">'  ||
  TO_CHAR(a.initial_extent) || ' / ' ||
  TO_CHAR(a.next_extent)  ||
  '</div>'  in_extents
  , '<div align="right">'  ||
  TO_CHAR(a.min_extents)  || ' / ' ||
  TO_CHAR(a.max_extents)  ||
  '</div>'  m_extents
  , DECODE(  a.status
  , 'OFFLINE'
  , '<div align="center"><b><font color="#990000">'  || a.status || '</font></b></div>'
  , '<div align="center"><b><font color="darkgreen">' || a.status || '</font></b></div>') status
  , b.bytes  bytes
  , b.extents  extents
  , d.shrinks  shrinks
  , d.wraps  wraps
  , d.optsize  opt
FROM
  dba_rollback_segs a
  , dba_segments b
  , v$rollname c
  , v$rollstat d
  , gv$parameter p
  , gv$instance  i
WHERE
  a.segment_name  = b.segment_name
  AND  a.segment_name  = c.name (+)
  AND  c.usn  = d.usn (+)
  AND  p.name (+)  = 'undo_tablespace'
  AND  p.value (+)  = a.tablespace_name
  AND  p.inst_id  = i.inst_id (+)
ORDER BY
  a.tablespace_name
  , a.segment_name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- UNDO SEGMENT CONTENTION -
 
prompt <a name="undo_segment_contention"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>UNDO Segment Contention</b></font><hr align="left" width="460">
 
prompt <b>UNDO statistics from V$ROLLSTAT - (ordered by waits)</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN roll_name  HEADING 'UNDO Segment Name'  ENTMAP off
COLUMN gets  FORMAT 999,999,999  HEADING 'Gets'  ENTMAP off
COLUMN waits  FORMAT 999,999,999  HEADING 'Waits'  ENTMAP off
COLUMN immediate_misses FORMAT 999,999,999  HEADING 'Immediate Misses'  ENTMAP off
COLUMN hit_ratio  HEADING 'Hit Ratio'  ENTMAP off
 
BREAK ON report
COMPUTE SUM label '<font color="#990000"><b>Total:</b></font>' OF gets waits ON report
 
SELECT
  '<font color="#336699"><b>' || b.name || '</b></font>'  roll_name
  , gets  gets
  , waits  waits
  , '<div align="right">' || TO_CHAR(ROUND(((gets - waits)*100)/gets, 2)) || '%</div>' hit_ratio
FROM 
  sys.v_$rollstat a
  , sys.v_$rollname b
WHERE
  a.USN = b.USN
ORDER BY
  waits DESC;
 
prompt 
prompt <b>Wait statistics</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN class  HEADING 'Class'  
COLUMN ratio  HEADING 'Wait Ratio'  
 
SELECT
  '<font color="#336699"><b>' || w.class || '</b></font>'  class
  , '<div align="right">' || TO_CHAR(ROUND(100*(w.count/SUM(s.value)),8)) || '%</div>' ratio
FROM
  v$waitstat  w
  , v$sysstat  s
WHERE
  w.class IN (  'system undo header'
  , 'system undo block'
  , 'undo header'
  , 'undo block'
  )
  AND s.name IN ('db block gets', 'consistent gets')
GROUP BY
  w.class
  , w.count;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- +============================================================================+
-- ||
-- |<<<<<  BACKUPS  >>>>>
-- ||
-- +============================================================================+
 
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Backups</u></b></font></center>
 
-- |- RMAN BACKUP JOBS -
 
prompt <a name="rman_backup_jobs"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup Jobs</b></font><hr align="left" width="460">
 
prompt <b>Last 10 RMAN backup jobs</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN backup_name  FORMAT a130  HEADING 'Backup Name'  ENTMAP off
COLUMN start_time  FORMAT a75  HEADING 'Start Time'  ENTMAP off
COLUMN elapsed_time  FORMAT a75  HEADING 'Elapsed Time'  ENTMAP off
COLUMN status  HEADING 'Status'  ENTMAP off
COLUMN input_type  HEADING 'Input Type'  ENTMAP off
COLUMN output_device_type  HEADING 'Output Devices'  ENTMAP off
COLUMN input_size  HEADING 'Input Size'  ENTMAP off
COLUMN output_size  HEADING 'Output Size'  ENTMAP off
COLUMN output_rate_per_sec  HEADING 'Output Rate Per Sec'  ENTMAP off
 
SELECT
  '<div nowrap><b><font color="#336699">' || r.command_id  || '</font></b></div>'  backup_name
  , '<div nowrap align="right">'  || TO_CHAR(r.start_time, 'mm/dd/yyyy HH24:MI:SS') || '</div>'  start_time
  , '<div nowrap align="right">'  || r.time_taken_display  || '</div>'  elapsed_time
  , DECODE(  r.status
  , 'COMPLETED'
  , '<div align="center"><b><font color="darkgreen">' || r.status || '</font></b></div>'
  , 'RUNNING'
  , '<div align="center"><b><font color="#000099">'  || r.status || '</font></b></div>'
  , 'FAILED'
  , '<div align="center"><b><font color="#990000">'  || r.status || '</font></b></div>'
  , '<div align="center"><b><font color="#663300">'  || r.status || '</font></b></div>'
  )  status
  , r.input_type  input_type
  , r.output_device_type  output_device_type
  , '<div nowrap align="right">' || r.input_bytes_display  || '</div>'  input_size
  , '<div nowrap align="right">' || r.output_bytes_display  || '</div>'  output_size
  , '<div nowrap align="right">' || r.output_bytes_per_sec_display  || '</div>'  output_rate_per_sec
FROM
  (select
  command_id
  , start_time
  , time_taken_display
  , status
  , input_type
  , output_device_type
  , input_bytes_display
  , output_bytes_display
  , output_bytes_per_sec_display
  from v$rman_backup_job_details
  order by start_time DESC
  ) r
WHERE
  rownum < 11; 
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- RMAN CONFIGURATION -
 
prompt <a name="rman_configuration"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Configuration</b></font><hr align="left" width="460">
 
prompt <b>All non-default RMAN configuration settings</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN name  FORMAT a130  HEADING 'Name'  ENTMAP off
COLUMN value  HEADING 'Value'  ENTMAP off
 
SELECT
  '<div nowrap><b><font color="#336699">' || name || '</font></b></div>'  name
  , value
FROM
  v$rman_configuration
ORDER BY
  name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- RMAN BACKUP SETS -
 
prompt <a name="rman_backup_sets"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup Sets</b></font><hr align="left" width="460">
 
prompt <b>Available backup sets contained in the control file including available and expired backup sets</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN bs_key  FORMAT a75  HEADING 'BS Key'  ENTMAP off
COLUMN backup_type  FORMAT a70  HEADING 'Backup Type'  ENTMAP off
COLUMN device_type  HEADING 'Device Type'  ENTMAP off
COLUMN controlfile_included  FORMAT a30  HEADING 'Controlfile Included?'  ENTMAP off
COLUMN spfile_included  FORMAT a30  HEADING 'SPFILE Included?'  ENTMAP off
COLUMN incremental_level  HEADING 'Incremental Level'  ENTMAP off
COLUMN pieces  FORMAT 999,999,999,999  HEADING '# of Pieces'  ENTMAP off
COLUMN start_time  FORMAT a75  HEADING 'Start Time'  ENTMAP off
COLUMN completion_time  FORMAT a75  HEADING 'End Time'  ENTMAP off
COLUMN elapsed_seconds  FORMAT 999,999,999,999,999  HEADING 'Elapsed Seconds'  ENTMAP off
COLUMN tag  HEADING 'Tag'  ENTMAP off
COLUMN block_size  FORMAT 999,999,999,999,999  HEADING 'Block Size'  ENTMAP off
COLUMN keep  FORMAT a40  HEADING 'Keep?'  ENTMAP off
COLUMN keep_until  FORMAT a75  HEADING 'Keep Until'  ENTMAP off
COLUMN keep_options  FORMAT a15  HEADING 'Keep Options'  ENTMAP off
 
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total:</b></font>' OF pieces elapsed_seconds ON report
 
SELECT
  '<div align="center"><font color="#336699"><b>' || bs.recid || '</b></font></div>'  bs_key
  , DECODE(backup_type
  , 'L', '<div nowrap><font color="#990000">Archived Redo Logs</font></div>'
  , 'D', '<div nowrap><font color="#000099">Datafile Full Backup</font></div>'
  , 'I', '<div nowrap><font color="darkgreen">Incremental Backup</font></div>')  backup_type
  , '<div nowrap align="right">' || device_type || '</div>'  device_type
  , '<div align="center">' ||
  DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included) || '</div>'  controlfile_included
  , '<div align="center">' || NVL(sp.spfile_included, '-') || '</div>'  spfile_included
  , bs.incremental_level  incremental_level
  , bs.pieces  pieces
  , '<div nowrap align="right">' || TO_CHAR(bs.start_time, 'mm/dd/yyyy HH24:MI:SS')  || '</div>'  start_time
  , '<div nowrap align="right">' || TO_CHAR(bs.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '</div>'  completion_time
  , bs.elapsed_seconds  elapsed_seconds
  , bp.tag  tag
  , bs.block_size  block_size
  , '<div align="center">' || bs.keep || '</div>'  keep
  , '<div nowrap align="right">' || NVL(TO_CHAR(bs.keep_until, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'  keep_until
  , bs.keep_options  keep_options
FROM
  v$backup_set  bs
  , (select distinct
  set_stamp
  , set_count
  , tag
  , device_type
  from v$backup_piece
  where status in ('A', 'X'))  bp
 ,  (select distinct set_stamp, set_count, 'YES' spfile_included
  from v$backup_spfile)  sp
WHERE
  bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bs.set_stamp = sp.set_stamp (+)
  AND bs.set_count = sp.set_count (+)
ORDER BY
  bs.recid;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- RMAN BACKUP PIECES -
 
prompt <a name="rman_backup_pieces"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup Pieces</b></font><hr align="left" width="460">
 
prompt <b>Available backup pieces contained in the control file including available and expired backup sets</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN bs_key  FORMAT a75  HEADING 'BS Key'  ENTMAP off
COLUMN piece#  HEADING 'Piece #'  ENTMAP off
COLUMN copy#  HEADING 'Copy #'  ENTMAP off
COLUMN bp_key  HEADING 'BP Key'  ENTMAP off
COLUMN status  HEADING 'Status'  ENTMAP off
COLUMN handle  HEADING 'Handle'  ENTMAP off
COLUMN start_time  FORMAT a75  HEADING 'Start Time'  ENTMAP off
COLUMN completion_time  FORMAT a75  HEADING 'End Time'  ENTMAP off
COLUMN elapsed_seconds  FORMAT 999,999,999,999,999  HEADING 'Elapsed Seconds'  ENTMAP off
COLUMN deleted  FORMAT a10  HEADING 'Deleted?'  ENTMAP off
 
BREAK ON bs_key
 
SELECT
  '<div align="center"><font color="#336699"><b>' || bs.recid  || '</b></font></div>'  bs_key
  , bp.piece#  piece#
  , bp.copy#  copy#
  , bp.recid  bp_key
  , DECODE(  status
  , 'A', '<div nowrap align="center"><font color="darkgreen"><b>Available</b></font></div>'
  , 'D', '<div nowrap align="center"><font color="#000099"><b>Deleted</b></font></div>'
  , 'X', '<div nowrap align="center"><font color="#990000"><b>Expired</b></font></div>')  status
  , handle  handle
  , '<div nowrap align="right">' || TO_CHAR(bp.start_time, 'mm/dd/yyyy HH24:MI:SS')  || '</div>'  start_time
  , '<div nowrap align="right">' || TO_CHAR(bp.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '</div>'  completion_time
  , bp.elapsed_seconds  elapsed_seconds
FROM
  v$backup_set  bs
  , v$backup_piece  bp
WHERE
  bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
ORDER BY
  bs.recid
  , piece#;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- RMAN BACKUP CONTROL FILES -
 
prompt <a name="rman_backup_control_files"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup Control Files</b></font><hr align="left" width="460">
 
prompt <b>Available automatic control files within all available (and expired) backup sets</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN bs_key  FORMAT a75  HEADING 'BS Key'  ENTMAP off
COLUMN piece#  HEADING 'Piece #'  ENTMAP off
COLUMN copy#  HEADING 'Copy #'  ENTMAP off
COLUMN bp_key  HEADING 'BP Key'  ENTMAP off
COLUMN controlfile_included  FORMAT a75  HEADING 'Controlfile Included?'  ENTMAP off
COLUMN status  HEADING 'Status'  ENTMAP off
COLUMN handle  HEADING 'Handle'  ENTMAP off
COLUMN start_time  FORMAT a40  HEADING 'Start Time'  ENTMAP off
COLUMN completion_time  FORMAT a40  HEADING 'End Time'  ENTMAP off
COLUMN elapsed_seconds  FORMAT 999,999,999,999,999  HEADING 'Elapsed Seconds'  ENTMAP off
COLUMN deleted  FORMAT a10  HEADING 'Deleted?'  ENTMAP off
 
BREAK ON bs_key
 
SELECT
  '<div align="center"><font color="#336699"><b>' || bs.recid  || '</b></font></div>'  bs_key
  , bp.piece#  piece#
  , bp.copy#  copy#
  , bp.recid  bp_key
  , '<div align="center"><font color="#663300"><b>'  ||
  DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included)  ||
  '</b></font></div>'  controlfile_included
  , DECODE(  status
  , 'A', '<div nowrap align="center"><font color="darkgreen"><b>Available</b></font></div>'
  , 'D', '<div nowrap align="center"><font color="#000099"><b>Deleted</b></font></div>'
  , 'X', '<div nowrap align="center"><font color="#990000"><b>Expired</b></font></div>')  status
  , handle  handle
FROM
  v$backup_set  bs
  , v$backup_piece  bp
WHERE
  bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.controlfile_included != 'NO'
ORDER BY
  bs.recid
  , piece#;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- RMAN BACKUP SPFILE -
 
prompt <a name="rman_backup_spfile"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup SPFILE</b></font><hr align="left" width="460">
 
prompt <b>Available automatic SPFILE backups within all available (and expired) backup sets</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN bs_key  FORMAT a75  HEADING 'BS Key'  ENTMAP off
COLUMN piece#  HEADING 'Piece #'  ENTMAP off
COLUMN copy#  HEADING 'Copy #'  ENTMAP off
COLUMN bp_key  HEADING 'BP Key'  ENTMAP off
COLUMN spfile_included  FORMAT a75  HEADING 'SPFILE Included?'  ENTMAP off
COLUMN status  HEADING 'Status'  ENTMAP off
COLUMN handle  HEADING 'Handle'  ENTMAP off
COLUMN start_time  FORMAT a40  HEADING 'Start Time'  ENTMAP off
COLUMN completion_time  FORMAT a40  HEADING 'End Time'  ENTMAP off
COLUMN elapsed_seconds  FORMAT 999,999,999,999,999  HEADING 'Elapsed Seconds'  ENTMAP off
COLUMN deleted  FORMAT a10  HEADING 'Deleted?'  ENTMAP off
 
BREAK ON bs_key
 
SELECT
  '<div align="center"><font color="#336699"><b>' || bs.recid  || '</b></font></div>'  bs_key
  , bp.piece#  piece#
  , bp.copy#  copy#
  , bp.recid  bp_key
  , '<div align="center"><font color="#663300"><b>'  ||
  NVL(sp.spfile_included, '-')  ||
  '</b></font></div>'  spfile_included
  , DECODE(  status
  , 'A', '<div nowrap align="center"><font color="darkgreen"><b>Available</b></font></div>'
  , 'D', '<div nowrap align="center"><font color="#000099"><b>Deleted</b></font></div>'
  , 'X', '<div nowrap align="center"><font color="#990000"><b>Expired</b></font></div>')  status
  , handle  handle
FROM
  v$backup_set  bs
  , v$backup_piece  bp
  ,  (select distinct set_stamp, set_count, 'YES' spfile_included
  from v$backup_spfile)  sp
WHERE
  bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.set_stamp = sp.set_stamp
  AND bs.set_count = sp.set_count
ORDER BY
  bs.recid
  , piece#;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- ARCHIVING MODE -
 
prompt <a name="archiving_mode"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Archiving Mode</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN db_log_mode  FORMAT a95  HEADING 'Database|Log Mode'  ENTMAP off
COLUMN log_archive_start  FORMAT a95  HEADING 'Automatic|Archival'  ENTMAP off
COLUMN oldest_online_log_sequence  FORMAT 999999999999999  HEADING 'Oldest Online |Log Sequence'  ENTMAP off
COLUMN current_log_seq  FORMAT 999999999999999  HEADING 'Current |Log Sequence'  ENTMAP off
 
SELECT
  '<div align="center"><font color="#663300"><b>' || d.log_mode  || '</b></font></div>'  db_log_mode
  , '<div align="center"><font color="#663300"><b>' || p.log_archive_start  || '</b></font></div>'  log_archive_start
  , c.current_log_seq  current_log_seq
  , o.oldest_online_log_sequence  oldest_online_log_sequence
FROM
  (select
  DECODE(  log_mode
  , 'ARCHIVELOG', 'Archive Mode'
  , 'NOARCHIVELOG', 'No Archive Mode'
  , log_mode
  )  log_mode
  from v$database
  ) d
  , (select
  DECODE(  log_mode
  , 'ARCHIVELOG', 'Enabled'
  , 'NOARCHIVELOG', 'Disabled')  log_archive_start
  from v$database
  ) p
  , (select a.sequence#  current_log_seq
  from  v$log a
  where  a.status = 'CURRENT'
  and thread# = &_thread_number
  ) c
  , (select min(a.sequence#) oldest_online_log_sequence
  from  v$log a
  where  thread# = &_thread_number
  ) o
/
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- ARCHIVE DESTINATIONS -
 
prompt <a name="archive_destinations"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Archive Destinations</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN dest_id  HEADING 'Destination|ID'  ENTMAP off
COLUMN dest_name  HEADING 'Destination|Name'  ENTMAP off
COLUMN destination  HEADING 'Destination'  ENTMAP off
COLUMN status  HEADING 'Status'  ENTMAP off
COLUMN schedule  HEADING 'Schedule'  ENTMAP off
COLUMN archiver  HEADING 'Archiver'  ENTMAP off
COLUMN log_sequence  FORMAT 999999999999999  HEADING 'Current Log|Sequence'  ENTMAP off
 
SELECT
  '<div align="center"><font color="#336699"><b>' || a.dest_id || '</b></font></div>'  dest_id
  , a.dest_name  dest_name
  , a.destination  destination
  , DECODE(  a.status
  , 'VALID',  '<div align="center"><b><font color="darkgreen">' || status || '</font></b></div>'
  , 'INACTIVE', '<div align="center"><b><font color="#990000">'  || status || '</font></b></div>'
  ,  '<div align="center"><b><font color="#663300">'  || status || '</font></b></div>' ) status
  , DECODE(  a.schedule
  , 'ACTIVE',  '<div align="center"><b><font color="darkgreen">' || schedule || '</font></b></div>'
  , 'INACTIVE', '<div align="center"><b><font color="#990000">'  || schedule || '</font></b></div>'
  ,  '<div align="center"><b><font color="#663300">'  || schedule || '</font></b></div>' ) schedule
  , a.archiver  archiver
  , a.log_sequence  log_sequence
FROM
  v$archive_dest a
ORDER BY
  a.dest_id
/
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- ARCHIVING INSTANCE PARAMETERS -
 
prompt <a name="archiving_instance_parameters"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Archiving Instance Parameters</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN name  HEADING 'Parameter Name'  ENTMAP off
COLUMN value  HEADING 'Parameter Value'  ENTMAP off
 
SELECT
  '<b><font color="#336699">' || a.name || '</font></b>'  name
  , a.value  value
FROM
  v$parameter a
WHERE
  a.name like 'log_%'
ORDER BY
  a.name;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- ARCHIVING HISTORY -
 
prompt <a name="archiving_history"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Archiving History</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN thread#  FORMAT a79  HEADING 'Thread#'  ENTMAP off
COLUMN sequence#  FORMAT a79  HEADING 'Sequence#'  ENTMAP off
COLUMN name  HEADING 'Name'  ENTMAP off
COLUMN first_change#  HEADING 'First|Change #'  ENTMAP off
COLUMN first_time  FORMAT a75  HEADING 'First|Time'  ENTMAP off
COLUMN next_change#  HEADING 'Next|Change #'  ENTMAP off
COLUMN next_time  FORMAT a75  HEADING 'Next|Time'  ENTMAP off
COLUMN log_size  FORMAT 999,999,999,999,999  HEADING 'Size (in bytes)'  ENTMAP off
COLUMN archived  FORMAT a31  HEADING 'Archived?'  ENTMAP off
COLUMN applied  FORMAT a31  HEADING 'Applied?'  ENTMAP off
COLUMN deleted  FORMAT a31  HEADING 'Deleted?'  ENTMAP off
COLUMN status  FORMAT a75  HEADING 'Status'  ENTMAP off
 
BREAK ON report ON thread#
 
SELECT
  '<div align="center"><b><font color="#336699">' || thread#  || '</font></b></div>'  thread#
  , '<div align="center"><b><font color="#336699">' || sequence# || '</font></b></div>'  sequence#
  , name
  , first_change#
  , '<div align="right" nowrap>' || TO_CHAR(first_time, 'mm/dd/yyyy HH24:MI:SS') || '</div>' first_time
  , next_change#
  , '<div align="right" nowrap>' || TO_CHAR(next_time, 'mm/dd/yyyy HH24:MI:SS')  || '</div>' next_time
  , (blocks * block_size)  log_size
  , '<div align="center">' || archived || '</div>'  archived
  , '<div align="center">' || applied  || '</div>'  applied
  , '<div align="center">' || deleted  || '</div>'  deleted
  , DECODE(  status
  , 'A', '<div align="center"><b><font color="darkgreen">Available</font></b></div>'
  , 'D', '<div align="center"><b><font color="#663300">Deleted</font></b></div>'
  , 'U', '<div align="center"><b><font color="#990000">Unavailable</font></b></div>'
  , 'X', '<div align="center"><b><font color="#990000">Expired</font></b></div>'
  ) status
FROM
  v$archived_log
WHERE
  status in ('A')
ORDER BY
  thread#
  , sequence#;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- FLASH RECOVERY AREA PARAMETERS -
 
prompt <a name="flash_recovery_area_parameters"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Flash Recovery Area Parameters</b></font><hr align="left" width="460">
 
prompt <b>db_recovery_file_dest_size is specified in bytes</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name_print  FORMAT a95  HEADING 'Instance Name'  ENTMAP off
COLUMN thread_number_print  FORMAT a95  HEADING 'Thread Number'  ENTMAP off
COLUMN name  FORMAT a125  HEADING 'Name'  ENTMAP off
COLUMN value  HEADING 'Value'  ENTMAP off
 
BREAK ON report ON instance_name_print ON thread_number_print
 
SELECT
  '<div align="center"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name_print
  , '<div align="center">'  || i.thread#  || '</div>'  thread_number_print
  , '<div nowrap>'  || p.name  || '</div>'  name
  , (CASE p.name
  WHEN 'db_recovery_file_dest_size' THEN '<div nowrap align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
  ELSE
  '<div nowrap align="right">' || NVL(p.value, '(null)') || '</div>'
  END)  value
FROM
  gv$parameter p
  , gv$instance  i
WHERE
  p.inst_id = i.inst_id
  AND p.name IN ('db_recovery_file_dest_size', 'db_recovery_file_dest')
ORDER BY
  1
  , 3;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- FLASH RECOVERY AREA STATUS -
 
prompt <a name="flash_recovery_area_status"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Flash Recovery Area Status</b></font><hr align="left" width="460">
 
prompt <b>Current location, disk quota, space in use, space reclaimable by deleting files, and number of files in the Flash Recovery Area</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN name  FORMAT a75  HEADING 'Name'  ENTMAP off
COLUMN space_limit  FORMAT 99,999,999,999,999  HEADING 'Space Limit'  ENTMAP off
COLUMN space_used  FORMAT 99,999,999,999,999  HEADING 'Space Used'  ENTMAP off
COLUMN space_used_pct  FORMAT 999.99  HEADING '% Used'  ENTMAP off
COLUMN space_reclaimable  FORMAT 99,999,999,999,999  HEADING 'Space Reclaimable'  ENTMAP off
COLUMN pct_reclaimable  FORMAT 999.99  HEADING '% Reclaimable'  ENTMAP off
COLUMN number_of_files  FORMAT 999,999  HEADING 'Number of Files'  ENTMAP off
 
SELECT
  '<div align="center"><font color="#336699"><b>' || name || '</b></font></div>'  name
  , space_limit  space_limit
  , space_used  space_used
  , ROUND((space_used / DECODE(space_limit, 0, 0.000001, space_limit))*100, 2)  space_used_pct
  , space_reclaimable  space_reclaimable
  , ROUND((space_reclaimable / DECODE(space_limit, 0, 0.000001, space_limit))*100, 2) pct_reclaimable
  , number_of_files  number_of_files
FROM
  v$recovery_file_dest
ORDER BY
  name;
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN file_type  FORMAT a75  HEADING 'File Type'
COLUMN percent_space_used  HEADING 'Percent Space Used'
COLUMN percent_space_reclaimable  HEADING 'Percent Space Reclaimable'
COLUMN number_of_files  FORMAT 999,999 HEADING 'Number of Files'
 
SELECT
  '<div align="center"><font color="#336699"><b>' || file_type || '</b></font></div>' file_type
  , percent_space_used  percent_space_used
  , percent_space_reclaimable  percent_space_reclaimable
  , number_of_files  number_of_files
FROM
  v$flash_recovery_area_usage;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- +============================================================================+
-- ||
-- |<<<<<  FLASHBACK TECHNOLOGIES  >>>>>
-- ||
-- +============================================================================+
 
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Flashback Technologies</u></b></font></center>
 
-- |- FLASHBACK DATABASE PARAMETERS -
 
prompt <a name="flashback_database_parameters"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Flashback Database Parameters</b></font><hr align="left" width="460">
 
prompt <b>db_flashback_retention_target is specified in minutes</b>
prompt <b>db_recovery_file_dest_size is specified in bytes</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name_print  FORMAT a95  HEADING 'Instance Name'  ENTMAP off
COLUMN thread_number_print  FORMAT a95  HEADING 'Thread Number'  ENTMAP off
COLUMN name  FORMAT a125  HEADING 'Name'  ENTMAP off
COLUMN value  HEADING 'Value'  ENTMAP off
 
BREAK ON report ON instance_name_print ON thread_number_print
 
SELECT
  '<div align="center"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name_print
  , '<div align="center">'  || i.thread#  || '</div>'  thread_number_print
  , '<div nowrap>'  || p.name  || '</div>'  name
  , (CASE p.name
  WHEN 'db_recovery_file_dest_size'  THEN '<div nowrap align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
  WHEN 'db_flashback_retention_target' THEN '<div nowrap align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
  ELSE
  '<div nowrap align="right">' || NVL(p.value, '(null)') || '</div>'
  END)  value
FROM
  gv$parameter p
  , gv$instance  i
WHERE
  p.inst_id = i.inst_id
  AND p.name IN ('db_flashback_retention_target', 'db_recovery_file_dest_size', 'db_recovery_file_dest')
ORDER BY
  1
  , 3;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- FLASHBACK DATABASE STATUS -
 
prompt <a name="flashback_database_status"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Flashback Database Status</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN dbid  HEADING 'DB ID'  ENTMAP off
COLUMN name  FORMAT A75  HEADING 'DB Name'  ENTMAP off
COLUMN log_mode  FORMAT A75  HEADING 'Log Mode'  ENTMAP off
COLUMN flashback_on  FORMAT A75  HEADING 'Flashback DB On?'  ENTMAP off
 
SELECT
  '<div align="center"><font color="#336699"><b>' || dbid  || '</b></font></div>'  dbid
  , '<div align="center">'  || name  || '</div>'  name
  , '<div align="center">'  || log_mode  || '</div>'  log_mode
  , '<div align="center">'  || flashback_on  || '</div>'  flashback_on
FROM v$database;
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN oldest_flashback_time  FORMAT a125  HEADING 'Oldest Flashback Time'  ENTMAP off
COLUMN oldest_flashback_scn  HEADING 'Oldest Flashback SCN'  ENTMAP off
COLUMN retention_target  FORMAT 999,999  HEADING 'Retention Target (min)'  ENTMAP off
COLUMN retention_target_hours  FORMAT 999,999  HEADING 'Retention Target (hour)'  ENTMAP off
COLUMN flashback_size  FORMAT 9,999,999,999,999  HEADING 'Flashback Size'  ENTMAP off
COLUMN estimated_flashback_size FORMAT 9,999,999,999,999  HEADING 'Estimated Flashback Size'  ENTMAP off
 
SELECT
  '<div align="center"><font color="#336699"><b>' || TO_CHAR(oldest_flashback_time,'mm/dd/yyyy HH24:MI:SS') || '</b></font></div>'  oldest_flashback_time
  , oldest_flashback_scn  oldest_flashback_scn
  , retention_target  retention_target
  , retention_target/60  retention_target_hours
  , flashback_size  flashback_size
  , estimated_flashback_size  estimated_flashback_size
FROM
  v$flashback_database_log
ORDER BY
  1;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- FLASHBACK DATABASE REDO TIME MATRIX -
 
prompt <a name="flashback_database_redo_time_matrix"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Flashback Database Redo Time Matrix</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN begin_time  FORMAT a75  HEADING 'Begin Time'  ENTMAP off
COLUMN end_time  FORMAT a75  HEADING 'End Time'  ENTMAP off
COLUMN flashback_data  FORMAT 9,999,999,999,999  HEADING 'Flashback Data'  ENTMAP off
COLUMN db_data  FORMAT 9,999,999,999,999  HEADING 'DB Data'  ENTMAP off
COLUMN redo_data  FORMAT 9,999,999,999,999  HEADING 'Redo Data'  ENTMAP off
COLUMN estimated_flashback_size FORMAT 9,999,999,999,999  HEADING 'Estimated Flashback Size' ENTMAP off
 
SELECT
  '<div align="right">' || TO_CHAR(begin_time,'mm/dd/yyyy HH24:MI:SS') || '</div>'  begin_time
  , '<div align="right">' || TO_CHAR(end_time,'mm/dd/yyyy HH24:MI:SS') || '</div>'  end_time
  , flashback_data
  , db_data
  , redo_data
  , estimated_flashback_size
FROM
  v$flashback_database_stat
ORDER BY
  begin_time;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- +============================================================================+
-- ||
-- |<<<<<  PERFORMANCE  >>>>>
-- ||
-- +============================================================================+
 
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Performance</u></b></font></center>
 
-- |- SGA INFORMATION -
 
prompt <a name="sga_information"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SGA Information</b></font><hr align="left" width="460">
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name FORMAT a79  HEADING 'Instance Name'  ENTMAP off
COLUMN name  FORMAT a150  HEADING 'Pool Name'  ENTMAP off
COLUMN value  FORMAT 999,999,999,999,999 HEADING 'Bytes'  ENTMAP off
 
BREAK ON report ON instance_name
COMPUTE sum LABEL '<font color="#990000"><b>Total:</b></font>' OF value ON instance_name
 
SELECT
  '<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name
  , '<div align="left"><font color="#336699"><b>' || s.name  || '</b></font></div>'  name
  , s.value  value
FROM
  gv$sga  s
  , gv$instance  i
WHERE
  s.inst_id = i.inst_id
ORDER BY
  i.instance_name
  , s.value DESC;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- SGA TARGET ADVICE -
 
prompt <a name="sga_target_advice"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SGA Target Advice</b></font><hr align="left" width="460">
 
prompt Modify the SGA_TARGET parameter (up to the size of the SGA_MAX_SIZE, if necessary) to reduce
prompt the number of "Estimated Physical Reads".
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name FORMAT a79  HEADING 'Instance Name'  ENTMAP off
COLUMN name  FORMAT a79  HEADING 'Parameter Name'  ENTMAP off
COLUMN value  FORMAT a79  HEADING 'Value'  ENTMAP off
 
BREAK ON report ON instance_name
 
SELECT
  '<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name
  , p.name  name
  , (CASE p.name
  WHEN 'sga_max_size' THEN '<div align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
  WHEN 'sga_target'  THEN '<div align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
  ELSE
  '<div align="right">' || p.value || '</div>'
  END) value
FROM
  gv$parameter p
  , gv$instance  i
WHERE
  p.inst_id = i.inst_id
  AND p.name IN ('sga_max_size', 'sga_target')
ORDER BY
  i.instance_name
  , p.name;
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name  FORMAT a79  HEADING 'Instance Name'  ENTMAP off
COLUMN sga_size  FORMAT 999,999,999,999,999  HEADING 'SGA Size'  ENTMAP off
COLUMN sga_size_factor  FORMAT 999,999,999,999,999  HEADING 'SGA Size Factor'  ENTMAP off
COLUMN estd_db_time  FORMAT 999,999,999,999,999  HEADING 'Estimated DB Time'  ENTMAP off
COLUMN estd_db_time_factor  FORMAT 999,999,999,999,999  HEADING 'Estimated DB Time Factor'  ENTMAP off
COLUMN estd_physical_reads  FORMAT 999,999,999,999,999  HEADING 'Estimated Physical Reads'  ENTMAP off
 
BREAK ON report ON instance_name
 
SELECT
  '<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name
  , s.sga_size
  , s.sga_size_factor
  , s.estd_db_time
  , s.estd_db_time_factor
  , s.estd_physical_reads
FROM
  gv$sga_target_advice s
  , gv$instance  i
WHERE
  s.inst_id = i.inst_id
ORDER BY
  i.instance_name
  , s.sga_size_factor;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- SGA (ASMM) DYNAMIC COMPONENTS -
 
prompt <a name="sga_asmm_dynamic_components"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SGA (ASMM) Dynamic Components</b></font><hr align="left" width="460">
 
prompt Provides a summary report of all dynamic components as part of the Automatic Shared Memory
prompt Management (ASMM) configuration. This will display the total real memory allocation for the current
prompt SGA from the V$SGA_DYNAMIC_COMPONENTS view, which contains both manual and autotuned SGA components.
prompt As with the other manageability features of Oracle Database 10g, ASMM requires you to set the 
prompt STATISTICS_LEVEL parameter to at least TYPICAL (the default) before attempting to enable ASMM. ASMM
prompt can be enabled by setting SGA_TARGET to a nonzero value in the initialization parameter file (pfile/spfile).
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name  FORMAT a79  HEADING 'Instance Name'  ENTMAP off
COLUMN component  FORMAT a79  HEADING 'Component Name'  ENTMAP off
COLUMN current_size  FORMAT 999,999,999,999  HEADING 'Current Size'  ENTMAP off
COLUMN min_size  FORMAT 999,999,999,999  HEADING 'Min Size'  ENTMAP off
COLUMN max_size  FORMAT 999,999,999,999  HEADING 'Max Size'  ENTMAP off
COLUMN user_specified_size  FORMAT 999,999,999,999  HEADING 'User Specified|Size'  ENTMAP off
COLUMN oper_count  FORMAT 999,999,999,999  HEADING 'Oper.|Count'  ENTMAP off
COLUMN last_oper_type  FORMAT a75  HEADING 'Last Oper.|Type'  ENTMAP off
COLUMN last_oper_mode  FORMAT a75  HEADING 'Last Oper.|Mode'  ENTMAP off
COLUMN last_oper_time  FORMAT a75  HEADING 'Last Oper.|Time'  ENTMAP off
COLUMN granule_size  FORMAT 999,999,999,999  HEADING 'Granule Size'  ENTMAP off
 
BREAK ON report ON instance_name
 
SELECT
  '<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name
  , sdc.component
  , sdc.current_size
  , sdc.min_size
  , sdc.max_size
  , sdc.user_specified_size
  , sdc.oper_count
  , sdc.last_oper_type
  , sdc.last_oper_mode
  , '<div align="right">' || NVL(TO_CHAR(sdc.last_oper_time, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'  last_oper_time
  , sdc.granule_size
FROM
  gv$sga_dynamic_components sdc
  , gv$instance  i
ORDER BY
  i.instance_name
  , sdc.component DESC;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- PGA TARGET ADVICE -
 
prompt <a name="pga_target_advice"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>PGA Target Advice</b></font><hr align="left" width="460">
 
prompt The <b>V$PGA_TARGET_ADVICE</b> view predicts how the statistics cache hit percentage and over
prompt allocation count in V$PGASTAT will be impacted if you change the value of the
prompt initialization parameter PGA_AGGREGATE_TARGET. When you set the PGA_AGGREGATE_TARGET and
prompt WORKAREA_SIZE_POLICY to <b>AUTO</b> then the *_AREA_SIZE parameter are automatically ignored and
prompt Oracle will automatically use the computed value for these parameters. Use the results from
prompt the query below to adequately set the initialization parameter PGA_AGGREGATE_TARGET as to avoid
prompt any over allocation. If column ESTD_OVERALLOCATION_COUNT in the V$PGA_TARGET_ADVICE
prompt view (below) is nonzero, it indicates that PGA_AGGREGATE_TARGET is too small to even
prompt meet the minimum PGA memory needs. If PGA_AGGREGATE_TARGET is set within the over
prompt allocation zone, the memory manager will over-allocate memory and actual PGA memory
prompt consumed will be more than the limit you set. It is therefore meaningless to set a
prompt value of PGA_AGGREGATE_TARGET in that zone. After eliminating over-allocations, the
prompt goal is to maximize the PGA cache hit percentage, based on your response-time requirement
prompt and memory constraints.
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name FORMAT a79  HEADING 'Instance Name'  ENTMAP off
COLUMN name  FORMAT a79  HEADING 'Parameter Name'  ENTMAP off
COLUMN value  FORMAT a79  HEADING 'Value'  ENTMAP off
 
BREAK ON report ON instance_name
 
SELECT
  '<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name
  , p.name  name
  , (CASE p.name
  WHEN 'pga_aggregate_target' THEN '<div align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
  ELSE
  '<div align="right">' || p.value || '</div>'
  END) value
FROM
  gv$parameter p
  , gv$instance  i
WHERE
  p.inst_id = i.inst_id
  AND p.name IN ('pga_aggregate_target', 'workarea_size_policy')
ORDER BY
  i.instance_name
  , p.name;
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN instance_name  FORMAT a79  HEADING 'Instance Name'  ENTMAP off
COLUMN pga_target_for_estimate  FORMAT 999,999,999,999,999  HEADING 'PGA Target for Estimate'  ENTMAP off
COLUMN estd_extra_bytes_rw  FORMAT 999,999,999,999,999  HEADING 'Estimated Extra Bytes R/W'  ENTMAP off
COLUMN estd_pga_cache_hit_percentage  FORMAT 999,999,999,999,999  HEADING 'Estimated PGA Cache Hit %'  ENTMAP off
COLUMN estd_overalloc_count  FORMAT 999,999,999,999,999  HEADING 'ESTD_OVERALLOC_COUNT'  ENTMAP off
 
BREAK ON report ON instance_name
 
SELECT
  '<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>'  instance_name
  , p.pga_target_for_estimate
  , p.estd_extra_bytes_rw
  , p.estd_pga_cache_hit_percentage
  , p.estd_overalloc_count
FROM
  gv$pga_target_advice p
  , gv$instance  i
WHERE
  p.inst_id = i.inst_id
ORDER BY
  i.instance_name
  , p.pga_target_for_estimate;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- FILE I/O STATISTICS -
 
prompt <a name="file_io_statistics"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>File I/O Statistics</b></font><hr align="left" width="460">
 
prompt <b>Ordered by "Physical Reads" since last startup of the Oracle instance</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN tablespace_name  FORMAT a50  HEAD 'Tablespace'  ENTMAP off
COLUMN fname  HEAD 'File Name'  ENTMAP off
COLUMN phyrds  FORMAT 999,999,999,999,999  HEAD 'Physical Reads'  ENTMAP off
COLUMN phywrts  FORMAT 999,999,999,999,999  HEAD 'Physical Writes'  ENTMAP off
COLUMN read_pct  HEAD 'Read Pct.'  ENTMAP off
COLUMN write_pct  HEAD 'Write Pct.'  ENTMAP off
COLUMN total_io  FORMAT 999,999,999,999,999  HEAD 'Total I/O'  ENTMAP off
 
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' OF phyrds phywrts total_io ON report
 
SELECT
  '<font color="#336699"><b>' || df.tablespace_name || '</b></font>'  tablespace_name
  , df.file_name  fname
  , fs.phyrds  phyrds
  , '<div align="right">' || ROUND((fs.phyrds * 100) / (fst.pr + tst.pr), 2) || '%</div>'  read_pct
  , fs.phywrts  phywrts
  , '<div align="right">' || ROUND((fs.phywrts * 100) / (fst.pw + tst.pw), 2) || '%</div>'  write_pct
  , (fs.phyrds + fs.phywrts)  total_io
FROM
  sys.dba_data_files df
  , v$filestat  fs
  , (select sum(f.phyrds) pr, sum(f.phywrts) pw from v$filestat f) fst
  , (select sum(t.phyrds) pr, sum(t.phywrts) pw from v$tempstat t) tst
WHERE
  df.file_id = fs.file#
UNION
SELECT
  '<font color="#336699"><b>' || tf.tablespace_name || '</b></font>'  tablespace_name
  , tf.file_name  fname
  , ts.phyrds  phyrds
  , '<div align="right">' || ROUND((ts.phyrds * 100) / (fst.pr + tst.pr), 2) || '%</div>'  read_pct
  , ts.phywrts  phywrts
  , '<div align="right">' || ROUND((ts.phywrts * 100) / (fst.pw + tst.pw), 2) || '%</div>' write_pct
  , (ts.phyrds + ts.phywrts)  total_io
FROM
  sys.dba_temp_files  tf
  , v$tempstat  ts
  , (select sum(f.phyrds) pr, sum(f.phywrts) pw from v$filestat f) fst
  , (select sum(t.phyrds) pr, sum(t.phywrts) pw from v$tempstat t) tst
WHERE
  tf.file_id = ts.file#
ORDER BY phyrds DESC;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- FILE I/O TIMINGS -
 
prompt <a name="file_io_timings"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>File I/O Timings</b></font><hr align="left" width="460">
 
prompt <b>Average time (in milliseconds) for an I/O call per datafile since last startup of the Oracle instance - (ordered by Physical Reads)</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN fname  HEAD 'File Name'  ENTMAP off
COLUMN phyrds  FORMAT 999,999,999,999,999  HEAD 'Physical Reads'  ENTMAP off
COLUMN read_rate  FORMAT 999,999,999,999,999.99 HEAD 'Average Read Time<br>(milliseconds per read)'  ENTMAP off
COLUMN phywrts  FORMAT 999,999,999,999,999  HEAD 'Physical Writes'  ENTMAP off
COLUMN write_rate  FORMAT 999,999,999,999,999.99 HEAD 'Average Write Time<br>(milliseconds per write)' ENTMAP off
 
BREAK ON REPORT
COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' OF phyrds phywrts ON report
COMPUTE avg LABEL '<font color="#990000"><b>Average: </b></font>' OF read_rate write_rate ON report
 
SELECT
  '<b><font color="#336699">' || d.name || '</font></b>'  fname
  , s.phyrds  phyrds
  , ROUND((s.readtim/GREATEST(s.phyrds,1)), 2)  read_rate
  , s.phywrts  phywrts
  , ROUND((s.writetim/GREATEST(s.phywrts,1)),2)  write_rate
FROM
  v$filestat  s
  , v$datafile  d
WHERE
  s.file# = d.file#
UNION
SELECT
  '<b><font color="#336699">' || t.name || '</font></b>'  fname
  , s.phyrds  phyrds
  , ROUND((s.readtim/GREATEST(s.phyrds,1)), 2)  read_rate
  , s.phywrts  phywrts
  , ROUND((s.writetim/GREATEST(s.phywrts,1)),2)  write_rate
FROM
  v$tempstat  s
  , v$tempfile  t
WHERE
  s.file# = t.file#
ORDER BY
  2 DESC;
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- AVERAGE OVERALL I/O PER SECOND -
 
prompt <a name="average_overall_io_per_sec"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Average Overall I/O per Second</b></font><hr align="left" width="460">
 
prompt <b>Average overall I/O calls (physical read/write calls) since last startup of the Oracle instance</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
DECLARE
 
CURSOR get_file_io IS
  SELECT
  NVL(SUM(a.phyrds + a.phywrts), 0)  sum_datafile_io
  , TO_NUMBER(null)  sum_tempfile_io
  FROM
  v$filestat a
  UNION
  SELECT
  TO_NUMBER(null)  sum_datafile_io
  , NVL(SUM(b.phyrds + b.phywrts), 0)  sum_tempfile_io
  FROM
  v$tempstat b;
 
current_time  DATE;
elapsed_time_seconds  NUMBER;
sum_datafile_io  NUMBER;
sum_datafile_io2  NUMBER;
sum_tempfile_io  NUMBER;
sum_tempfile_io2  NUMBER;
total_io  NUMBER;
datafile_io_per_sec  NUMBER;
tempfile_io_per_sec  NUMBER;
total_io_per_sec  NUMBER;
 
BEGIN
  OPEN get_file_io;
  FOR i IN 1..2 LOOP
  FETCH get_file_io INTO sum_datafile_io, sum_tempfile_io;
  IF i = 1 THEN
  sum_datafile_io2 := sum_datafile_io;
  ELSE
  sum_tempfile_io2 := sum_tempfile_io;
  END IF;
  END LOOP;
 
  total_io := sum_datafile_io2 + sum_tempfile_io2;
  SELECT sysdate INTO current_time FROM dual;
  SELECT CEIL ((current_time - startup_time)*(60*60*24)) INTO elapsed_time_seconds FROM v$instance;
 
  datafile_io_per_sec := sum_datafile_io2/elapsed_time_seconds;
  tempfile_io_per_sec := sum_tempfile_io2/elapsed_time_seconds;
  total_io_per_sec  := total_io/elapsed_time_seconds;
 
  DBMS_OUTPUT.PUT_LINE('<table width="90%" border="1">');
 
  DBMS_OUTPUT.PUT_LINE('<tr><th align="left" width="20%">Elapsed Time (in seconds)</th><td width="80%">' || TO_CHAR(elapsed_time_seconds, '9,999,999,999,999') || '</td></tr>');
  DBMS_OUTPUT.PUT_LINE('<tr><th align="left" width="20%">Datafile I/O Calls per Second</th><td width="80%">' || TO_CHAR(datafile_io_per_sec, '9,999,999,999,999') || '</td></tr>');
  DBMS_OUTPUT.PUT_LINE('<tr><th align="left" width="20%">Tempfile I/O Calls per Second</th><td width="80%">' || TO_CHAR(tempfile_io_per_sec, '9,999,999,999,999') || '</td></tr>');
  DBMS_OUTPUT.PUT_LINE('<tr><th align="left" width="20%">Total I/O Calls per Second</th><td width="80%">' || TO_CHAR(total_io_per_sec, '9,999,999,999,999') || '</td></tr>');
 
  DBMS_OUTPUT.PUT_LINE('</table>');
END;
/
 
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
 
-- |- REDO LOG CONTENTION -
 
prompt <a name="redo_log_contention"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Redo Log Contention</b></font><hr align="left" width="460">
 
prompt <b>All latches like redo% - (ordered by misses)</b>
 
CLEAR COLUMNS BREAKS COMPUTES
 
COLUMN name  FORMAT a95  HEADING 'Latch Name'
COLUMN gets  FORMAT 999,999,999,999,999,999  HEADING 'Gets'
COLUMN misses  FORMAT 999,999,999,999  HEADING 'Misses'
COLUMN sleeps  FORMAT 999,999,999,999  HEADING 'Sleeps'
COLUMN immediate_gets  FORMAT 999,999,999,999,999,999  HEADING 'Immediate Gets'
COLUMN immediate_misses FORMAT 999,999,999,999  HEADING 'Immediate Misses'
 
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total:</b></font>' OF gets misses sleeps immediate_gets immediate_misses ON report
 
SELECT 
  '<div align="left"><font color="#336699"><b>' || INITCAP(name) || '</b></font></div>' name
  , gets
  , misses
  , sleeps
  , immediate_gets
  , immediate_misses
FROM sys.v_$latch
WHERE name LIKE 'redo%'
ORDER BY 1;
 
prompt 
prompt <b>System statistics like redo%</b>
 
CLEAR COLUMNS