text: -- +----------------------------------------------------------------------------+
  -- |                          Jeffrey M. Hunter                                 |
  -- |                      jhunter@idevelopment.info                             |
  -- |                         www.idevelopment.info                              |
  -- |----------------------------------------------------------------------------|
  -- |      Copyright (c) 1998-2012 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-2012 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-2012 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>
  
  
  prompt -
  <tr><th colspan="4">Online Analytical Processing - (OLAP)</th></tr> -
  <tr> -
  <td nowrap align="center" width="25%"><a class="link" href="#dba_dimensions">Dimensions</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#dba_dimension_levels">Dimension Levels</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#dba_dimension_attributes">Dimension Attributes</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#dba_dimension_hierarchies">Dimension Hierarchies</a></td> -
  </tr> -
  <tr> -
  <td nowrap align="center" width="25%"><a class="link" href="#dba_cubes">Cubes</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#dba_olap_materialized_views">Materialized Views</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#dba_olap_materialized_view_logs">Materialized View Logs</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#dba_olap_materialized_view_refresh_groups">Materialized View Refresh Groups</a></td> -
  </tr>
  
  
  prompt -
  <tr><th colspan="4">Data Pump</th></tr> -
  <tr> -
  <td nowrap align="center" width="25%"><a class="link" href="#data_pump_jobs">Data Pump Jobs</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#data_pump_sessions">Data Pump Sessions</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#data_pump_job_progress">Data Pump Job Progress</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
  </tr>
  
  
  prompt -
  <tr><th colspan="4">Networking</th></tr> -
  <tr> -
  <td nowrap align="center" width="25%"><a class="link" href="#mts_dispatcher_statistics">MTS Dispatcher Statistics</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#mts_dispatcher_response_queue_wait_stats">MTS Dispatcher Response Queue Wait Stats</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#mts_shared_server_wait_statistics">MTS Shared Server Wait Statistics</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#"><br></a></td> -
  </tr>
  
  
  prompt -
  <tr><th colspan="4">Replication</th></tr> -
  <tr> -
  <td nowrap align="center" width="25%"><a class="link" href="#replication_summary">Replication Summary</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#deferred_transactions">Deferred Transactions</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#administrative_request_jobs">Administrative Request Jobs</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#rep_initialization_parameters">Initialization Parameters</a></td> -
  </tr> -
  <tr> -
  <td nowrap align="center" width="25%"><a class="link" href="#schedule_purge_jobs">(Schedule) - Purge Jobs</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#schedule_push_jobs">(Schedule) - Push Jobs</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#schedule_refresh_jobs">(Schedule) - Refresh Jobs</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#multimaster_master_groups">(Multi-Master) - Master Groups</a></td> -
  </tr> -
  <tr> -
  <td nowrap align="center" width="25%"><a class="link" href="#multimaster_master_groups_and_sites">(Multi-Master) - Master Groups and Sites</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#materialized_view_master_site_summary">(Materialized View) - Master Site Summary</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#materialized_view_master_site_logs">(Materialized View) - Master Site Logs</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#materialized_view_master_site_templates">(Materialized View) - Master Site Templates</a></td> -
  </tr> -
  <tr> -
  <td nowrap align="center" width="25%"><a class="link" href="#materialized_view_summary">(Materialized View) - Summary</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#materialized_view_groups">(Materialized View) - Groups</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#materialized_view_materialized_views">(Materialized View) - Materialized Views</a></td> -
  <td nowrap align="center" width="25%"><a class="link" href="#materialized_view_refresh_groups">(Materialized View) - Refresh Groups</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 BREAKS COMPUTES
  
  COLUMN name    FORMAT a95                   HEADING 'Statistics Name'
  COLUMN value   FORMAT 999,999,999,999,999   HEADING 'Value'
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || INITCAP(name) || '</b></font></div>' name
    , value
  FROM v$sysstat
  WHERE name LIKE 'redo%'
  ORDER BY 1;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                           - FULL TABLE SCANS -                             |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="full_table_scans"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Full Table Scans</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN large_table_scans   FORMAT 999,999,999,999,999  HEADING 'Large Table Scans'   ENTMAP off
  COLUMN small_table_scans   FORMAT 999,999,999,999,999  HEADING 'Small Table Scans'   ENTMAP off
  COLUMN pct_large_scans                                 HEADING 'Pct. Large Scans'    ENTMAP off
  
  SELECT
      a.value large_table_scans
    , b.value small_table_scans
    , '<div align="right">' || ROUND(100*a.value/DECODE((a.value+b.value),0,1,(a.value+b.value)),2) || '%</div>' pct_large_scans
  FROM
      v$sysstat  a
    , v$sysstat  b
  WHERE
        a.name = 'table scans (long tables)'
    AND b.name = 'table scans (short tables)';
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                                - SORTS -                                   |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="sorts"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Sorts</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN disk_sorts     FORMAT 999,999,999,999,999    HEADING 'Disk Sorts'       ENTMAP off
  COLUMN memory_sorts   FORMAT 999,999,999,999,999    HEADING 'Memory Sorts'     ENTMAP off
  COLUMN pct_disk_sorts                               HEADING 'Pct. Disk Sorts'  ENTMAP off
  
  SELECT
      a.value   disk_sorts
    , b.value   memory_sorts
    , '<div align="right">' || ROUND(100*a.value/DECODE((a.value+b.value),0,1,(a.value+b.value)),2) || '%</div>' pct_disk_sorts
  FROM
      v$sysstat  a
    , v$sysstat  b
  WHERE
        a.name = 'sorts (disk)'
    AND b.name = 'sorts (memory)';
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                               - OUTLINES -                                 |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_outlines"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Outlines</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN category       FORMAT a125    HEADING 'Category'     ENTMAP off
  COLUMN owner          FORMAT a125    HEADING 'Owner'        ENTMAP off
  COLUMN name           FORMAT a125    HEADING 'Name'         ENTMAP off
  COLUMN used                          HEADING 'Used?'        ENTMAP off
  COLUMN timestamp      FORMAT a125    HEADING 'Time Stamp'   ENTMAP off
  COLUMN version                       HEADING 'Version'      ENTMAP off
  COLUMN sql_text                      HEADING 'SQL Text'     ENTMAP off
  
  SELECT
      '<div nowrap><font color="#336699"><b>' || category || '</b></font></div>' category
    , owner
    , name
    , used
    , '<div nowrap align="right">' || TO_CHAR(timestamp, 'mm/dd/yyyy HH24:MI:SS') || '</div>' timestamp
    , version
    , sql_text
  FROM
      dba_outlines
  ORDER BY
      category
    , owner
    , name;
    
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                            - OUTLINE HINTS -                               |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_outline_hints"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Outline Hints</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN category       FORMAT a125    HEADING 'Category'        ENTMAP off
  COLUMN owner          FORMAT a125    HEADING 'Owner'           ENTMAP off
  COLUMN name           FORMAT a125    HEADING 'Name'            ENTMAP off
  COLUMN node                          HEADING 'Node'            ENTMAP off
  COLUMN join_pos                      HEADING 'Join Position'   ENTMAP off
  COLUMN hint                          HEADING 'Hint'            ENTMAP off
  
  BREAK ON category ON owner ON name
  
  SELECT
      '<div nowrap><font color="#336699"><b>' || a.category || '</b></font></div>' category
    , a.owner                                           owner
    , a.name                                            name
    , '<div align="center">' || b.node || '</div>'      node
    , '<div align="center">' || b.join_pos || '</div>'  join_pos
    , b.hint                                            hint
  FROM
      dba_outlines       a
    , dba_outline_hints  b
  WHERE
        a.owner = b.owner
    AND b.name  = b.name
  ORDER BY
      category
    , owner
    , name;
    
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                - SQL STATEMENTS WITH MOST BUFFER GETS -                    |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="sql_statements_with_most_buffer_gets"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SQL Statements With Most Buffer Gets</b></font><hr align="left" width="460">
  
  prompt <b>Top 100 SQL statements with buffer gets greater than 1000</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN username        FORMAT a75                   HEADING 'Username'                 ENTMAP off
  COLUMN buffer_gets     FORMAT 999,999,999,999,999   HEADING 'Buffer Gets'              ENTMAP off
  COLUMN executions      FORMAT 999,999,999,999,999   HEADING 'Executions'               ENTMAP off
  COLUMN gets_per_exec   FORMAT 999,999,999,999,999   HEADING 'Buffer Gets / Execution'  ENTMAP off
  COLUMN sql_text                                     HEADING 'SQL Text'                 ENTMAP off
  
  SELECT 
      '<font color="#336699"><b>' || UPPER(b.username) || '</b></font>' username
    , a.buffer_gets              buffer_gets
    , a.executions               executions
    , (a.buffer_gets / decode(a.executions, 0, 1, a.executions))  gets_per_exec
    , a.sql_text                 sql_text
  FROM 
      (SELECT ai.buffer_gets, ai.executions, ai.sql_text, ai.parsing_user_id
       FROM sys.v_$sqlarea ai
       ORDER BY ai.buffer_gets
      ) a
    , dba_users b
  WHERE
        a.parsing_user_id = b.user_id 
    AND a.buffer_gets > 1000
    AND b.username NOT IN ('SYS','SYSTEM')
    AND rownum < 101
  ORDER BY
      a.buffer_gets DESC;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                 - SQL STATEMENTS WITH MOST DISK READS -                    |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="sql_statements_with_most_disk_reads"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SQL Statements With Most Disk Reads</b></font><hr align="left" width="460">
  
  prompt <b>Top 100 SQL statements with disk reads greater than 1000</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN username        FORMAT a75                   HEADING 'Username'           ENTMAP off
  COLUMN disk_reads      FORMAT 999,999,999,999,999   HEADING 'Disk Reads'         ENTMAP off
  COLUMN executions      FORMAT 999,999,999,999,999   HEADING 'Executions'         ENTMAP off
  COLUMN reads_per_exec  FORMAT 999,999,999,999,999   HEADING 'Reads / Execution'  ENTMAP off
  COLUMN sql_text                                     HEADING 'SQL Text'           ENTMAP off
  
  SELECT 
      '<font color="#336699"><b>' || UPPER(b.username) || '</b></font>' username
    , a.disk_reads       disk_reads
    , a.executions       executions
    , (a.disk_reads / decode(a.executions, 0, 1, a.executions))  reads_per_exec
    , a.sql_text         sql_text
  FROM 
      (SELECT ai.disk_reads, ai.executions, ai.sql_text, ai.parsing_user_id
       FROM sys.v_$sqlarea ai
       ORDER BY ai.buffer_gets
      ) a
    , dba_users b
  WHERE
        a.parsing_user_id = b.user_id 
    AND a.disk_reads > 1000
    AND b.username NOT IN ('SYS','SYSTEM')
    AND rownum < 101
  ORDER BY
      a.disk_reads DESC;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  -- +============================================================================+
  -- |                                                                            |
  -- |        <<<<<     AUTOMATIC WORKLOAD REPOSITORY - (AWR)     >>>>>           |
  -- |                                                                            |
  -- +============================================================================+
  
  
  prompt
  prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Automatic Workload Repository - (AWR)</u></b></font></center>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                   - WORKLOAD REPOSITORY INFORMATION -                      |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="awr_workload_repository_information"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Workload Repository Information</b></font><hr align="left" width="460">
  
  prompt <b>Instances found in the "Workload Repository"</b>
  prompt <b>The instance running this report (&_instance_name) is indicated in "<font color="darkgreen">GREEN</font>"</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN dbbid          FORMAT a75           HEAD 'Database ID'      ENTMAP off
  COLUMN dbb_name       FORMAT a75           HEAD 'Database Name'    ENTMAP off
  COLUMN instt_name     FORMAT a75           HEAD 'Instance Name'    ENTMAP off
  COLUMN instt_num      FORMAT 9999999999    HEAD 'Instance Number'  ENTMAP off
  COLUMN host           FORMAT a75           HEAD 'Host'             ENTMAP off
  COLUMN host_platform  FORMAT a125          HEAD 'Host Platform'    ENTMAP off
  
  SELECT
      DISTINCT (CASE WHEN cd.dbid = wr.dbid
                          AND 
                          cd.name = wr.db_name
                          AND
                          ci.instance_number = wr.instance_number
                          AND
                          ci.instance_name = wr.instance_name
                     THEN '<div align="left"><font color="darkgreen"><b>' || wr.dbid || '</b></font></div>'
                     ELSE '<div align="left"><font color="#663300"><b>'   || wr.dbid || '</b></font></div>'
                END)                  dbbid
    , wr.db_name                      dbb_name
    , wr.instance_name                instt_name
    , wr.instance_number              instt_num
    , wr.host_name                    host
    , cd.platform_name                host_platform
  FROM
      dba_hist_database_instance wr
    , v$database cd
    , v$instance ci
  ORDER BY
      wr.instance_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                          - AWR SNAPSHOT SETTINGS -                         |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="awr_snapshot_settings"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>AWR Snapshot Settings</b></font><hr align="left" width="460">
  
  prompt Use the <b>DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS</b> procedure to modify the interval
  prompt of the snapshot generation and how long the snapshots are retained in the Workload Repository. The
  prompt default interval is 60 minutes and can be set to a value between 10 minutes and 5,256,000 (1 year).
  prompt The default retention period is 10,080 minutes (7 days) and can be set to a value between
  prompt 1,440 minutes (1 day) and 52,560,000 minutes (100 years).
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN dbbid           FORMAT a75    HEAD 'Database ID'          ENTMAP off
  COLUMN dbb_name        FORMAT a75    HEAD 'Database Name'        ENTMAP off
  COLUMN snap_interval   FORMAT a75    HEAD 'Snap Interval'        ENTMAP off
  COLUMN retention       FORMAT a75    HEAD 'Retention Period'     ENTMAP off
  COLUMN topnsql         FORMAT a75    HEAD 'Top N SQL'            ENTMAP off
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || s.dbid || '</b></font></div>'  dbbid
    , d.name                                                                          dbb_name
    , s.snap_interval                                                                 snap_interval
    , s.retention                                                                     retention
    , s.topnsql                                                                       
  FROM
      dba_hist_wr_control   s
    , v$database            d
  WHERE
      s.dbid = d.dbid
  ORDER BY
      dbbid;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                           - AWR SNAPSHOT LIST -                            |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="awr_snapshot_list"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>AWR Snapshot List</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN instance_name_print  FORMAT a75               HEADING 'Instance Name'          ENTMAP off
  COLUMN snap_id              FORMAT a75               HEADING 'Snap ID'                ENTMAP off
  COLUMN startup_time         FORMAT a75               HEADING 'Instance Startup Time'  ENTMAP off
  COLUMN begin_interval_time  FORMAT a75               HEADING 'Begin Interval Time'    ENTMAP off
  COLUMN end_interval_time    FORMAT a75               HEADING 'End Interval Time'      ENTMAP off
  COLUMN elapsed_time         FORMAT 999,999,999.99    HEADING 'Elapsed Time (min)'     ENTMAP off
  COLUMN db_time              FORMAT 999,999,999.99    HEADING 'DB Time (min)'          ENTMAP off
  COLUMN pct_db_time          FORMAT a75               HEADING '% DB Time'              ENTMAP off
  COLUMN cpu_time             FORMAT 999,999,999.99    HEADING 'CPU Time (min)'         ENTMAP off
  
  BREAK ON instance_name_print ON startup_time
  
  SELECT
      '<div align="center"><font color="#336699"><b>' || i.instance_name                                         || '</b></font></div>'  instance_name_print
    , '<div align="center"><font color="#336699"><b>' || s.snap_id                                               || '</b></font></div>'  snap_id
    , '<div nowrap align="right">'                    || TO_CHAR(s.startup_time, 'mm/dd/yyyy HH24:MI:SS')        || '</div>'             startup_time
    , '<div nowrap align="right">'                    || TO_CHAR(s.begin_interval_time, 'mm/dd/yyyy HH24:MI:SS') || '</div>'             begin_interval_time
    , '<div nowrap align="right">'                    || TO_CHAR(s.end_interval_time, 'mm/dd/yyyy HH24:MI:SS')   || '</div>'             end_interval_time
    , ROUND(EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +
            EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
            EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
            EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60, 2)                                                    elapsed_time
    , ROUND((e.value - b.value)/1000000/60, 2)                                                                                           db_time
    , '<div align="right">' || 
          ROUND(((((e.value - b.value)/1000000/60) / (EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +
                                                      EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
                                                      EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
                                                      EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60) ) * 100), 2) 
                               || ' %</div>'                                                                                             pct_db_time
  FROM
      dba_hist_snapshot       s
    , gv$instance             i
    , dba_hist_sys_time_model e
    , dba_hist_sys_time_model b
  WHERE
      i.instance_number   = s.instance_number
    AND e.snap_id         = s.snap_id
    AND b.snap_id         = s.snap_id - 1
    AND e.stat_id         = b.stat_id
    AND e.instance_number = b.instance_number
    AND e.instance_number = s.instance_number
    AND e.stat_name       = 'DB time'
  ORDER BY
      i.instance_name
    , s.snap_id;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                      - AWR SNAPSHOT SIZE ESTIMATES -                       |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="awr_snapshot_size_estimates"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>AWR Snapshot Size Estimates</b></font><hr align="left" width="460">
  
  DECLARE
  
      CURSOR get_instances IS
          SELECT COUNT(DISTINCT instance_number)
          FROM wrm$_database_instance;
    
      CURSOR get_wr_control_info IS
          SELECT snapint_num, retention_num
          FROM wrm$_wr_control;
    
      CURSOR get_snaps IS
          SELECT
              SUM(all_snaps)
            , SUM(good_snaps)
            , SUM(today_snaps)
            , SYSDATE - MIN(begin_interval_time)
          FROM
              (SELECT
                    1 AS all_snaps
                  , (CASE WHEN s.status = 0 THEN 1 ELSE 0 END) AS good_snaps
                  , (CASE WHEN (s.end_interval_time > SYSDATE - 1) THEN 1 ELSE 0 END) AS today_snaps
                  , CAST(s.begin_interval_time AS DATE) AS begin_interval_time
               FROM wrm$_snapshot s
               );
  
      CURSOR sysaux_occ_usage IS
          SELECT
              occupant_name
            , schema_name
            , space_usage_kbytes/1024 space_usage_mb
          FROM
              v$sysaux_occupants
          ORDER BY
              space_usage_kbytes DESC
            , occupant_name;
    
      mb_format           CONSTANT  VARCHAR2(30)  := '99,999,990.0';
      kb_format           CONSTANT  VARCHAR2(30)  := '999,999,990';
      pct_format          CONSTANT  VARCHAR2(30)  := '990.0';
      snapshot_interval   NUMBER;
      retention_interval  NUMBER;
      all_snaps           NUMBER;
      awr_size            NUMBER;
      snap_size           NUMBER;
      awr_average_size    NUMBER;
      est_today_snaps     NUMBER;
      awr_size_past24     NUMBER;
      good_snaps          NUMBER;
      today_snaps         NUMBER;
      num_days            NUMBER;
      num_instances       NUMBER;
  
  BEGIN
  
      OPEN get_instances;
      FETCH get_instances INTO num_instances;
      CLOSE get_instances;
  
      OPEN get_wr_control_info;
      FETCH get_wr_control_info INTO snapshot_interval, retention_interval;
      CLOSE get_wr_control_info;
  
      OPEN get_snaps;
      FETCH get_snaps INTO all_snaps, good_snaps, today_snaps, num_days;
      CLOSE get_snaps;
  
      FOR occ_rec IN sysaux_occ_usage
      LOOP
          IF (occ_rec.occupant_name = 'SM/AWR') THEN
              awr_size := occ_rec.space_usage_mb;
          END IF;
      END LOOP;
  
      snap_size := awr_size/all_snaps;
      awr_average_size := snap_size*86400/snapshot_interval;
  
      today_snaps := today_snaps / num_instances;
  
      IF (num_days < 1) THEN
          est_today_snaps := ROUND(today_snaps / num_days);
      ELSE
          est_today_snaps := today_snaps;
      END IF;
  
      awr_size_past24 := snap_size * est_today_snaps;
      
      DBMS_OUTPUT.PUT_LINE('<table width="90%" border="1">');
  
      DBMS_OUTPUT.PUT_LINE('<tr><th align="center" colspan="3">Estimates based on ' || ROUND(snapshot_interval/60) || ' minute snapshot intervals</th></tr>');
      DBMS_OUTPUT.PUT_LINE('<tr><td>AWR size/day</td><td align="right">'
                              || TO_CHAR(awr_average_size, mb_format)
                              || ' MB</td><td align="right">(' || TRIM(TO_CHAR(snap_size*1024, kb_format)) || ' K/snap * '
                              || ROUND(86400/snapshot_interval) || ' snaps/day)</td></tr>' );
      DBMS_OUTPUT.PUT_LINE('<tr><td>AWR size/wk</td><td align="right">'
                              || TO_CHAR(awr_average_size * 7, mb_format)
                              || ' MB</td><td align="right">(size_per_day * 7) per instance</td></tr>' );
      IF (num_instances > 1) THEN
          DBMS_OUTPUT.PUT_LINE('<tr><td>AWR size/wk</td><td align="right">'
                              || TO_CHAR(awr_average_size * 7 * num_instances, mb_format)
                              || ' MB</td><td align="right">(size_per_day * 7) per database</td></tr>' );
      END IF;
  
      DBMS_OUTPUT.PUT_LINE('<tr><th align="center" colspan="3">Estimates based on ' || ROUND(today_snaps) || ' snaps in past 24 hours</th></tr>');
  
      DBMS_OUTPUT.PUT_LINE('<tr><td>AWR size/day</td><td align="right">'
                              || TO_CHAR(awr_size_past24, mb_format)
                              || ' MB</td><td align="right">('
                              || TRIM(TO_CHAR(snap_size*1024, kb_format)) || ' K/snap and '
                              || ROUND(today_snaps) || ' snaps in past '
                              || ROUND(least(num_days*24,24),1) || ' hours)</td></tr>' );
      DBMS_OUTPUT.PUT_LINE('<tr><td>AWR size/wk</td><td align="right">'
                              || TO_CHAR(awr_size_past24 * 7, mb_format)
                              || ' MB</td><td align="right">(size_per_day * 7) per instance</td></tr>' );
      IF (num_instances > 1) THEN
          DBMS_OUTPUT.PUT_LINE('<tr><td>AWR size/wk</td><td align="right">'
                              || TO_CHAR(awr_size_past24 * 7 * num_instances, mb_format)
                              || ' MB</td><td align="right">(size_per_day * 7) per database</td></tr>' );
      END IF;
    
      DBMS_OUTPUT.PUT_LINE('</table>');
      
  END;
  /
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                              - AWR BASELINES -                             |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="awr_baselines"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>AWR Baselines</b></font><hr align="left" width="460">
  
  prompt Use the <b>DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE</b> procedure to create a named baseline.
  prompt A baseline (also known as a preserved snapshot set) is a pair of AWR snapshots that represents a
  prompt specific period of database usage. The Oracle database server will exempt the AWR snapshots 
  prompt assigned to a specific baseline from the automated purge routine. The main purpose of a baseline
  prompt is to preserve typical run-time statistics in the AWR repository which can then be compared to 
  prompt current performance or similar periods in the past.
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN dbbid            FORMAT a75    HEAD 'Database ID'              ENTMAP off
  COLUMN dbb_name         FORMAT a75    HEAD 'Database Name'            ENTMAP off
  COLUMN baseline_id                    HEAD 'Baseline ID'              ENTMAP off
  COLUMN baseline_name    FORMAT a75    HEAD 'Baseline Name'            ENTMAP off
  COLUMN start_snap_id                  HEAD 'Beginning Snapshot ID'    ENTMAP off
  COLUMN start_snap_time  FORMAT a75    HEAD 'Beginning Snapshot Time'  ENTMAP off
  COLUMN end_snap_id                    HEAD 'Ending Snapshot ID'       ENTMAP off
  COLUMN end_snap_time    FORMAT a75    HEAD 'Ending Snapshot Time'     ENTMAP off
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || b.dbid || '</b></font></div>'  dbbid
    , d.name                                                                          dbb_name
    , b.baseline_id                                                                   baseline_id
    , baseline_name                                                                   baseline_name
    , b.start_snap_id                                                                 start_snap_id
    , '<div nowrap align="right">' || TO_CHAR(b.start_snap_time, 'mm/dd/yyyy HH24:MI:SS')  || '</div>'  start_snap_time
    , b.end_snap_id                                                                   end_snap_id
    , '<div nowrap align="right">' || TO_CHAR(b.end_snap_time, 'mm/dd/yyyy HH24:MI:SS')  || '</div>'    end_snap_time
  FROM
      dba_hist_baseline   b
    , v$database          d
  WHERE
      b.dbid = d.dbid
  ORDER BY
      dbbid
    , b.baseline_id;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  -- +============================================================================+
  -- |                                                                            |
  -- |                      <<<<<     SESSIONS    >>>>>                           |
  -- |                                                                            |
  -- +============================================================================+
  
  
  prompt
  prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Sessions</u></b></font></center>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                          - CURRENT SESSIONS -                              |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="current_sessions"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Current Sessions</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN instance_name_print  FORMAT a45    HEADING 'Instance Name'              ENTMAP off
  COLUMN thread_number_print  FORMAT a45    HEADING 'Thread Number'              ENTMAP off
  COLUMN count                FORMAT a45    HEADING 'Current No. of Processes'   ENTMAP off
  COLUMN value                FORMAT a45    HEADING 'Max No. of Processes'       ENTMAP off
  COLUMN pct_usage            FORMAT a45    HEADING '% Usage'                    ENTMAP off
  
  SELECT
      '<div align="center"><font color="#336699"><b>' || a.instance_name  || '</b></font></div>'  instance_name_print
    , '<div align="center">' || a.thread#             || '</div>'  thread_number_print
    , '<div align="center">' || TO_CHAR(a.count)      || '</div>'  count
    , '<div align="center">' || b.value               || '</div>'  value
    , '<div align="center">' || TO_CHAR(ROUND(100*(a.count / b.value), 2)) || '%</div>'  pct_usage
  FROM
      (select   count(*) count, a1.inst_id, a2.instance_name, a2.thread#
       from     gv$session a1
              , gv$instance a2
       where    a1.inst_id = a2.inst_id
       group by a1.inst_id
              , a2.instance_name
              , a2.thread#) a
    , (select value, inst_id from gv$parameter where name='processes') b
  WHERE
      a.inst_id = b.inst_id
  ORDER BY
      a.instance_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                        - USER SESSION MATRIX -                             |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="user_session_matrix"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>User Session Matrix</b></font><hr align="left" width="460">
  
  prompt <b>User sessions (excluding SYS and background processes)</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN instance_name_print  FORMAT a75               HEADING 'Instance Name'            ENTMAP off
  COLUMN thread_number_print  FORMAT a75               HEADING 'Thread Number'            ENTMAP off
  COLUMN username             FORMAT a79               HEADING 'Oracle User'              ENTMAP off
  COLUMN num_user_sess        FORMAT 999,999,999,999   HEADING 'Total Number of Logins'   ENTMAP off
  COLUMN count_a              FORMAT 999,999,999       HEADING 'Active Logins'            ENTMAP off
  COLUMN count_i              FORMAT 999,999,999       HEADING 'Inactive Logins'          ENTMAP off
  COLUMN count_k              FORMAT 999,999,999       HEADING 'Killed Logins'            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"><font color="#336699"><b>' || i.thread#       || '</b></font></div>'                      thread_number_print
    , '<div align="left"><font color="#000000">' || NVL(sess.username, '[B.G. Process]') || '</font></div>' username
    , count(*)              num_user_sess
    , NVL(act.count, 0)     count_a
    , NVL(inact.count, 0)   count_i
    , NVL(killed.count, 0)  count_k
  FROM 
      gv$session                        sess
    , gv$instance                       i
    , (SELECT    count(*) count, NVL(username, '[B.G. Process]') username, inst_id
       FROM      gv$session
       WHERE     status = 'ACTIVE'
       GROUP BY  username, inst_id)              act
    , (SELECT    count(*) count, NVL(username, '[B.G. Process]') username, inst_id
       FROM      gv$session
       WHERE     status = 'INACTIVE'
       GROUP BY  username, inst_id)              inact
    , (SELECT    count(*) count, NVL(username, '[B.G. Process]') username, inst_id
       FROM      gv$session
       WHERE     status = 'KILLED'
       GROUP BY  username, inst_id)              killed
  WHERE
           sess.inst_id                         = i.inst_id
       AND (
             NVL(sess.username, '[B.G. Process]') = act.username (+)
             AND
             sess.inst_id  = act.inst_id (+)
           )
       AND (
             NVL(sess.username, '[B.G. Process]') = inact.username (+)
             AND
             sess.inst_id  = inact.inst_id (+)
           )
       AND (
             NVL(sess.username, '[B.G. Process]') = killed.username (+)
             AND
             sess.inst_id  = killed.inst_id (+)
           )
       AND sess.username NOT IN ('SYS')
  GROUP BY
      i.instance_name
    , i.thread#
    , sess.username
    , act.count
    , inact.count
    , killed.count
  ORDER BY
      i.instance_name
    , i.thread#
    , sess.username;
  
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                            - ENABLED TRACES -                              |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_enabled_traces"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Enabled Traces</b></font><hr align="left" width="460">
  
  prompt <b><u>End-to-End Application Tracing from View DBA_ENABLED_TRACES.</u></b>
  prompt   <li> <b>Trace Type:</b> Possible values are CLIENT_ID, SESSION, SERVICE, SERVICE_MODULE, SERVICE_MODULE_ACTION, and DATABASE, based on the type of tracing enabled.
  prompt   <li> <b>Primary ID:</b> Specific client identifier (username) or service name.
  prompt <p>
  
  prompt <b><u>Application tracing is enabled using the DBMS_MONITOR package and the following procedures:</u></b>
  prompt   <li> <b>CLIENT_ID_TRACE_ENABLE:</b> Enable tracing based on client identifier (username).
  prompt   <li> <b>CLIENT_ID_TRACE_DISABLE:</b> Disable client identifier tracing.
  prompt   <li> <b>SESSION_TRACE_ENABLE:</b> Enable tracing based on SID and SERIAL# of V$SESSION.
  prompt   <li> <b>SESSION_TRACE_DISABLE:</b> Disable session tracing.
  prompt   <li> <b>SERV_MOD_ACT_TRACE_ENABLE:</b> Enable tracing for a given combination of service name, module, and action.
  prompt   <li> <b>SERV_MOD_ACT_TRACE_DISABLE:</b> Disable service, module, and action tracing.
  prompt   <li> <b>DATABASE_TRACE_ENABLE:</b> Enable tracing for the entire database.
  prompt   <li> <b>DATABASE_TRACE_DISABLE:</b> Disable database tracing.
  prompt <p>
  
  prompt <b><font color="#ff0000">Hint</font>:</b> In a shared environment where you have more than one session to trace, it is 
  prompt possible to end up with many trace files when tracing is enabled (i.e. connection pools).
  prompt Oracle10<i>g</i> introduces the <b>trcsess</b> command-line utility to combine all the relevant
  prompt trace files based on a session or client identifier or the service name, module name, and
  prompt action name hierarchy combination. The output trace file from the trcsess command can then be
  prompt sent to tkprof for a formatted output. Type trcsess at the command-line without any arguments to
  prompt show the parameters and usage.
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN trace_type           FORMAT a75    HEADING 'Trace Type'         ENTMAP off
  COLUMN primary_id           FORMAT a75    HEADING 'Primary ID'         ENTMAP off
  COLUMN qualifier_id1        FORMAT a75    HEADING 'Module Name'        ENTMAP off
  COLUMN qualifier_id2        FORMAT a75    HEADING 'Action Name'        ENTMAP off
  COLUMN waits                FORMAT a75    HEADING 'Waits?'             ENTMAP off
  COLUMN binds                FORMAT a75    HEADING 'Binds?'             ENTMAP off
  COLUMN instance_name_print  FORMAT a75    HEADING 'Instance Name'      ENTMAP off
  
  SELECT
      '<div align="left"><font color="#336699"><b>'   || trace_type                 || '</b></font></div>' trace_type
    , '<div align="left">'                            || NVL(primary_id, '<br>')    || '</div>' primary_id
    , '<div align="left">'                            || NVL(qualifier_id1, '<br>') || '</div>' qualifier_id1
    , '<div align="left">'                            || NVL(qualifier_id2, '<br>') || '</div>' qualifier_id2
    , '<div align="center">'                          || waits                      || '</div>' waits
    , '<div align="center">'                          || binds                      || '</div>' binds
    , '<div align="left">'                            || NVL(instance_name, '<br>') || '</div>' instance_name_print
  FROM
      dba_enabled_traces
  ORDER BY
      trace_type
    , primary_id
    , qualifier_id1
    , qualifier_id2;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                         - ENABLED AGGREGATIONS -                           |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_enabled_aggregations"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Enabled Aggregations</b></font><hr align="left" width="460">
  
  prompt <b><u>Statistics Aggregation from View DBA_ENABLED_AGGREGATIONS.</u></b>
  prompt   <li> <b>Aggregation Type:</b> Possible values are CLIENT_ID, SERVICE_MODULE, and SERVICE_MODULE_ACTION, based on the type of statistics being gathered.
  prompt   <li> <b>Primary ID:</b> Specific client identifier (username) or service name.
  prompt <p>
  
  prompt <b><u>Statistics aggregation is enabled using the DBMS_MONITOR package and the following procedures.</u></b>
  prompt Note that statistics gathering is global for the database and is persistent across instance starts
  prompt and restarts.
  prompt   <li> <b>CLIENT_ID_STAT_ENABLE:</b> Enable statistics gathering based on client identifier (username).
  prompt   <li> <b>CLIENT_ID_STAT_DISABLE:</b> Disable client identifier statistics gathering.
  prompt   <li> <b>SERV_MOD_ACT_STAT_ENABLE:</b> Enable statistics gathering for a given combination of service name, module, and action.
  prompt   <li> <b>SERV_MOD_ACT_STAT_DISABLE:</b> Disable service, module, and action statistics gathering.
  prompt <p>
  
  prompt <b><font color="#ff0000">Hint</font>:</b> While the DBA_ENABLED_AGGREGATIONS provides global statistics for currently enabled
  prompt statistics, several other views can be used to query statistics aggregation values: V$CLIENT_STATS,
  prompt V$SERVICE_STATS, V$SERV_MOD_ACT_STATS, and V$SERVICEMETRIC.
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN aggregation_type     FORMAT a75    HEADING 'Aggregation Type'   ENTMAP off
  COLUMN primary_id           FORMAT a75    HEADING 'Primary ID'         ENTMAP off
  COLUMN qualifier_id1        FORMAT a75    HEADING 'Module Name'        ENTMAP off
  COLUMN qualifier_id2        FORMAT a75    HEADING 'Action Name'        ENTMAP off
  
  SELECT
      '<div align="left"><font color="#336699"><b>'   || aggregation_type           || '</b></font></div>' aggregation_type
    , '<div align="left">'                            || NVL(primary_id, '<br>')    || '</div>' primary_id
    , '<div align="left">'                            || NVL(qualifier_id1, '<br>') || '</div>' qualifier_id1
    , '<div align="left">'                            || NVL(qualifier_id2, '<br>') || '</div>' qualifier_id2
  FROM
      dba_enabled_aggregations
  ORDER BY
      aggregation_type
    , primary_id
    , qualifier_id1
    , qualifier_id2;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  -- +============================================================================+
  -- |                                                                            |
  -- |                      <<<<<     SECURITY     >>>>>                          |
  -- |                                                                            |
  -- +============================================================================+
  
  
  prompt
  prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Security</u></b></font></center>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                             - USER ACCOUNTS -                              |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="user_accounts"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>User Accounts</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN username              FORMAT a75    HEAD 'Username'        ENTMAP off
  COLUMN account_status        FORMAT a75    HEAD 'Account Status'  ENTMAP off
  COLUMN expiry_date           FORMAT a75    HEAD 'Expire Date'     ENTMAP off
  COLUMN default_tablespace    FORMAT a75    HEAD 'Default Tbs.'    ENTMAP off
  COLUMN temporary_tablespace  FORMAT a75    HEAD 'Temp Tbs.'       ENTMAP off
  COLUMN created               FORMAT a75    HEAD 'Created On'      ENTMAP off
  COLUMN profile               FORMAT a75    HEAD 'Profile'         ENTMAP off
  COLUMN sysdba                FORMAT a75    HEAD 'SYSDBA'          ENTMAP off
  COLUMN sysoper               FORMAT a75    HEAD 'SYSOPER'         ENTMAP off
  
  SELECT distinct
      '<b><font color="#336699">' || a.username || '</font></b>'                                            username
    , DECODE(   a.account_status
              , 'OPEN'
              , '<div align="left"><b><font color="darkgreen">' || a.account_status || '</font></b></div>'
              , '<div align="left"><b><font color="#663300">'   || a.account_status || '</font></b></div>') account_status
    , '<div nowrap align="right">' || NVL(TO_CHAR(a.expiry_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'           expiry_date
    , a.default_tablespace                                                                                  default_tablespace
    , a.temporary_tablespace                                                                                temporary_tablespace
    , '<div nowrap align="right">' || TO_CHAR(a.created, 'mm/dd/yyyy HH24:MI:SS') || '</div>'               created
    , a.profile                                        profile
    , '<div nowrap align="center">' || NVL(DECODE(p.sysdba,'TRUE', 'TRUE',''), '<br>') || '</div>'   sysdba
    , '<div nowrap align="center">' || NVL(DECODE(p.sysoper,'TRUE','TRUE',''), '<br>') || '</div>'   sysoper
  FROM
      dba_users       a
    , v$pwfile_users  p
  WHERE
      p.username (+) = a.username 
  ORDER BY
      username;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                      - USERS WITH DBA PRIVILEGES -                         |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="users_with_dba_privileges"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Users With DBA Privileges</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN grantee        FORMAT a70   HEADING 'Grantee'         ENTMAP off
  COLUMN granted_role   FORMAT a35   HEADING 'Granted Role'    ENTMAP off
  COLUMN admin_option   FORMAT a75   HEADING 'Admin. Option?'  ENTMAP off
  COLUMN default_role   FORMAT a75   HEADING 'Default Role?'   ENTMAP off
  
  SELECT
      '<b><font color="#336699">' || grantee       || '</font></b>'  grantee
    , '<div align="center">'      || granted_role  || '</div>'  granted_role
    , DECODE(   admin_option
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || admin_option || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || admin_option || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || admin_option || '</b></font></div>')   admin_option
    , DECODE(   default_role
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || default_role || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || default_role || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || default_role || '</b></font></div>')   default_role
  FROM
      dba_role_privs
  WHERE
      granted_role = 'DBA'
  ORDER BY
      grantee
    , granted_role;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                                 - ROLES -                                  |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="roles"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Roles</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN role             FORMAT a70    HEAD 'Role Name'       ENTMAP off
  COLUMN grantee          FORMAT a35    HEAD 'Grantee'         ENTMAP off
  COLUMN admin_option     FORMAT a75    HEAD 'Admin Option?'   ENTMAP off
  COLUMN default_role     FORMAT a75    HEAD 'Default Role?'   ENTMAP off
  
  BREAK ON role
  
  SELECT
     '<b><font color="#336699">' ||  b.role         || '</font></b>'          role
    , a.grantee                                                               grantee
    , DECODE(   a.admin_option
              , null
              , '<br>'
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || a.admin_option || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || a.admin_option || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || a.admin_option || '</b></font></div>')   admin_option
    , DECODE(   a.default_role
              , null
              , '<br>'
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || a.default_role || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || a.default_role || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || a.default_role || '</b></font></div>')   default_role
  FROM
      dba_role_privs  a
    , dba_roles       b
  WHERE
      granted_role(+) = b.role
  ORDER BY
      b.role
    , a.grantee;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                          - DEFAULT PASSWORDS -                             |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="default_passwords"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Default Passwords</b></font><hr align="left" width="460">
  
  prompt <b>User(s) with default password</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN username                      HEADING 'Username'        ENTMAP off
  COLUMN account_status   FORMAT a75   HEADING 'Account Status'  ENTMAP off
  
  SELECT
      '<b><font color="#336699">' || username        || '</font></b>'        username
    , DECODE(   account_status
              , 'OPEN'
              , '<div align="left"><b><font color="darkgreen">' || account_status || '</font></b></div>'
              , '<div align="left"><b><font color="#663300">'   || account_status || '</font></b></div>') account_status
  FROM dba_users
  WHERE password IN (
      'E066D214D5421CCC'   -- dbsnmp
    , '24ABAB8B06281B4C'   -- ctxsys
    , '72979A94BAD2AF80'   -- mdsys
    , 'C252E8FA117AF049'   -- odm
    , 'A7A32CD03D3CE8D5'   -- odm_mtr
    , '88A2B2C183431F00'   -- ordplugins
    , '7EFA02EC7EA6B86F'   -- ordsys
    , '4A3BA55E08595C81'   -- outln
    , 'F894844C34402B67'   -- scott
    , '3F9FBD883D787341'   -- wk_proxy
    , '79DF7A1BD138CF11'   -- wk_sys
    , '7C9BA362F8314299'   -- wmsys
    , '88D8364765FCE6AF'   -- xdb
    , 'F9DA8977092B7B81'   -- tracesvr
    , '9300C0977D7DC75E'   -- oas_public
    , 'A97282CE3D94E29E'   -- websys
    , 'AC9700FD3F1410EB'   -- lbacsys
    , 'E7B5D92911C831E1'   -- rman
    , 'AC98877DE1297365'   -- perfstat
    , 'D4C5016086B2DC6A'   -- sys
    , 'D4DF7931AB130E37')  -- system
  ORDER BY
      username;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                              - DB LINKS -                                  |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="db_links"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>DB Links</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner        FORMAT a75    HEADING 'Owner'           ENTMAP off
  COLUMN db_link      FORMAT a75    HEADING 'DB Link Name'    ENTMAP off
  COLUMN username                   HEADING 'Username'        ENTMAP off
  COLUMN host                       HEADING 'Host'            ENTMAP off
  COLUMN created      FORMAT a75    HEADING 'Created'         ENTMAP off
  
  BREAK ON owner
  
  SELECT
      '<b><font color="#336699">' || owner || '</font></b>'  owner
    , db_link
    , username
    , host
    , '<div nowrap align="right">' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '</div>' created
  FROM dba_db_links
  ORDER BY owner, db_link;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  -- +============================================================================+
  -- |                                                                            |
  -- |                     <<<<<     OBJECTS     >>>>>                            |
  -- |                                                                            |
  -- +============================================================================+
  
  
  prompt
  prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Objects</u></b></font></center>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                            - OBJECT SUMMARY -                              |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="object_summary"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Object Summary</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner           FORMAT a60               HEADING 'Owner'           ENTMAP off
  COLUMN object_type     FORMAT a25               HEADING 'Object Type'     ENTMAP off
  COLUMN obj_count       FORMAT 999,999,999,999   HEADING 'Object Count'    ENTMAP off
  
  BREAK ON report ON owner SKIP 2
  -- compute sum label ""               of obj_count on owner
  -- compute sum label '<font color="#990000"><b>Grand Total: </b></font>' of obj_count on report
  COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' OF obj_count ON report
  
  SELECT
      '<b><font color="#336699">' || owner || '</font></b>'  owner
    , object_type                                            object_type
    , count(*)                                               obj_count
  FROM
      dba_objects
  GROUP BY
      owner
    , object_type
  ORDER BY
      owner
    , object_type;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                          - SEGMENT SUMMARY -                               |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="segment_summary"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Segment Summary</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner           FORMAT a50                    HEADING 'Owner'             ENTMAP off
  COLUMN segment_type    FORMAT a25                    HEADING 'Segment Type'      ENTMAP off
  COLUMN seg_count       FORMAT 999,999,999,999        HEADING 'Segment Count'     ENTMAP off
  COLUMN bytes           FORMAT 999,999,999,999,999    HEADING 'Size (in Bytes)'   ENTMAP off
  
  BREAK ON report ON owner SKIP 2
  -- COMPUTE sum LABEL ""                                                  OF seg_count bytes ON owner
  COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' OF seg_count bytes ON report
  
  SELECT
      '<b><font color="#336699">' || owner || '</font></b>'  owner
    , segment_type        segment_type
    , count(*)            seg_count
    , sum(bytes)          bytes
  FROM
      dba_segments
  GROUP BY
      owner
    , segment_type
  ORDER BY
      owner
    , segment_type;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                    - TOP 100 SEGMENTS (BY SIZE) -                          |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="top_100_segments_by_size"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Top 100 Segments (by size)</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                                               HEADING 'Owner'            ENTMAP off
  COLUMN segment_name                                        HEADING 'Segment Name'     ENTMAP off
  COLUMN partition_name                                      HEADING 'Partition Name'   ENTMAP off
  COLUMN segment_type                                        HEADING 'Segment Type'     ENTMAP off
  COLUMN tablespace_name                                     HEADING 'Tablespace Name'  ENTMAP off
  COLUMN bytes               FORMAT 999,999,999,999,999,999  HEADING 'Size (in bytes)'  ENTMAP off
  COLUMN extents             FORMAT 999,999,999,999,999,999  HEADING 'Extents'          ENTMAP off
  
  BREAK ON report
  COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' OF bytes extents ON report
  
  SELECT
      a.owner
    , a.segment_name
    , a.partition_name
    , a.segment_type
    , a.tablespace_name
    , a.bytes
    , a.extents
  FROM
      (select
           b.owner
         , b.segment_name
         , b.partition_name
         , b.segment_type
         , b.tablespace_name
         , b.bytes
         , b.extents
       from
           dba_segments b
       order by
           b.bytes desc
      ) a
  WHERE
      rownum < 100;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                      - TOP 100 SEGMENTS (BY EXTENTS) -                     |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="top_100_segments_by_extents"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Top 100 Segments (by number of extents)</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                                               HEADING 'Owner'            ENTMAP off
  COLUMN segment_name                                        HEADING 'Segment Name'     ENTMAP off
  COLUMN partition_name                                      HEADING 'Partition Name'   ENTMAP off
  COLUMN segment_type                                        HEADING 'Segment Type'     ENTMAP off
  COLUMN tablespace_name                                     HEADING 'Tablespace Name'  ENTMAP off
  COLUMN extents             FORMAT 999,999,999,999,999,999  HEADING 'Extents'          ENTMAP off
  COLUMN bytes               FORMAT 999,999,999,999,999,999  HEADING 'Size (in bytes)'  ENTMAP off
  
  BREAK ON report
  COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' OF extents bytes ON report
  
  SELECT
      a.owner
    , a.segment_name
    , a.partition_name
    , a.segment_type
    , a.tablespace_name
    , a.extents
    , a.bytes
  FROM
      (select
           b.owner
         , b.segment_name
         , b.partition_name
         , b.segment_type
         , b.tablespace_name
         , b.bytes
         , b.extents
       from
           dba_segments b
       order by
           b.extents desc
      ) a
  WHERE
      rownum < 100;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                           - DIRECTORIES -                                  |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_directories"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Directories</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner             FORMAT a75  HEADING 'Owner'             ENTMAP off
  COLUMN directory_name    FORMAT a75  HEADING 'Directory Name'    ENTMAP off
  COLUMN directory_path                HEADING 'Directory Path'    ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || owner          || '</b></font></div>'  owner
    , '<b><font color="#663300">'                   || directory_name || '</font></b>'        directory_name
    , '<tt>' || directory_path || '</tt>' directory_path
  FROM
      dba_directories
  ORDER BY
      owner
    , directory_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                        - DIRECTORY PRIVILEGES -                            |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_directory_privileges"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Directory Privileges</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN table_name    FORMAT a75      HEADING 'Directory Name'    ENTMAP off
  COLUMN grantee       FORMAT a75      HEADING 'Grantee'           ENTMAP off
  COLUMN privilege     FORMAT a75      HEADING 'Privilege'         ENTMAP off
  COLUMN grantable     FORMAT a75      HEADING 'Grantable?'        ENTMAP off
  
  BREAK ON report ON table_name ON grantee
  
  SELECT
      '<b><font color="#336699">' || table_name || '</font></b>'  table_name
    , '<b><font color="#663300">' || grantee    || '</font></b>'  grantee
    , privilege                                                   privilege
    , DECODE(   grantable
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || grantable || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || grantable || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || grantable || '</b></font></div>')   grantable
  FROM
      dba_tab_privs
  WHERE
      privilege IN ('READ', 'WRITE')
  ORDER BY
      table_name
    , grantee
    , privilege;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                             - LIBRARIES -                                  |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_libraries"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Libraries</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner          FORMAT a75    HEADING 'Owner'             ENTMAP off
  COLUMN library_name   FORMAT a75    HEADING 'Library Name'      ENTMAP off
  COLUMN file_spec                    HEADING 'File Spec'         ENTMAP off
  COLUMN dynamic        FORMAT a75    HEADING 'Dynamic?'          ENTMAP off
  COLUMN status         FORMAT a75    HEADING 'Status'            ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || owner           || '</b></font></div>'  owner
    , '<b><font color="#663300">'                   || library_name    || '</font></b>'        library_name
    , file_spec                                                                                file_spec
    , '<div align="center">' || dynamic || '</div>'                                            dynamic
    , DECODE(   status
              , 'VALID'
              , '<div align="center"><font color="darkgreen"><b>' || status || '</b></font></div>'
              , '<div align="center"><font color="#990000"><b>'   || status || '</b></font></div>' ) status
  FROM
      dba_libraries
  ORDER BY
      owner
    , library_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                               - TYPES -                                    |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_types"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Types</b></font><hr align="left" width="460">
  
  prompt <b>Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner              FORMAT a75        HEADING 'Owner'              ENTMAP off
  COLUMN type_name          FORMAT a75        HEADING 'Type Name'          ENTMAP off
  COLUMN typecode           FORMAT a75        HEADING 'Type Code'          ENTMAP off
  COLUMN attributes         FORMAT a75        HEADING 'Num. Attributes'    ENTMAP off
  COLUMN methods            FORMAT a75        HEADING 'Num. Methods'       ENTMAP off
  COLUMN predefined         FORMAT a75        HEADING 'Predefined?'        ENTMAP off
  COLUMN incomplete         FORMAT a75        HEADING 'Incomplete?'        ENTMAP off
  COLUMN final              FORMAT a75        HEADING 'Final?'             ENTMAP off
  COLUMN instantiable       FORMAT a75        HEADING 'Instantiable?'      ENTMAP off
  COLUMN supertype_owner    FORMAT a75        HEADING 'Super Owner'        ENTMAP off
  COLUMN supertype_name     FORMAT a75        HEADING 'Super Name'         ENTMAP off
  COLUMN local_attributes   FORMAT a75        HEADING 'Local Attributes'   ENTMAP off
  COLUMN local_methods      FORMAT a75        HEADING 'Local Methods'      ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || t.owner || '</b></font></div>'    owner
    , '<div nowrap>'                || t.type_name                                          || '</div>'   type_name
    , '<div nowrap>'                || t.typecode                                           || '</div>'   typecode
    , '<div nowrap align="right">'  || TO_CHAR(t.attributes, '999,999')                     || '</div>'   attributes
    , '<div nowrap align="right">'  || TO_CHAR(t.methods, '999,999')                        || '</div>'   methods
    , '<div nowrap align="center">' || t.predefined                                         || '</div>'   predefined
    , '<div nowrap align="center">' || t.incomplete                                         || '</div>'   incomplete
    , '<div nowrap align="center">' || t.final                                              || '</div>'   final
    , '<div nowrap align="center">' || t.instantiable                                       || '</div>'   instantiable
    , '<div nowrap align="left">'   || NVL(t.supertype_owner, '<br>')                       || '</div>'   supertype_owner
    , '<div nowrap align="left">'   || NVL(t.supertype_name, '<br>')                        || '</div>'   supertype_name
    , '<div nowrap align="right">'  || NVL(TO_CHAR(t.local_attributes, '999,999'), '<br>')  || '</div>'   local_attributes
    , '<div nowrap align="right">'  || NVL(TO_CHAR(t.local_methods, '999,999'), '<br>')     || '</div>'   local_methods
  FROM
      dba_types  t
  WHERE
      t.owner NOT IN (    'CTXSYS'
                        , 'DBSNMP'
                        , 'DMSYS'
                        , 'EXFSYS'
                        , 'IX'
                        , 'LBACSYS'
                        , 'MDSYS'
                        , 'OLAPSYS'
                        , 'ORDSYS'
                        , 'OUTLN'
                        , 'SYS'
                        , 'SYSMAN'
                        , 'SYSTEM'
                        , 'WKSYS'
                        , 'WMSYS'
                        , 'XDB')
  ORDER BY
      t.owner
    , t.type_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                           - TYPE ATTRIBUTES -                              |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_type_attributes"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Type Attributes</b></font><hr align="left" width="460">
  
  prompt <b>Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                FORMAT a75        HEADING 'Owner'                ENTMAP off
  COLUMN type_name            FORMAT a75        HEADING 'Type Name'            ENTMAP off
  COLUMN typecode             FORMAT a75        HEADING 'Type Code'            ENTMAP off
  COLUMN attribute_name       FORMAT a75        HEADING 'Attribute Name'       ENTMAP off
  COLUMN attribute_datatype   FORMAT a75        HEADING 'Attribute Data Type'  ENTMAP off
  COLUMN inherited            FORMAT a75        HEADING 'Inherited?'           ENTMAP off
  
  BREAK ON report ON owner ON type_name ON typecode
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || t.owner || '</b></font></div>'    owner
    , '<div nowrap>' || t.type_name   || '</div>'               type_name
    , '<div nowrap>' || t.typecode    || '</div>'               typecode
    , '<div nowrap>' || a.attr_name   || '</div>'               attribute_name
    , (CASE
         WHEN (a.length IS NOT NULL)
             THEN a.attr_type_name || '(' || a.length || ')'
         WHEN (a.attr_type_name='NUMBER' AND (a.precision IS NOT NULL AND a.scale IS NOT NULL))
             THEN a.attr_type_name || '(' || a.precision || ',' || a.scale || ')'
         WHEN (a.attr_type_name='NUMBER' AND (a.precision IS NOT NULL AND a.scale IS NULL))
             THEN a.attr_type_name || '(' || a.precision || ')'
         ELSE
             a.attr_type_name
       END)                                                     attribute_datatype
    , DECODE(   a.inherited
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || a.inherited || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || a.inherited || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || a.inherited || '</b></font></div>')   inherited
  FROM
      dba_types        t
    , dba_type_attrs   a
  WHERE
        t.owner       = a.owner
    AND t.type_name   = a.type_name
    AND t.owner NOT IN (    'CTXSYS'
                          , 'DBSNMP'
                          , 'DMSYS'
                          , 'EXFSYS'
                          , 'IX'
                          , 'LBACSYS'
                          , 'MDSYS'
                          , 'OLAPSYS'
                          , 'ORDSYS'
                          , 'OUTLN'
                          , 'SYS'
                          , 'SYSMAN'
                          , 'SYSTEM'
                          , 'WKSYS'
                          , 'WMSYS'
                          , 'XDB')
  ORDER BY
      t.owner
    , t.type_name
    , t.typecode
    , a.attr_no;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                             - TYPE METHODS -                               |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_type_methods"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Type Methods</b></font><hr align="left" width="460">
  
  prompt <b>Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner              FORMAT a75        HEADING 'Owner'              ENTMAP off
  COLUMN type_name          FORMAT a75        HEADING 'Type Name'          ENTMAP off
  COLUMN typecode           FORMAT a75        HEADING 'Type Code'          ENTMAP off
  COLUMN method_name        FORMAT a75        HEADING 'Method Name'        ENTMAP off
  COLUMN method_type        FORMAT a75        HEADING 'Method Type'        ENTMAP off
  COLUMN num_parameters     FORMAT a75        HEADING 'Num. Parameters'    ENTMAP off
  COLUMN results            FORMAT a75        HEADING 'Results'            ENTMAP off
  COLUMN final              FORMAT a75        HEADING 'Final?'             ENTMAP off
  COLUMN instantiable       FORMAT a75        HEADING 'Instantiable?'      ENTMAP off
  COLUMN overriding         FORMAT a75        HEADING 'Overriding?'        ENTMAP off
  COLUMN inherited          FORMAT a75        HEADING 'Inherited?'         ENTMAP off
  
  BREAK ON report ON owner ON type_name ON typecode
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || t.owner || '</b></font></div>'    owner
    , '<div nowrap>'                || t.type_name                       || '</div>'  type_name
    , '<div nowrap>'                || t.typecode                        || '</div>'  typecode
    , '<div nowrap>'                || m.method_name                     || '</div>'  method_name
    , '<div nowrap>'                || m.method_type                     || '</div>'  method_type
    , '<div nowrap align="right">'  || TO_CHAR(m.parameters, '999,999')  || '</div>'  num_parameters
    , '<div nowrap align="right">'  || TO_CHAR(m.results, '999,999')     || '</div>'  results
    , '<div nowrap align="center">' || m.final                           || '</div>'  final
    , '<div nowrap align="center">' || m.instantiable                    || '</div>'  instantiable
    , '<div nowrap align="center">' || m.overriding                      || '</div>'  overriding
    , DECODE(   m.inherited
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || m.inherited || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || m.inherited || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || m.inherited || '</b></font></div>')   inherited
  FROM
      dba_types          t
    , dba_type_methods   m
  WHERE
        t.owner       = m.owner
    AND t.type_name   = m.type_name
    AND t.owner NOT IN (    'CTXSYS'
                          , 'DBSNMP'
                          , 'DMSYS'
                          , 'EXFSYS'
                          , 'IX'
                          , 'LBACSYS'
                          , 'MDSYS'
                          , 'OLAPSYS'
                          , 'ORDSYS'
                          , 'OUTLN'
                          , 'SYS'
                          , 'SYSMAN'
                          , 'SYSTEM'
                          , 'WKSYS'
                          , 'WMSYS'
                          , 'XDB')
  ORDER BY
      t.owner
    , t.type_name
    , t.typecode
    , m.method_no;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                              - COLLECTIONS -                               |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_collections"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Collections</b></font><hr align="left" width="460">
  
  prompt <b>Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                FORMAT a75        HEADING 'Owner'              ENTMAP off
  COLUMN type_name            FORMAT a75        HEADING 'Type Name'          ENTMAP off
  COLUMN coll_type            FORMAT a75        HEADING 'Collection Type'    ENTMAP off
  COLUMN upper_bound          FORMAT a75        HEADING 'VARRAY Limit'       ENTMAP off
  COLUMN elem_type_owner      FORMAT a75        HEADING 'Element Type Owner' ENTMAP off
  COLUMN elem_datatype        FORMAT a75        HEADING 'Element Data Type'  ENTMAP off
  COLUMN character_set_name   FORMAT a75        HEADING 'Character Set'      ENTMAP off
  COLUMN elem_storage         FORMAT a75        HEADING 'Element Storage'    ENTMAP off
  COLUMN nulls_stored         FORMAT a75        HEADING 'Nulls Stored?'      ENTMAP off
  
  BREAK ON report ON owner ON type_name
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || c.owner || '</b></font></div>'    owner
    , '<div nowrap>'                  || c.type_name                                           || '</div>'  type_name
    , '<div nowrap>'                  || c.coll_type                                           || '</div>'  coll_type
    , '<div nowrap align="right">'    || NVL(TO_CHAR(c.upper_bound, '9,999,999,999'), '<br>')  || '</div>'  upper_bound
    , '<div nowrap>'                  || NVL(c.elem_type_owner, '<br>')                        || '</div>'  elem_type_owner
    , (CASE
         WHEN (c.length IS NOT NULL)
             THEN c.elem_type_name || '(' || c.length || ')'
         WHEN (c.elem_type_name='NUMBER' AND (c.precision IS NOT NULL AND c.scale IS NOT NULL))
             THEN c.elem_type_name || '(' || c.precision || ',' || c.scale || ')'
         WHEN (c.elem_type_name='NUMBER' AND (c.precision IS NOT NULL AND c.scale IS NULL))
             THEN c.elem_type_name || '(' || c.precision || ')'
         ELSE
             c.elem_type_name
       END)                                    elem_datatype
    , '<div nowrap>'                  || NVL(c.character_set_name, '<br>')                     || '</div>'  character_set_name
    , '<div nowrap>'                  || NVL(c.elem_storage, '<br>')                           || '</div>'  elem_storage
    , DECODE(   c.nulls_stored
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || c.nulls_stored || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || c.nulls_stored || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || c.nulls_stored || '</b></font></div>')   nulls_stored
  FROM
      dba_coll_types  c
  WHERE
      c.owner NOT IN (    'CTXSYS'
                        , 'DBSNMP'
                        , 'DMSYS'
                        , 'EXFSYS'
                        , 'IX'
                        , 'LBACSYS'
                        , 'MDSYS'
                        , 'OLAPSYS'
                        , 'ORDSYS'
                        , 'OUTLN'
                        , 'SYS'
                        , 'SYSMAN'
                        , 'SYSTEM'
                        , 'WKSYS'
                        , 'WMSYS'
                        , 'XDB')
  ORDER BY
      c.owner
    , c.type_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                           - LOB SEGMENTS -                                 |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_lob_segments"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>LOB Segments</b></font><hr align="left" width="460">
  
  prompt <b>Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner              FORMAT a85        HEADING 'Owner'              ENTMAP off
  COLUMN table_name         FORMAT a75        HEADING 'Table Name'         ENTMAP off
  COLUMN column_name        FORMAT a75        HEADING 'Column Name'        ENTMAP off
  COLUMN segment_name       FORMAT a125       HEADING 'LOB Segment Name'   ENTMAP off
  COLUMN tablespace_name    FORMAT a75        HEADING 'Tablespace Name'    ENTMAP off
  COLUMN lob_segment_bytes  FORMAT a75        HEADING 'Segment Size'       ENTMAP off
  COLUMN index_name         FORMAT a125       HEADING 'LOB Index Name'     ENTMAP off
  COLUMN in_row             FORMAT a75        HEADING 'In Row?'            ENTMAP off
  
  BREAK ON report ON owner ON table_name
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || l.owner || '</b></font></div>'    owner
    , '<div nowrap>' || l.table_name        || '</div>'       table_name
    , '<div nowrap>' || l.column_name       || '</div>'       column_name
    , '<div nowrap>' || l.segment_name      || '</div>'       segment_name
    , '<div nowrap>' || s.tablespace_name   || '</div>'       tablespace_name
    , '<div nowrap align="right">' || TO_CHAR(s.bytes, '999,999,999,999,999') || '</div>'  lob_segment_bytes
    , '<div nowrap>' || l.index_name        || '</div>'       index_name
    , DECODE(   l.in_row
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || l.in_row || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || l.in_row || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || l.in_row || '</b></font></div>')   in_row
  FROM
      dba_lobs     l
    , dba_segments s
  WHERE
        l.owner = s.owner
    AND l.segment_name = s.segment_name
    AND l.owner NOT IN (    'CTXSYS'
                          , 'DBSNMP'
                          , 'DMSYS'
                          , 'EXFSYS'
                          , 'IX'
                          , 'LBACSYS'
                          , 'MDSYS'
                          , 'OLAPSYS'
                          , 'ORDSYS'
                          , 'OUTLN'
                          , 'SYS'
                          , 'SYSMAN'
                          , 'SYSTEM'
                          , 'WKSYS'
                          , 'WMSYS'
                          , 'XDB')
  ORDER BY
      l.owner
    , l.table_name
    , l.column_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                      - OBJECTS UNABLE TO EXTEND -                          |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="objects_unable_to_extend"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Objects Unable to Extend</b></font><hr align="left" width="460">
  
  prompt <b>Segments that cannot extend because of MAXEXTENTS or not enough space</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner             FORMAT a75                  HEADING 'Owner'            ENTMAP off
  COLUMN tablespace_name                               HEADING 'Tablespace Name'  ENTMAP off
  COLUMN segment_name                                  HEADING 'Segment Name'     ENTMAP off
  COLUMN segment_type                                  HEADING 'Segment Type'     ENTMAP off
  COLUMN next_extent       FORMAT 999,999,999,999,999  HEADING 'Next Extent'      ENTMAP off
  COLUMN max               FORMAT 999,999,999,999,999  HEADING 'Max. Piece Size'  ENTMAP off
  COLUMN sum               FORMAT 999,999,999,999,999  HEADING 'Sum of Bytes'     ENTMAP off
  COLUMN extents           FORMAT 999,999,999,999,999  HEADING 'Num. of Extents'  ENTMAP off
  COLUMN max_extents       FORMAT 999,999,999,999,999  HEADING 'Max Extents'      ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || ds.owner || '</b></font></div>'    owner
    , ds.tablespace_name    tablespace_name
    , ds.segment_name       segment_name
    , ds.segment_type       segment_type
    , ds.next_extent        next_extent
    , NVL(dfs.max, 0)       max
    , NVL(dfs.sum, 0)       sum
    , ds.extents            extents
    , ds.max_extents        max_extents
  FROM 
      dba_segments ds
    , (select
           max(bytes) max
         , sum(bytes) sum
         , tablespace_name
       from
           dba_free_space 
       group by
           tablespace_name
      ) dfs
  WHERE
        (ds.next_extent > nvl(dfs.max, 0)
         OR
         ds.extents >= ds.max_extents)
    AND ds.tablespace_name = dfs.tablespace_name (+)
    AND ds.owner NOT IN ('SYS','SYSTEM')
  ORDER BY
      ds.owner
    , ds.tablespace_name
    , ds.segment_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |               - OBJECTS WHICH ARE NEARING MAXEXTENTS -                     |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="objects_which_are_nearing_maxextents"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Objects Which Are Nearing MAXEXTENTS</b></font><hr align="left" width="460">
  
  prompt <b>Segments where number of EXTENTS is less than 1/2 of MAXEXTENTS</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner             FORMAT a75                   HEADING 'Owner'             ENTMAP off
  COLUMN tablespace_name   FORMAT a30                   HEADING 'Tablespace name'   ENTMAP off
  COLUMN segment_name      FORMAT a30                   HEADING 'Segment Name'      ENTMAP off
  COLUMN segment_type      FORMAT a20                   HEADING 'Segment Type'      ENTMAP off
  COLUMN bytes             FORMAT 999,999,999,999,999   HEADING 'Size (in bytes)'   ENTMAP off
  COLUMN next_extent       FORMAT 999,999,999,999,999   HEADING 'Next Extent Size'  ENTMAP off
  COLUMN pct_increase                                   HEADING '% Increase'        ENTMAP off
  COLUMN extents           FORMAT 999,999,999,999,999   HEADING 'Num. of Extents'   ENTMAP off
  COLUMN max_extents       FORMAT 999,999,999,999,999   HEADING 'Max Extents'       ENTMAP off
  COLUMN pct_util          FORMAT a35                   HEADING '% Utilized'        ENTMAP off
  
  SELECT
      owner
    , tablespace_name
    , segment_name
    , segment_type
    , bytes
    , next_extent
    , pct_increase
    , extents
    , max_extents
    , '<div align="right">' || ROUND((extents/max_extents)*100, 2) || '%</div>'   pct_util
  FROM
      dba_segments
  WHERE
        extents > max_extents/2
    AND max_extents != 0
  ORDER BY
      (extents/max_extents) DESC;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                          - INVALID OBJECTS -                               |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="invalid_objects"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Invalid Objects</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner           FORMAT a85         HEADING 'Owner'         ENTMAP off
  COLUMN object_name     FORMAT a30         HEADING 'Object Name'   ENTMAP off
  COLUMN object_type     FORMAT a20         HEADING 'Object Type'   ENTMAP off
  COLUMN status          FORMAT a75         HEADING 'Status'        ENTMAP off
  
  BREAK ON report ON owner
  COMPUTE count LABEL '<font color="#990000"><b>Grand Total: </b></font>' OF object_name ON report
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || owner || '</b></font></div>'    owner
    , object_name
    , object_type
    , DECODE(   status
              , 'VALID'
              , '<div align="center"><font color="darkgreen"><b>' || status || '</b></font></div>'
              , '<div align="center"><font color="#990000"><b>'   || status || '</b></font></div>' ) status
  FROM dba_objects
  WHERE status <> 'VALID'
  ORDER BY
      owner
    , object_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                     - PROCEDURAL OBJECT ERRORS -                           |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="procedural_object_errors"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Procedural Object Errors</b></font><hr align="left" width="460">
  
  prompt <b>All records from DBA_ERRORS</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                FORMAT a85      HEAD 'Schema'        ENTMAP off
  COLUMN name                 FORMAT a30      HEAD 'Object Name'   ENTMAP off
  COLUMN type                 FORMAT a15      HEAD 'Object Type'   ENTMAP off
  COLUMN sequence             FORMAT 999,999  HEAD 'Sequence'      ENTMAP off
  COLUMN line                 FORMAT 999,999  HEAD 'Line'          ENTMAP off
  COLUMN position             FORMAT 999,999  HEAD 'Position'      ENTMAP off
  COLUMN text                                 HEAD 'Text'          ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || owner || '</b></font></div>'    owner
    , name
    , type
    , sequence
    , line
    , position
    , text
  FROM
      dba_errors
  ORDER BY
      1
    , 2
    , 3;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                     - OBJECTS WITHOUT STATISTICS -                         |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="objects_without_statistics"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Objects Without Statistics</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner            FORMAT a95                HEAD 'Owner'            ENTMAP off
  COLUMN object_type      FORMAT a20                HEAD 'Object Type'      ENTMAP off
  COLUMN count            FORMAT 999,999,999,999    HEAD 'Count'            ENTMAP off
  
  BREAK ON report ON owner
  COMPUTE count LABEL '<font color="#990000"><b>Total: </b></font>' OF object_name ON report
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || owner || '</b></font></div>'        owner
    , 'Table'                                                                                     object_type
    , count(*)                                                                                    count
  FROM
      sys.dba_tables 
  WHERE
        last_analyzed IS NULL 
    AND owner NOT IN ('SYS','SYSTEM') 
    AND partitioned = 'NO'
  GROUP BY
      owner
    , 'Table'
  UNION 
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || owner || '</b></font></div>'        owner
    , 'Index'                                                                                     object_type
    , count(*)                                                                                    count
  FROM
      sys.dba_indexes 
  WHERE
        last_analyzed IS NULL 
    AND owner NOT IN ('SYS','SYSTEM') 
    AND partitioned = 'NO'
  GROUP BY
      owner
    , 'Index'
  UNION 
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || table_owner || '</b></font></div>'  owner
    , 'Table Partition'                                                                           object_type
    , count(*)                                                                                    count
  FROM
      sys.dba_tab_partitions 
  WHERE
        last_analyzed IS NULL 
    AND table_owner NOT IN ('SYS','SYSTEM')
  GROUP BY
      table_owner
    , 'Table Partition'
  UNION 
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || index_owner || '</b></font></div>'  owner
    , 'Index Partition'                                                                           object_type
    , count(*)                                                                                    count
  FROM
      sys.dba_ind_partitions 
  WHERE
        last_analyzed IS NULL 
    AND index_owner NOT IN ('SYS','SYSTEM')
  GROUP BY
      index_owner
    , 'Index Partition'
  ORDER BY
      1
    , 2
    , 3;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |           - TABLES SUFFERING FROM ROW CHAINING/MIGRATION -                 |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="tables_suffering_from_row_chaining_migration"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tables Suffering From Row Chaining/Migration</b></font><hr align="left" width="460">
  
  prompt <b><font color="#990000">NOTE</font>: Tables must have statistics gathered</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                                          HEADING 'Owner'           ENTMAP off
  COLUMN table_name                                     HEADING 'Table Name'      ENTMAP off
  COLUMN partition_name                                 HEADING 'Partition Name'  ENTMAP off
  COLUMN num_rows           FORMAT 999,999,999,999,999  HEADING 'Total Rows'      ENTMAP off
  COLUMN pct_chained_rows   FORMAT a65                  HEADING '% Chained Rows'  ENTMAP off
  COLUMN avg_row_length     FORMAT 999,999,999,999,999  HEADING 'Avg Row Length'  ENTMAP off
  
  SELECT
      owner                               owner
    , table_name                          table_name
    , ''                                  partition_name
    , num_rows                            num_rows
    , '<div align="right">' || ROUND((chain_cnt/num_rows)*100, 2) || '%</div>' pct_chained_rows
    , avg_row_len                         avg_row_length
  FROM
      (select
           owner
         , table_name
         , chain_cnt
         , num_rows
         , avg_row_len 
       from
           sys.dba_tables 
       where
             chain_cnt is not null 
         and num_rows is not null 
         and chain_cnt > 0 
         and num_rows > 0 
         and owner != 'SYS')  
  UNION ALL 
  SELECT
      table_owner                         owner
    , table_name                          table_name
    , partition_name                      partition_name
    , num_rows                            num_rows
    , '<div align="right">' || ROUND((chain_cnt/num_rows)*100, 2) || '%</div>' pct_chained_rows
    , avg_row_len                         avg_row_length
  FROM
      (select
           table_owner
         , table_name
         , partition_name
         , chain_cnt
         , num_rows
         , avg_row_len 
       from
           sys.dba_tab_partitions 
       where
             chain_cnt is not null 
         and num_rows is not null 
         and chain_cnt > 0 
         and num_rows > 0 
         and table_owner != 'SYS') b 
  WHERE
      (chain_cnt/num_rows)*100 > 10;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |             - USERS WITH DEFAULT TABLESPACE - (SYSTEM) -                   |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="users_with_default_tablespace_defined_as_system"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Users With Default Tablespace - (SYSTEM)</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN username                 FORMAT a75    HEADING 'Username'                ENTMAP off
  COLUMN default_tablespace       FORMAT a125   HEADING 'Default Tablespace'      ENTMAP off
  COLUMN temporary_tablespace     FORMAT a125   HEADING 'Temporary Tablespace'    ENTMAP off
  COLUMN created                  FORMAT a75    HEADING 'Created'                 ENTMAP off
  COLUMN account_status           FORMAT a75    HEADING 'Account Status'          ENTMAP off
  
  SELECT
      '<font color="#336699"><b>' || username             || '</font></b>'                  username
    , '<div align="left">'        || default_tablespace   || '</div>'                       default_tablespace
    , '<div align="left">'        || temporary_tablespace || '</div>'                       temporary_tablespace
    , '<div align="right">'       || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '</div>'  created
    , DECODE(   account_status
              , 'OPEN'
              , '<div align="center"><b><font color="darkgreen">' || account_status || '</font></b></div>'
              , '<div align="center"><b><font color="#663300">'   || account_status || '</font></b></div>') account_status
  FROM
      dba_users
  WHERE
      default_tablespace = 'SYSTEM'
  ORDER BY
      username;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |          - USERS WITH DEFAULT TEMPORARY TABLESPACE - (SYSTEM) -            |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="users_with_default_temporary_tablespace_as_system"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Users With Default Temporary Tablespace - (SYSTEM)</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN username                 FORMAT a75    HEADING 'Username'                ENTMAP off
  COLUMN default_tablespace       FORMAT a125   HEADING 'Default Tablespace'      ENTMAP off
  COLUMN temporary_tablespace     FORMAT a125   HEADING 'Temporary Tablespace'    ENTMAP off
  COLUMN created                  FORMAT a75    HEADING 'Created'                 ENTMAP off
  COLUMN account_status           FORMAT a75    HEADING 'Account Status'          ENTMAP off
  
  SELECT
      '<font color="#336699"><b>'  || username             || '</font></b>'                  username
    , '<div align="center">'       || default_tablespace   || '</div>'                       default_tablespace
    , '<div align="center">'       || temporary_tablespace || '</div>'                       temporary_tablespace
    , '<div align="right">'        || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '</div>'  created
    , DECODE(   account_status
              , 'OPEN'
              , '<div align="center"><b><font color="darkgreen">' || account_status || '</font></b></div>'
              , '<div align="center"><b><font color="#663300">'   || account_status || '</font></b></div>') account_status
  FROM
      dba_users
  WHERE
      temporary_tablespace = 'SYSTEM'
  ORDER BY
      username;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                  - OBJECTS IN THE SYSTEM TABLESPACE -                      |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="objects_in_the_system_tablespace"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Objects in the SYSTEM Tablespace</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner               FORMAT a75                   HEADING 'Owner'           ENTMAP off
  COLUMN segment_name        FORMAT a125                  HEADING 'Segment Name'    ENTMAP off
  COLUMN segment_type        FORMAT a75                   HEADING 'Type'            ENTMAP off
  COLUMN tablespace_name     FORMAT a125                  HEADING 'Tablespace'      ENTMAP off
  COLUMN bytes               FORMAT 999,999,999,999,999   HEADING 'Bytes|Alloc'     ENTMAP off
  COLUMN extents             FORMAT 999,999,999,999,999   HEADING 'Extents'         ENTMAP off
  COLUMN max_extents         FORMAT 999,999,999,999,999   HEADING 'Max|Ext'         ENTMAP off
  COLUMN initial_extent      FORMAT 999,999,999,999,999   HEADING 'Initial|Ext'     ENTMAP off
  COLUMN next_extent         FORMAT 999,999,999,999,999   HEADING 'Next|Ext'        ENTMAP off
  COLUMN pct_increase        FORMAT 999,999,999,999,999   HEADING 'Pct|Inc'         ENTMAP off
  
  BREAK ON report ON owner
  COMPUTE count LABEL '<font color="#990000"><b>Total Count: </b></font>' OF segment_name ON report
  COMPUTE sum   LABEL '<font color="#990000"><b>Total Bytes: </b></font>' OF bytes ON report
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || owner || '</b></font></div>'    owner
    , segment_name
    , segment_type
    , tablespace_name
    , bytes
    , extents
    , initial_extent
    , next_extent
    , pct_increase
  FROM
      dba_segments
  WHERE
        owner NOT IN ('SYS','SYSTEM')
    AND tablespace_name = 'SYSTEM'
  ORDER BY
      owner
    , segment_name
    , extents DESC;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                              - RECYCLE BIN -                               |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_recycle_bin"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Recycle Bin</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner               FORMAT a85                   HEADING 'Owner'           ENTMAP off
  COLUMN original_name                                    HEADING 'Original|Name'   ENTMAP off
  COLUMN type                                             HEADING 'Object|Type'     ENTMAP off
  COLUMN object_name                                      HEADING 'Object|Name'     ENTMAP off
  COLUMN ts_name                                          HEADING 'Tablespace'      ENTMAP off
  COLUMN operation                                        HEADING 'Operation'       ENTMAP off
  COLUMN createtime                                       HEADING 'Create|Time'     ENTMAP off
  COLUMN droptime                                         HEADING 'Drop|Time'       ENTMAP off
  COLUMN can_undrop                                       HEADING 'Can|Undrop?'     ENTMAP off
  COLUMN can_purge                                        HEADING 'Can|Purge?'      ENTMAP off
  COLUMN bytes               FORMAT 999,999,999,999,999   HEADING 'Bytes'           ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || owner || '</b></font></div>'    owner
    , original_name
    , type
    , object_name
    , ts_name
    , operation
    , '<div nowrap align="right">'  || NVL(createtime, '<br>') || '</div>' createtime
    , '<div nowrap align="right">'  || NVL(droptime, '<br>')   || '</div>' droptime
    , DECODE(   can_undrop
              , null
              , '<BR>'
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || can_undrop || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || can_undrop || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || can_undrop || '</b></font></div>')   can_undrop
    , DECODE(   can_purge
              , null
              , '<BR>'
              , 'YES'
              , '<div align="center"><font color="darkgreen"><b>' || can_purge || '</b></font></div>'
              , 'NO'
              , '<div align="center"><font color="#990000"><b>'   || can_purge || '</b></font></div>'
              , '<div align="center"><font color="#663300"><b>'   || can_purge || '</b></font></div>')    can_purge
    , (space * p.blocksize) bytes
  FROM
      dba_recyclebin r
    , (SELECT value blocksize FROM v$parameter WHERE name='db_block_size') p
  ORDER BY
      owner
    , object_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  -- +============================================================================+
  -- |                                                                            |
  -- |         <<<<<     ONLINE ANALYTICAL PROCESSING - (OLAP)     >>>>>          |
  -- |                                                                            |
  -- +============================================================================+
  
  
  prompt
  prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Online Analytical Processing - (OLAP)</u></b></font></center>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                              - DIMENSIONS -                                |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_dimensions"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Dimensions</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner              FORMAT a75     HEADING 'Owner'            ENTMAP off
  COLUMN dimension_name     FORMAT a75     HEADING 'Dimension Name'   ENTMAP off
  COLUMN invalid            FORMAT a75     HEADING 'Invalid?'         ENTMAP off
  COLUMN compile_state      FORMAT a75     HEADING 'Compile State'    ENTMAP off
  COLUMN revision                          HEADING 'Revision'         ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || dd.owner || '</b></font></div>'    owner
    , dd.dimension_name                                                                   dimension_name
    , '<div align="center">' || dd.invalid  || '</div>'                                   invalid   
    , DECODE(   dd.compile_state
              , 'VALID'
              , '<div align="center"><font color="darkgreen"><b>' || dd.compile_state || '</b></font></div>'
              , '<div align="center"><font color="#990000"><b>'   || dd.compile_state || '</b></font></div>' ) compile_state
    , '<div align="center">' || dd.revision || '</div>'                                   revision
  FROM
      dba_dimensions      dd
  ORDER BY
      dd.owner
    , dd.dimension_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                            - DIMENSION LEVELS -                            |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_dimension_levels"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Dimension Levels</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner              FORMAT a75                HEADING 'Owner'            ENTMAP off
  COLUMN dimension_name     FORMAT a75                HEADING 'Dimension Name'   ENTMAP off
  COLUMN level_name         FORMAT a75                HEADING 'Level Name'       ENTMAP off
  COLUMN level_table_name   FORMAT a75                HEADING 'Source Table'     ENTMAP off
  COLUMN column_name        FORMAT a75                HEADING 'Column Name(s)'   ENTMAP off
  COLUMN key_position       FORMAT a75                HEADING 'Column Position'  ENTMAP off
  
  BREAK ON owner ON dimension_name ON level_name ON level_table_name
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || d.owner || '</b></font></div>'  owner
    , d.dimension_name                                    dimension_name
    , l.level_name                                        level_name
    , l.detailobj_owner || '.' || l.detailobj_name        level_table_name
    , k.column_name                                       column_name
    , '<div align="center">' || TO_CHAR(k.key_position, '999,999') || '</div>'  key_position
  FROM
      dba_dimensions          d
    , dba_dim_levels          l
    , dba_dim_level_key       k
  WHERE
        d.owner          = l.owner
    AND d.dimension_name = l.dimension_name
    AND d.owner          = k.owner
    AND d.dimension_name = k.dimension_name
    AND l.level_name     = k.level_name
  ORDER by
      l.owner
    , l.dimension_name
    , l.level_name
    , level_table_name
    , k.key_position;
  
  
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                         - DIMENSION ATTRIBUTES -                           |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_dimension_attributes"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Dimension Attributes</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner              FORMAT a75     HEADING 'Owner'                    ENTMAP off
  COLUMN dimension_name     FORMAT a75     HEADING 'Dimension Name'           ENTMAP off
  COLUMN level_name         FORMAT a75     HEADING 'Level Name'               ENTMAP off
  COLUMN level_table_name   FORMAT a75     HEADING 'Source Table'             ENTMAP off
  COLUMN column_name        FORMAT a75     HEADING 'Attribute Source Column'  ENTMAP off
  COLUMN inferred           FORMAT a75     HEADING 'Inferred?'                ENTMAP off
  
  BREAK ON report ON owner ON dimension_name ON level_name
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || d.owner || '</b></font></div>'  owner
    , d.dimension_name                                 dimension_name
    , l.level_name                                     level_name
    , l.detailobj_owner || '.' || l.detailobj_name     level_table_name
    , a.column_name                                    column_name
    , '<div align="center">' || a.inferred  || '</div>'  inferred
  FROM
      dba_dimensions          d
    , dba_dim_levels          l
    , dba_dim_attributes      a
  WHERE
        d.owner          = l.owner
    AND d.dimension_name = l.dimension_name
    AND d.owner          = a.owner
    AND d.dimension_name = a.dimension_name
    AND l.level_name     = a.level_name
  ORDER by
      l.owner
    , l.dimension_name
    , l.level_name
    , level_table_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                         - DIMENSION HIERARCHIES -                          |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_dimension_hierarchies"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Dimension Hierarchies</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner              FORMAT a75              HEADING 'Owner'                ENTMAP off
  COLUMN dimension_name     FORMAT a75              HEADING 'Dimension Name'       ENTMAP off
  COLUMN hierarchy_name     FORMAT a75              HEADING 'Hierarchy Name'       ENTMAP off
  COLUMN parent_level_name  FORMAT a75              HEADING 'Parent Level'         ENTMAP off
  COLUMN child_level_name   FORMAT a75              HEADING 'Child Level'          ENTMAP off
  COLUMN position           FORMAT a75              HEADING 'Position'             ENTMAP off
  COLUMN join_key_id        FORMAT a75              HEADING 'Join Key ID'          ENTMAP off
  
  BREAK ON owner ON dimension_name ON hierarchy_name
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || d.owner || '</b></font></div>'  owner
    , d.dimension_name                                                      dimension_name
    , h.hierarchy_name                                                      hierarchy_name
    , c.parent_level_name                                                   parent_level_name
    , c.child_level_name                                                    child_level_name
    , '<div align="center">' || TO_CHAR(c.position, '999,999') || '</div>'  position
    , '<div align="center">' || NVL(c.join_key_id,'<br>')      || '</div>'  join_key_id
  FROM
      dba_dimensions          d
    , dba_dim_hierarchies     h
    , dba_dim_child_of        c
  WHERE
        d.owner          = h.owner
    AND d.dimension_name = h.dimension_name
    AND d.owner          = c.owner
    AND d.dimension_name = c.dimension_name
    AND h.hierarchy_name = c.hierarchy_name
  ORDER BY
      d.owner
    , d.dimension_name
    , h.hierarchy_name
    , c.position DESC;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                                 - CUBES -                                  |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_cubes"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Cubes</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                    FORMAT a75                 HEADING 'Owner'            ENTMAP off
  COLUMN cube_name                FORMAT a75                 HEADING 'Cube Name'        ENTMAP off
  COLUMN invalid                  FORMAT a75                 HEADING 'Valid?'           ENTMAP off
  COLUMN display_name             FORMAT a75                 HEADING 'Display Name'     ENTMAP off
  COLUMN description              FORMAT a275                HEADING 'Description'      ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || c.owner || '</b></font></div>'  owner
    , c.cube_name                                                                      cube_name
    , DECODE(   c.invalid
              , 'O'
              , '<div align="center"><font color="darkgreen"><b>Yes</b></font></div>'
              , '1'
              , '<div align="center"><font color="#990000"><b>No</b></font></div>'
              , 'Y'
              , '<div align="center"><font color="#990000"><b>No</b></font></div>'
              , 'N'
              , '<div align="center"><font color="darkgreen"><b>Yes</b></font></div>'
              , '<div align="center">' || invalid  || '</div>')   invalid
    , c.display_name                                                                   display_name 
    , REPLACE(REPLACE(c.description, '<', '\&lt;'), '>', '\&gt;')                      description
  FROM
      dba_olap_cubes   c
  ORDER BY
      c.owner
    , c.cube_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                          - MATERIALIZED VIEWS -                            |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_olap_materialized_views"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Materialized Views</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                FORMAT a75     HEADING 'Owner'               ENTMAP off
  COLUMN mview_name           FORMAT a75     HEADING 'MView|Name'          ENTMAP off
  COLUMN master_link          FORMAT a75     HEADING 'Master|Link'         ENTMAP off
  COLUMN updatable            FORMAT a75     HEADING 'Updatable?'          ENTMAP off
  COLUMN update_log           FORMAT a75     HEADING 'Update|Log'          ENTMAP off
  COLUMN rewrite_enabled      FORMAT a75     HEADING 'Rewrite|Enabled?'    ENTMAP off
  COLUMN refresh_mode         FORMAT a75     HEADING 'Refresh|Mode'        ENTMAP off
  COLUMN refresh_method       FORMAT a75     HEADING 'Refresh|Method'      ENTMAP off
  COLUMN build_mode           FORMAT a75     HEADING 'Build|Mode'          ENTMAP off
  COLUMN fast_refreshable     FORMAT a75     HEADING 'Fast|Refreshable'    ENTMAP off
  COLUMN last_refresh_type    FORMAT a75     HEADING 'Last Refresh|Type'   ENTMAP off
  COLUMN last_refresh_date    FORMAT a75     HEADING 'Last Refresh|Date'   ENTMAP off
  COLUMN staleness            FORMAT a75     HEADING 'Staleness'           ENTMAP off
  COLUMN compile_state        FORMAT a75     HEADING 'Compile State'       ENTMAP off
  
  BREAK ON owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || m.owner || '</b></font></div>'                    owner
    , m.mview_name                                                                                       mview_name
    , m.master_link                                                                                      master_link
    , '<div align="center">' || NVL(m.updatable,'<br>')        || '</div>'                               updatable
    , update_log                                                                                         update_log
    , '<div align="center">' || NVL(m.rewrite_enabled,'<br>')  || '</div>'                               rewrite_enabled
    , m.refresh_mode                                                                                     refresh_mode
    , m.refresh_method                                                                                   refresh_method
    , m.build_mode                                                                                       build_mode
    , m.fast_refreshable                                                                                 fast_refreshable
    , m.last_refresh_type                                                                                last_refresh_type
    , '<div nowrap align="right">' || TO_CHAR(m.last_refresh_date, 'mm/dd/yyyy HH24:MI:SS') || '</div>'  last_refresh_date
    , m.staleness                                                                                        staleness
    , DECODE(   m.compile_state
              , 'VALID'
              , '<div align="center"><font color="darkgreen"><b>' || m.compile_state || '</b></font></div>'
              , '<div align="center"><font color="#990000"><b>'   || m.compile_state || '</b></font></div>' ) compile_state
  FROM
    dba_mviews     m 
  ORDER BY
      owner
    , mview_name
  /
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                        - MATERIALIZED VIEW LOGS -                          |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_olap_materialized_view_logs"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Materialized View Logs</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN log_owner            FORMAT a75     HEADING 'Log Owner'            ENTMAP off
  COLUMN log_table            FORMAT a75     HEADING 'Log Table'            ENTMAP off
  COLUMN master               FORMAT a75     HEADING 'Master'               ENTMAP off
  COLUMN log_trigger          FORMAT a75     HEADING 'Log Trigger'          ENTMAP off
  COLUMN rowids               FORMAT a75     HEADING 'Rowids?'              ENTMAP off
  COLUMN primary_key          FORMAT a75     HEADING 'Primary Key?'         ENTMAP off
  COLUMN object_id            FORMAT a75     HEADING 'Object ID?'           ENTMAP off
  COLUMN filter_columns       FORMAT a75     HEADING 'Filter Columns?'      ENTMAP off
  COLUMN sequence             FORMAT a75     HEADING 'Sequence?'            ENTMAP off
  COLUMN include_new_values   FORMAT a75     HEADING 'Include New Values?'  ENTMAP off
  
  BREAK ON log_owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || ml.log_owner || '</b></font></div>'       log_owner
    , ml.log_table                                                              log_table
    , ml.master                                                                 master
    , ml.log_trigger                                                            log_trigger
    , '<div align="center">' || NVL(ml.rowids,'<br>')              || '</div>'  rowids
    , '<div align="center">' || NVL(ml.primary_key,'<br>')         || '</div>'  primary_key
    , '<div align="center">' || NVL(ml.object_id,'<br>')           || '</div>'  object_id
    , '<div align="center">' || NVL(ml.filter_columns,'<br>')      || '</div>'  filter_columns
    , '<div align="center">' || NVL(ml.sequence,'<br>')            || '</div>'  sequence
    , '<div align="center">' || NVL(ml.include_new_values,'<br>')  || '</div>'  include_new_values
  FROM
      dba_mview_logs  ml
  ORDER BY
      ml.log_owner
    , ml.master;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                   - MATERIALIZED VIEW REFRESH GROUPS -                     |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="dba_olap_materialized_view_refresh_groups"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Materialized View Refresh Groups</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner         FORMAT a75   HEADING 'Owner'        ENTMAP off
  COLUMN name          FORMAT a75   HEADING 'Name'         ENTMAP off
  COLUMN broken        FORMAT a75   HEADING 'Broken?'      ENTMAP off
  COLUMN next_date     FORMAT a75   HEADING 'Next Date'    ENTMAP off
  COLUMN interval      FORMAT a75   HEADING 'Interval'     ENTMAP off
  
  BREAK ON report ON owner
  
  SELECT
      '<div nowrap align="left"><font color="#336699"><b>' || rowner   || '</b></font></div>'  owner
    , '<div align="left">'                                 || rname    || '</div>'             name
    , '<div align="center">'                               || broken   || '</div>'             broken
    , '<div nowrap align="right">'                         || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'   next_date
    , '<div nowrap align="right">'                         || interval || '</div>'             interval
  FROM
      dba_refresh 
  ORDER BY
      rowner
    , rname
  /
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  -- +============================================================================+
  -- |                                                                            |
  -- |                      <<<<<     DATA PUMP     >>>>>                         |
  -- |                                                                            |
  -- +============================================================================+
  
  
  prompt
  prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Data Pump</u></b></font></center>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                           - DATA PUMP JOBS -                               |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="data_pump_jobs"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Data Pump Jobs</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner_name         FORMAT a75            HEADING 'Owner Name'         ENTMAP off
  COLUMN job_name           FORMAT a75            HEADING 'Job Name'           ENTMAP off
  COLUMN operation          FORMAT a75            HEADING 'Operation'          ENTMAP off
  COLUMN job_mode           FORMAT a75            HEADING 'Job Mode'           ENTMAP off
  COLUMN state              FORMAT a75            HEADING 'State'              ENTMAP off
  COLUMN degree             FORMAT 999,999,999    HEADING 'Degree'             ENTMAP off
  COLUMN attached_sessions  FORMAT 999,999,999    HEADING 'Attached Sessions'  ENTMAP off
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || dpj.owner_name || '</b></font></div>'  owner_name
    , dpj.job_name                                                                            job_name
    , dpj.operation                                                                           operation
    , dpj.job_mode                                                                            job_mode
    , dpj.state                                                                               state
    , dpj.degree                                                                              degree
    , dpj.attached_sessions                                                                   attached_sessions
  FROM
      dba_datapump_jobs      dpj
  ORDER BY
      dpj.owner_name
    , dpj.job_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                          - DATA PUMP SESSIONS -                            |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="data_pump_sessions"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Data Pump Sessions</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN instance_name_print  FORMAT a75            HEADING 'Instance Name'    ENTMAP off
  COLUMN owner_name           FORMAT a75            HEADING 'Owner Name'       ENTMAP off
  COLUMN job_name             FORMAT a75            HEADING 'Job Name'         ENTMAP off
  COLUMN session_type         FORMAT a75            HEADING 'Session Type'     ENTMAP off
  COLUMN sid                                        HEADING 'SID'              ENTMAP off
  COLUMN serial_no                                  HEADING 'Serial#'          ENTMAP off
  COLUMN oracle_username      FORMAT a75            HEADING 'Oracle Username'  ENTMAP off
  COLUMN os_username          FORMAT a75            HEADING 'O/S Username'     ENTMAP off
  COLUMN os_pid                                     HEADING 'O/S PID'          ENTMAP off
  
  BREAK ON report ON instance_name_print ON owner_name ON job_name
  
  SELECT
      '<div align="center"><font color="#336699"><b>' || i.instance_name  || '</b></font></div>'  instance_name_print
    , dj.owner_name                                                                               owner_name 
    , dj.job_name                                                                                 job_name
    , ds.type                                                                                     session_type
    , s.sid                                                                                       sid
    , s.serial#                                                                                   serial_no
    , s.username                                                                                  oracle_username
    , s.osuser                                                                                    os_username
    , p.spid                                                                                      os_pid
  FROM
      gv$datapump_job         dj
    , gv$datapump_session     ds
    , gv$session              s
    , gv$instance             i
    , gv$process              p
  WHERE
        s.inst_id  = i.inst_id
    AND s.inst_id  = p.inst_id
    AND ds.inst_id = i.inst_id
    AND dj.inst_id = i.inst_id
    AND s.saddr    = ds.saddr
    AND s.paddr    = p.addr (+)
    AND dj.job_id  = ds.job_id
  ORDER BY
      i.instance_name
    , dj.owner_name
    , dj.job_name
    , ds.type;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                        - DATA PUMP JOB PROGRESS -                          |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="data_pump_job_progress"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Data Pump Job Progress</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN instance_name_print  FORMAT a75                 HEADING 'Instance Name'           ENTMAP off
  COLUMN owner_name           FORMAT a75                 HEADING 'Owner Name'              ENTMAP off
  COLUMN job_name             FORMAT a75                 HEADING 'Job Name'                ENTMAP off
  COLUMN session_type         FORMAT a75                 HEADING 'Session Type'            ENTMAP off
  COLUMN start_time                                      HEADING 'Start Time'              ENTMAP off
  COLUMN time_remaining       FORMAT 9,999,999,999,999   HEADING 'Time Remaining (min.)'   ENTMAP off
  COLUMN sofar                FORMAT 9,999,999,999,999   HEADING 'Bytes Completed So Far'  ENTMAP off
  COLUMN totalwork            FORMAT 9,999,999,999,999   HEADING 'Total Bytes for Job'     ENTMAP off
  COLUMN pct_completed                                   HEADING '% Completed'             ENTMAP off
  
  BREAK ON report ON instance_name_print ON owner_name ON job_name
  
  SELECT
      '<div align="center"><font color="#336699"><b>' || i.instance_name  || '</b></font></div>'   instance_name_print
    , dj.owner_name                                                                                owner_name 
    , dj.job_name                                                                                  job_name
    , ds.type                                                                                      session_type
    , '<div align="center" nowrap>' || TO_CHAR(sl.start_time,'mm/dd/yyyy HH24:MI:SS') || '</div>'  start_time
    , ROUND(sl.time_remaining/60,0)                                                                time_remaining
    , sl.sofar                                                                                     sofar
    , sl.totalwork                                                                                 totalwork
    , '<div align="right">' || TRUNC(ROUND((sl.sofar/sl.totalwork) * 100, 1)) || '%</div>'         pct_completed
  FROM
      gv$datapump_job         dj
    , gv$datapump_session     ds
    , gv$session              s
    , gv$instance             i
    , gv$session_longops      sl
  WHERE
        s.inst_id  = i.inst_id
    AND ds.inst_id = i.inst_id
    AND dj.inst_id = i.inst_id
    AND sl.inst_id = i.inst_id
    AND s.saddr    = ds.saddr
    AND dj.job_id  = ds.job_id
    AND sl.sid     = s.sid
    AND sl.serial# = s.serial#
    AND ds.type    = 'MASTER'
  ORDER BY
      i.instance_name
    , dj.owner_name
    , dj.job_name
    , ds.type;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  -- +============================================================================+
  -- |                                                                            |
  -- |                     <<<<<     NETWORKING    >>>>>                          |
  -- |                                                                            |
  -- +============================================================================+
  
  
  prompt
  prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Networking</u></b></font></center>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                     - MTS DISPATCHER STATISTICS -                          |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="mts_dispatcher_statistics"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>MTS Dispatcher Statistics</b></font><hr align="left" width="460">
  
  prompt <b>Dispatcher rate</b>
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN name                    HEADING 'Name'                  ENTMAP off
  COLUMN avg_loop_rate           HEADING 'Avg|Loop|Rate'         ENTMAP off
  COLUMN avg_event_rate          HEADING 'Avg|Event|Rate'        ENTMAP off
  COLUMN avg_events_per_loop     HEADING 'Avg|Events|Per|Loop'   ENTMAP off
  COLUMN avg_msg_rate            HEADING 'Avg|Msg|Rate'          ENTMAP off
  COLUMN avg_svr_buf_rate        HEADING 'Avg|Svr|Buf|Rate'      ENTMAP off
  COLUMN avg_svr_byte_rate       HEADING 'Avg|Svr|Byte|Rate'     ENTMAP off
  COLUMN avg_svr_byte_per_buf    HEADING 'Avg|Svr|Byte|Per|Buf'  ENTMAP off
  COLUMN avg_clt_buf_rate        HEADING 'Avg|Clt|Buf|Rate'      ENTMAP off
  COLUMN avg_clt_byte_rate       HEADING 'Avg|Clt|Byte|Rate'     ENTMAP off
  COLUMN avg_clt_byte_per_buf    HEADING 'Avg|Clt|Byte|Per|Buf'  ENTMAP off
  COLUMN avg_buf_rate            HEADING 'Avg|Buf|Rate'          ENTMAP off
  COLUMN avg_byte_rate           HEADING 'Avg|Byte|Rate'         ENTMAP off
  COLUMN avg_byte_per_buf        HEADING 'Avg|Byte|Per|Buf'      ENTMAP off
  COLUMN avg_in_connect_rate     HEADING 'Avg|In|Connect|Rate'   ENTMAP off
  COLUMN avg_out_connect_rate    HEADING 'Avg|Out|Connect|Rate'  ENTMAP off
  COLUMN avg_reconnect_rate      HEADING 'Avg|Reconnect|Rate'    ENTMAP off
  
  SELECT
      name
    , avg_loop_rate
    , avg_event_rate
    , avg_events_per_loop
    , avg_msg_rate
    , avg_svr_buf_rate
    , avg_svr_byte_rate
    , avg_svr_byte_per_buf
    , avg_clt_buf_rate
    , avg_clt_byte_rate
    , avg_clt_byte_per_buf
    , avg_buf_rate
    , avg_byte_rate
    , avg_byte_per_buf
    , avg_in_connect_rate
    , avg_out_connect_rate
    , avg_reconnect_rate
  FROM
      v$dispatcher_rate
  ORDER BY
      name;
  
  
  COLUMN protocol           HEADING 'Protocol'         ENTMAP off
  COLUMN total_busy_rate    HEADING 'Total Busy Rate'  ENTMAP off
  
  prompt <b>Dispatcher busy rate</b>
  
  SELECT
      a.network protocol
    , (SUM(a.BUSY) / (SUM(a.BUSY) + SUM(a.IDLE))) total_busy_rate
  FROM
      v$dispatcher a
  GROUP BY
      a.network;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |             - MTS DISPATCHER RESPONSE QUEUE WAIT STATS -                   |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="mts_dispatcher_response_queue_wait_stats"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>MTS Dispatcher Response Queue Wait Stats</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN type        HEADING 'Type'                         ENTMAP off
  COLUMN avg_wait    HEADING 'Avg Wait Time Per Response'   ENTMAP off
  
  SELECT
      a.type
    , DECODE( SUM(a.totalq), 0, 'NO RESPONSES', SUM(a.wait)/SUM(a.totalq) || ' HUNDREDTHS OF SECONDS') avg_wait
  FROM
      v$queue a
  WHERE
      a.type='DISPATCHER'
  GROUP BY
      a.type;
  
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                  - MTS SHARED SERVER WAIT STATISTICS -                     |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="mts_shared_server_wait_statistics"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>MTS Shared Server Wait Statistics</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN avg_wait   HEADING 'Average Wait Time Per Request'  ENTMAP off
  
  SELECT
      DECODE(a.totalq, 0, 'No Requests', a.wait/a.totalq || ' HUNDREDTHS OF SECONDS') avg_wait
  FROM
      v$queue a
  WHERE
      a.type='COMMON';
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  -- +============================================================================+
  -- |                                                                            |
  -- |                      <<<<<     REPLICATION    >>>>>                        |
  -- |                                                                            |
  -- +============================================================================+
  
  
  prompt
  prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Replication</u></b></font></center>
  
  
  -- +----------------------------------------------------------------------------+
  -- |                         - REPLICATION SUMMARY -                            |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="replication_summary"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Replication Summary</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN gname           HEADING 'Current Database Name'       ENTMAP off
  COLUMN admin_request   HEADING '# Admin. Requests'           ENTMAP off
  COLUMN status          HEADING '# Admin. Request Errors'     ENTMAP off
  COLUMN df_txn          HEADING '# Def. Trans'                ENTMAP off
  COLUMN df_error        HEADING '# Def. Tran Errors'          ENTMAP off
  COLUMN complete        HEADING '# Complete Trans in Queue'   ENTMAP off
  
  SELECT
      g.global_name           gname
    , d.admin_request         admin_request
    , e.status                status
    , dt.tran                 df_txn
    , de.error                df_error
    , c.complete              complete
  FROM
      (select global_name from global_name)  g
    , (select count(id) admin_request 
       from sys.dba_repcatlog)               d
    , (select count(status) status 
       from sys.dba_repcatlog 
       where status = 'ERROR')               e
    , (select count(*) tran 
       from deftrandest)                     dt
    , (select count(*) error 
  	from deferror)                     de
    , (select count(a.deferred_tran_id) complete 
       from deftran a 
       where a.deferred_tran_id not in 
             (select b.deferred_tran_id 
              from deftrandest b)
      )                                      c
  /
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                        - DEFERRED TRANSACTIONS -                           |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="deferred_transactions"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Deferred Transactions</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN source     HEADING 'Source'              ENTMAP off
  COLUMN dest       HEADING 'Target'              ENTMAP off
  COLUMN trans      HEADING '# Def. Trans'        ENTMAP off
  COLUMN errors     HEADING '# Def. Tran Errors'  ENTMAP off
  
  SELECT
      source
    , dest
    , trans
    , errors
  FROM
      (select
           e.origin_tran_db   source
         , e.destination      dest
         , 'n/a'              trans
         , to_char(count(*))  errors
       from
           deferror e 
       group by
           e.origin_tran_db
         , e.destination 
       union  
       select
           g.global_name      source
         , d.dblink           dest
         , to_char(count(*))  trans
         , 'n/a'              errors
       from
           (select global_name from global_name)  g
         ,  deftran                               t
         ,  deftrandest                           d 
       where
            d.deferred_tran_id = t.deferred_tran_id 
       group by
            g.global_name, d.dblink 
       );
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                     - ADMINISTRATIVE REQUEST JOBS -                        |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="administrative_request_jobs"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Administrative Request Jobs</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN job                          HEADING 'Job ID'             ENTMAP off
  COLUMN priv_user                    HEADING 'Privilege Schema'   ENTMAP off
  COLUMN what            FORMAT a175  HEADING 'Definition'         ENTMAP off
  COLUMN status                       HEADING 'Status'             ENTMAP off
  COLUMN next_date       FORMAT a75   HEADING 'Start'              ENTMAP off
  COLUMN interval                     HEADING 'Interval'           ENTMAP off
  
  SELECT
      job                                            job
    , priv_user                                      priv_user
    , what                                           what
    , DECODE(broken, 'Y', 'Broken', 'Normal')        status
    , '<div nowrap align="right">' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'   next_date
    , interval
  FROM
      sys.dba_jobs 
  WHERE
      what LIKE '%dbms_repcat.do_deferred_repcat_admin%' 
  ORDER BY
      1;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                      - INITIALIZATION PARAMETERS -                         |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="rep_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 pname             FORMAT a55  HEADING 'Parameter Name'    ENTMAP off
  COLUMN value             FORMAT a55  HEADING 'Value'             ENTMAP off
  COLUMN isdefault         FORMAT a55  HEADING 'Is Default?'       ENTMAP off
  COLUMN issys_modifiable  FORMAT a55  HEADING 'Is Dynamic?'       ENTMAP off
  
  SELECT
      DECODE(   isdefault
              , 'FALSE'
              , '<b><font color="#336699">' || SUBSTR(name,0,512) || '</font></b>'
              , '<b><font color="#336699">' || SUBSTR(name,0,512) || '</font></b>' ) pname
    , DECODE(   isdefault
              , 'FALSE'
              , '<font color="#663300"><b>' || SUBSTR(value,0,512) || '</b></font>'
              , SUBSTR(value,0,512) ) value
    , DECODE(   isdefault
              , 'FALSE'
              , '<div align="right"><font color="#663300"><b>' || isdefault || '</b></font></div>'
              , '<div align="right">' || isdefault || '</div>') isdefault
    , DECODE(   isdefault
              , 'FALSE'
              , '<div align="right"><font color="#663300"><b>' || issys_modifiable || '</b></font></div>'
              , '<div align="right">' || issys_modifiable || '</div>') issys_modifiable
  FROM
      v$parameter 
  WHERE
      name IN (   'compatible'
                , 'commit_point_strength'
                , 'dblink_encrypt_login'
                , 'distributed_lock_timeout'
                , 'distributed_recovery_connection_hold_time'
                , 'distributed_transactions'
                , 'global_names'
                , 'job_queue_interval'
                , 'job_queue_processes'
                , 'max_transaction_branches'
                , 'open_links'
                , 'open_links_per_instance'
                , 'parallel_automatic_tuning'
                , 'parallel_max_servers'
                , 'parallel_min_servers'
                , 'parallel_server_idle_time'
                , 'processes'
                , 'remote_dependencies_mode'
                , 'replication_dependency_tracking'
                , 'shared_pool_size'
                , 'utl_file_dir'
    )
  ORDER BY name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                        - (SCHEDULE) - PURGE JOBS -                         |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="schedule_purge_jobs"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Schedule) - Purge Jobs</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN job                          HEADING 'Job ID'            ENTMAP off
  COLUMN priv_user                    HEADING 'Privilege Schema'  ENTMAP off
  COLUMN status                       HEADING 'Status'            ENTMAP off
  COLUMN next_date       FORMAT a75   HEADING 'Start'             ENTMAP off
  COLUMN interval                     HEADING 'Interval'          ENTMAP off
  
  SELECT
      j.job                                           job
    , j.priv_user                                     priv_user
    , decode(broken, 'Y', 'Broken', 'Normal')         status
    , '<div nowrap align="right">' || NVL(TO_CHAR(s.next_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'   next_date
    , s.interval                                      interval 
  FROM
      sys.defschedule   s
    , sys.dba_jobs      j 
  WHERE
        s.dblink = (select global_name from global_name) 
    AND s.interval is not null AND s.job = j.job 
  ORDER BY
      1;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                       - (SCHEDULE) - PUSH JOBS -                           |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="schedule_push_jobs"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Schedule) - Push Jobs</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN job                         HEADING 'Job ID'             ENTMAP off
  COLUMN priv_user                   HEADING 'Privilege Schema'   ENTMAP off
  COLUMN dblink                      HEADING 'Target'             ENTMAP off
  COLUMN broken                      HEADING 'Status'             ENTMAP off
  COLUMN next_date      FORMAT a75   HEADING 'Start'              ENTMAP off
  COLUMN interval                    HEADING 'Interval'           ENTMAP off
  
  SELECT
      j.job                                          job
    , j.priv_user                                    priv_user
    , s.dblink                                       dblink
    , decode(j.broken, 'Y', 'Broken', 'Normal')      broken
    , '<div nowrap align="right">' || NVL(TO_CHAR(s.next_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'   next_date
    , s.interval                                     interval
  FROM
      sys.defschedule  s
    , sys.dba_jobs     j 
  WHERE
        s.dblink != (select global_name from global_name) 
    AND s.interval is not null
    AND s.job = j.job 
  ORDER BY
      1;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                      - (SCHEDULE) - REFRESH JOBS -                         |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="schedule_refresh_jobs"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Schedule) - Refresh Jobs</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN job                           HEADING 'Job ID'             ENTMAP off
  COLUMN priv_user                     HEADING 'Privilege Schema'   ENTMAP off
  COLUMN refresh_group                 HEADING 'Refresh Group'      ENTMAP off
  COLUMN broken                        HEADING 'Status'             ENTMAP off
  COLUMN next_date         FORMAT a75  HEADING 'Start'              ENTMAP off
  COLUMN interval          FORMAT a75  HEADING 'Interval'           ENTMAP off
  
  SELECT
      j.job                                          job
    , j.priv_user                                    priv_user
    , r.rowner || '.' || r.rname                     refresh_group
    , decode(j.broken, 'Y', 'Broken', 'Normal')      broken
    , '<div nowrap align="right">' || NVL(TO_CHAR(j.next_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'   next_date
    , '<div nowrap align="right">' || j.interval                                                 || '</div>'   interval
  FROM
      sys.dba_refresh  r
    , sys.dba_jobs     j
  WHERE
      r.job = j.job 
  order by
      1;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                    - (MULTI-MASTER) - MASTER GROUPS -                      |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="multimaster_master_groups"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Multi-Master) - Master Groups</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN name                       HEADING 'Master Group'             ENTMAP off
  COLUMN num_def_trans              HEADING '# Def. Trans'             ENTMAP off
  COLUMN num_tran_errors            HEADING '# Def. Tran Errors'       ENTMAP off
  COLUMN num_admin_requests         HEADING '# Admin. Requests'        ENTMAP off
  COLUMN num_admin_request_errors   HEADING '# Admin. Request Errors'  ENTMAP off
  
  SELECT
      g.gname          name
    , NVL(t.cnt1, 0)   num_def_trans
    , NVL(ie.cnt2, 0)  num_tran_errors
    , NVL(a.cnt3, 0)   num_admin_requests
    , NVL(b.cnt4, 0)   num_admin_request_errors
  FROM 
      (select distinct gname 
       from dba_repgroup 
       where master='Y')                             g
    , (select
           rog                        rog
         , count(dt.deferred_tran_id) cnt1 
       from (select distinct
                 ro.gname            rog
               , d.deferred_tran_id  dft 
             from
                 dba_repobject  ro
               , defcall        d
               , deftrANDest    td 
             where
                   ro.sname = d.schemaname 
               AND ro.oname = d.packagename 
               AND ro.type in ('TABLE', 'PACKAGE', 'SNAPSHOT') 
               AND td.deferred_tran_id = d.deferred_tran_id 
            ) t0, deftrANDest dt 
       where
           dt.deferred_tran_id = dft 
       group by rog 
      )                                              t
    , (select distinct
           ro.gname
         , count(distinct e.deferred_tran_id) cnt2 
       from
           dba_repobject  ro
         , defcall        d
         , deferror       e 
       where
             ro.sname = d.schemaname 
         AND ro.oname = d.packagename 
         AND ro.type in ('TABLE', 'PACKAGE', 'SNAPSHOT') 
         AND e.deferred_tran_id = d.deferred_tran_id 
         AND e.callno = d.callno 
       group by ro.gname 
      )                                              ie
    , (select gname, count(*) cnt3 
       from dba_repcatlog 
       group by gname 
      )                                              a
    , (select gname, count(*) cnt4 
       from dba_repcatlog  
       where status = 'ERROR' 
       group BY gname 
      )                                              b 
  WHERE
        g.gname = ie.gname (+) 
    AND g.gname = t.rog (+) 
    AND g.gname = a.gname (+) 
    AND g.gname = b.gname (+) 
  ORDER BY
      g.gname;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |               - (MULTI-MASTER) - MASTER GROUPS AND SITES -                 |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="multimaster_master_groups_and_sites"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Multi-Master) - Master Groups and Sites</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN master_group             HEADING 'Master Group'            ENTMAP off
  COLUMN sites                    HEADING 'Sites'                   ENTMAP off
  COLUMN master_definition_site   HEADING 'Master Definition Site'  ENTMAP off
  
  SELECT
      gname                                     master_group
    , dblink                                    sites
    , DECODE(masterdef, 'Y', 'YES', 'N', 'NO')  master_definition_site
  FROM
      sys.dba_repsites
  WHERE
        master = 'Y' 
    AND gname NOT IN (
                        SELECT gname from sys.dba_repsites 
                        WHERE snapmaster = 'Y'
                      )
  ORDER BY
      gname;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |               - (MATERIALIZED VIEW) - MASTER SITE SUMMARY -                |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="materialized_view_master_site_summary"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Materialized View) - Master Site Summary</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN mgroup     HEADING '# of Master Groups'          ENTMAP off
  COLUMN mvgroup    HEADING '# of Registered MV Groups'   ENTMAP off
  COLUMN mv         HEADING '# of Registered MVs'         ENTMAP off
  COLUMN mvlog      HEADING '# of MV Logs'                ENTMAP off
  COLUMN template   HEADING '# of Templates'              ENTMAP off
  
  SELECT
      a.mgroup      mgroup
    , b.mvgroup     mvgroup
    , c.mv          mv
    , d.mvlog       mvlog
    , e.template    template
  FROM 
      (select count(g.gname) mgroup 
       from sys.dba_repgroup g, sys.dba_repsites s 
       where g.master = 'Y' 
         and s.master = 'Y' 
         and g.gname = s.gname 
         and s.my_dblink = 'Y')                      a
    , (select count(*) mvGROUP 
       from sys.dba_registered_snapshot_groups)      b
    , (select count(*) mv 
       from sys.dba_registered_snapshots)            c
    , (select count(*) mvlog 
       from sys.dba_snapshot_logs)                   d
    , (select count(*) template 
       from sys.dba_repcat_refresh_templates)        e;
  
  
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN log_owner        FORMAT a75     HEADING 'Log Owner'         ENTMAP off
  COLUMN log_table                       HEADING 'Log Table'         ENTMAP off
  COLUMN master                          HEADING 'Master'            ENTMAP off
  COLUMN rowids           FORMAT a75     HEADING 'Row ID'            ENTMAP off
  COLUMN primary_key      FORMAT a75     HEADING 'Primary Key'       ENTMAP off
  COLUMN filter_columns   FORMAT a75     HEADING 'Filter Columns'    ENTMAP off
  
  BREAK ON report ON log_owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || log_owner || '</b></font></div>'  log_owner
    , log_table
    , master
    , '<div align="center">' || rowids          || '</div>'   rowids
    , '<div align="center">' || primary_key     || '</div>'   primary_key
    , '<div align="center">' || filter_columns  || '</div>'   filter_columns
  FROM
      sys.dba_snapshot_logs 
  ORDER BY
      log_owner;
  
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN ref_temp_name      HEADING 'Refresh Template Name'      ENTMAP off
  COLUMN owner              HEADING 'Owner'                      ENTMAP off
  COLUMN public_template    HEADING 'Public'                     ENTMAP off
  COLUMN instantiated       HEADING '# of Instantiated Sites'    ENTMAP off
  COLUMN template_comment   HEADING 'Comment'                    ENTMAP off
  
  SELECT
      rt.refresh_template_name                   ref_temp_name
    , owner                                      owner
    , decode(public_template, 'Y', 'YES', 'NO')  public_template
    , rs.instantiated                            instantiated
    , rt.template_comment                        template_comment
  FROM
      sys.dba_repcat_refresh_templates rt
    , (SELECT y.refresh_template_name, count(x.status) instantiated  
       FROM sys.dba_repcat_template_sites x, sys.dba_repcat_refresh_templates y 
       WHERE x.refresh_template_name(+) = y.refresh_template_name 
       GROUP BY y.refresh_template_name) rs 
  WHERE
      rt.refresh_template_name(+) = rs.refresh_template_name 
  ORDER BY
      rt.refresh_template_name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |               - (MATERIALIZED VIEW) - MASTER SITE LOGS -                   |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="materialized_view_master_site_logs"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Materialized View) - Master Site Logs</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN log_owner        FORMAT a75     HEADING 'Log Owner'         ENTMAP off
  COLUMN log_table                       HEADING 'Log Table'         ENTMAP off
  COLUMN master                          HEADING 'Master'            ENTMAP off
  COLUMN rowids           FORMAT a75     HEADING 'Row ID'            ENTMAP off
  COLUMN primary_key      FORMAT a75     HEADING 'Primary Key'       ENTMAP off
  COLUMN filter_columns   FORMAT a75     HEADING 'Filter Columns'    ENTMAP off
  
  BREAK ON report ON log_owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || log_owner || '</b></font></div>'  log_owner
    , log_table
    , master
    , '<div align="center">' || rowids          || '</div>'   rowids
    , '<div align="center">' || primary_key     || '</div>'   primary_key
    , '<div align="center">' || filter_columns  || '</div>'   filter_columns
  FROM
      sys.dba_snapshot_logs 
  ORDER BY
      log_owner;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |             - (MATERIALIZED VIEW) - MASTER SITE TEMPLATES -                |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="materialized_view_master_site_templates"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Materialized View) - Master Site Templates</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner                   HEADING 'Owner'                     ENTMAP off
  COLUMN refresh_template_name   HEADING 'Refresh Template Name'     ENTMAP off
  COLUMN public_template         HEADING 'Public'                    ENTMAP off
  COLUMN instantiated            HEADING '# of Instantiated Sites'   ENTMAP off
  COLUMN template_comment        HEADING 'Comment'                   ENTMAP off
  
  BREAK ON owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || owner || '</b></font></div>'  owner
    , rt.refresh_template_name                                                       refresh_template_name
    , decode(public_template, 'Y', 'YES', 'NO')                                      public_template
    , rs.instantiated                                                                instantiated
    , rt.template_comment                                                            template_comment
  FROM
      sys.dba_repcat_refresh_templates rt
    , ( SELECT y.refresh_template_name, count(x.status) instantiated  
        FROM sys.dba_repcat_template_sites x, sys.dba_repcat_refresh_templates y 
        WHERE x.refresh_template_name(+) = y.refresh_template_name 
        GROUP BY y.refresh_template_name
      ) rs 
  WHERE
      rt.refresh_template_name(+) = rs.refresh_template_name 
  ORDER BY
      owner;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                  - (MATERIALIZED VIEW) - SITE SUMMARY -                    |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="materialized_view_summary"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Materialized View) - Site Summary</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN mvgroup   HEADING '# of Materialized View Groups'  ENTMAP off
  COLUMN mv        HEADING '# of Materialized Views'        ENTMAP off
  COLUMN rgroup    HEADING '# of Refresh Groups'            ENTMAP off
  
  SELECT
      a.mvgroup    mvgroup
    , b.mv         mv
    , c.rgroup     rgroup
  FROM
      (  select count(s.gname) mvgroup 
         from sys.dba_repsites s 
         where s.snapmaster = 'Y')         a
    , (  select count(*) mv 
         from sys.dba_snapshots)           b
    , (  select count(*) rgroup
         from sys.dba_refresh)             c;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                 - (MATERIALIZED VIEW) - SITE GROUPS -                      |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="materialized_view_groups"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Materialized View) - Site Groups</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN gname         HEADING 'Name'           ENTMAP off
  COLUMN dblink        HEADING 'Master'         ENTMAP off
  COLUMN propagation   HEADING 'Propagation'    ENTMAP off
  COLUMN remark        HEADING 'Remark'         ENTMAP off
  
  SELECT
      s.gname                                      gname
    , s.dblink                                     dblink
    , decode(s.prop_updates, 0, 'Async', 'Sync')   propagation
    , g.schema_comment                             remark
  FROM
      sys.dba_repsites  s
    , sys.dba_repgroup  g
  WHERE
        s.gname = g.gname
    AND s.snapmaster = 'Y'
  ORDER BY
      s.gname;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |            - (MATERIALIZED VIEW) - SITE MATERIALIZED VIEWS -               |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="materialized_view_materialized_views"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Materialized View) - Site Materialized Views</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner          FORMAT a75  HEADING 'Owner'           ENTMAP off
  COLUMN name                       HEADING 'Name'            ENTMAP off
  COLUMN master_owner               HEADING 'Master Owner'    ENTMAP off
  COLUMN master_table               HEADING 'Master Table'    ENTMAP off
  COLUMN master_link                HEADING 'Master Link'     ENTMAP off
  COLUMN type                       HEADING 'Type'            ENTMAP off
  COLUMN updatable      FORMAT a75  HEADING 'Updatable?'      ENTMAP off
  COLUMN can_use_log    FORMAT a75  HEADING 'Can Use Log?'    ENTMAP off
  COLUMN last_refresh   FORMAT a75  HEADING 'Last Refresh'    ENTMAP off
  
  BREAK ON owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || s.owner  || '</b></font></div>'  owner
    , s.name                                                                            name
    , s.master_owner                                                                    master_owner
    , s.master                                                                          master_table
    , s.master_link                                                                     master_link
    , nls_initcap(s.type)                                                               type
    , '<div align="center">' || DECODE(s.updatable, 'YES', 'YES', 'NO')  || '</div>'    updatable
    , '<div align="center">' || DECODE(s.can_use_log,'YES', 'YES', 'NO') || '</div>'    can_use_log
    , '<div nowrap align="right">' || NVL(TO_CHAR(m.last_refresh_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'   last_refresh
  FROM
      sys.dba_snapshots  s
    , sys.dba_mviews     m 
  WHERE
        s.name = m.mview_name 
    AND s.owner = m.owner
  ORDER BY
      s.owner
    , s.name;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |              - (MATERIALIZED VIEW) - SITE REFRESH GROUPS -                 |
  -- +----------------------------------------------------------------------------+
  
  prompt <a name="materialized_view_refresh_groups"></a>
  prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>(Materialized View) - Site Refresh Groups</b></font><hr align="left" width="460">
  
  CLEAR COLUMNS BREAKS COMPUTES
  
  COLUMN owner         FORMAT a75   HEADING 'Owner'        ENTMAP off
  COLUMN name          FORMAT a75   HEADING 'Name'         ENTMAP off
  COLUMN broken        FORMAT a75   HEADING 'Broken?'      ENTMAP off
  COLUMN next_date     FORMAT a75   HEADING 'Next Date'    ENTMAP off
  COLUMN interval      FORMAT a75   HEADING 'Interval'     ENTMAP off
  
  BREAK ON owner
  
  SELECT
      '<div align="left"><font color="#336699"><b>' || rowner   || '</b></font></div>'  owner
    , '<div align="left">'                          || rname    || '</div>'             name
    , '<div align="center">'                        || broken   || '</div>'             broken
    , '<div nowrap align="right">'                  || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '<br>') || '</div>'   next_date
    , '<div nowrap align="right">'                  || interval || '</div>'             interval
  FROM
      sys.dba_refresh
  ORDER BY
      rowner
    , rname;
  
  prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
  
  
  
  
  
  
  
  
  -- +----------------------------------------------------------------------------+
  -- |                            - END OF REPORT -                               |
  -- +----------------------------------------------------------------------------+
  
  SPOOL OFF
  
  SET MARKUP HTML OFF
  
  SET TERMOUT ON
  
  prompt 
  prompt Output written to: &FileName._&_dbname._&_spool_time..html
  
  EXIT;
  