Overview Report 10g P1

+++++++ overview report 10g part 1

-- |Jeffrey M. Hunter
-- |jhunter@idevelopment.info
-- |www.idevelopment.info
 
-- |Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved.
 
-- |DATABASE : Oracle
-- |FILE  : dba_snapshot_database_10g.sql
-- |CLASS  : Database Administration
-- |PURPOSE  : This SQL script provides a detailed report (in HTML format) on
-- |all database metrics including installed options, storage,
-- |performance data, and security.
-- |VERSION  : This script was designed for Oracle Database 10g Release 2.
-- |Although this script will also work with Oracle Database 10g
-- |Release 1, several sections will error out from missing tables
-- |or columns.
-- |USAGE  :
-- ||
-- |sqlplus -s <dba>/<password>@<TNS string> @dba_snapshot_database_10g.sql
-- ||
-- |TESTING  : This script has been successfully tested on the following
-- |platforms:
-- ||
-- |Linux  : Oracle Database 10.2.0.3.0
-- |Linux  : Oracle RAC 10.2.0.3.0
-- |Solaris  : Oracle Database 10.2.0.2.0
-- |Solaris  : Oracle Database 10.2.0.3.0
-- |Windows XP : Oracle Database 10.2.0.3.0
-- ||
-- |NOTE  : As with any code, ensure to test this script in a development
-- |environment before attempting to run it in production.
 
prompt 
prompt +-----------------------------------------------------------------------------------------+
prompt |  Snapshot Database 10g Release 2
prompt |-----------------------------------------------------------------------------------------+
prompt | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. (www.idevelopment.info)
prompt +-----------------------------------------------------------------------------------------+
prompt
prompt Creating database report.
prompt This script must be run as a user with SYSDBA privileges.
prompt This process can take several minutes to complete.
prompt 
 
define reportHeader="<font size=+3 color=darkgreen><b>Snapshot Database 10<i>g</i> Release 2</b></font><hr>Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. (<a target=""_blank"" href=""http://www.idevelopment.info"">www.idevelopment.info</a>)<p>"
 
-- |SCRIPT SETTINGS
 
set termout  off
set echo  off
set feedback  off
set heading  off
set verify  off
set wrap  on
set trimspool  on
set serveroutput  on
set escape  on
 
set pagesize 50000
set linesize 175
set long  2000000000
 
clear buffer computes columns breaks
 
define fileName=dba_snapshot_database_10g
define versionNumber=5.3
 
-- |GATHER DATABASE REPORT INFORMATION
 
COLUMN tdate NEW_VALUE _date NOPRINT
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') tdate FROM dual;
 
COLUMN time NEW_VALUE _time NOPRINT
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual;
 
COLUMN date_time NEW_VALUE _date_time NOPRINT
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual;
 
COLUMN date_time_timezone NEW_VALUE _date_time_timezone NOPRINT
SELECT TO_CHAR(systimestamp, 'Mon DD, YYYY (') || TRIM(TO_CHAR(systimestamp, 'Day')) || TO_CHAR(systimestamp, ') "at" HH:MI:SS AM') || TO_CHAR(systimestamp, ' "in Timezone" TZR') date_time_timezone
FROM dual;
 
COLUMN spool_time NEW_VALUE _spool_time NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;
 
COLUMN dbname NEW_VALUE _dbname NOPRINT
SELECT name dbname FROM v$database;
 
COLUMN dbid NEW_VALUE _dbid NOPRINT
SELECT dbid dbid FROM v$database;
 
COLUMN platform_id NEW_VALUE _platform_id NOPRINT
SELECT platform_id platform_id FROM v$database;
 
COLUMN platform_name NEW_VALUE _platform_name NOPRINT
SELECT platform_name platform_name FROM v$database;
 
COLUMN global_name NEW_VALUE _global_name NOPRINT
SELECT global_name global_name FROM global_name;
 
COLUMN blocksize NEW_VALUE _blocksize NOPRINT
SELECT value blocksize FROM v$parameter WHERE name='db_block_size';
 
COLUMN startup_time NEW_VALUE _startup_time NOPRINT
SELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance;
 
COLUMN host_name NEW_VALUE _host_name NOPRINT
SELECT host_name host_name FROM v$instance;
 
COLUMN instance_name NEW_VALUE _instance_name NOPRINT
SELECT instance_name instance_name FROM v$instance;
 
COLUMN instance_number NEW_VALUE _instance_number NOPRINT
SELECT instance_number instance_number FROM v$instance;
 
COLUMN thread_number NEW_VALUE _thread_number NOPRINT
SELECT thread# thread_number FROM v$instance;
 
COLUMN cluster_database NEW_VALUE _cluster_database NOPRINT
SELECT value cluster_database FROM v$parameter WHERE name='cluster_database';
 
COLUMN cluster_database_instances NEW_VALUE _cluster_database_instances NOPRINT
SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances';
 
COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINT
SELECT user reportRunUser FROM dual;
 
-- |GATHER DATABASE REPORT INFORMATION
 
set heading on
 
set markup html on spool on preformat off entmap on -
head ' -
  <title>Database Report</title> -
  <style type="text/css"> -
  body  {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -
  p  {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -
  table,tr,td  {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#C0C0C0; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
  th  {font:bold 9pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} -
  h1  {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
  h2  {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
  a  {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.link  {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLink  {font:9pt Arial,Helvetica,sans-serif; color:#663300; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkDarkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkRed  {font:9pt Arial,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkDarkRed  {font:9pt Arial,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkGreen  {font:9pt Arial,Helvetica,sans-serif; color:#00ff00; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  a.noLinkDarkGreen {font:9pt Arial,Helvetica,sans-serif; color:#009900; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  </style>' -
body  'BGCOLOR="#C0C0C0"' -
table  'WIDTH="90%" BORDER="1"' 
 
spool &FileName._&_dbname._&_spool_time..html
 
set markup html on entmap off
 
-- |- REPORT HEADER -
 
prompt <a name=top></a>
prompt &reportHeader
 
-- |- REPORT INDEX -
 
prompt <a name="report_index"></a>
 
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Report Index</b></font><hr align="center" width="250"></center> -
<table width="90%" border="1"> 
 
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> -
 
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>
 
</table>
 
prompt <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;