Monday, February 14, 2011

Database diagnosis script

Hi,
Many times we are asked to check various things like what was the size of the database 2 years back,How are database is performing,How many processes are running,High Water Mark Statistics,Initialization Parameters description,controlfile,online redolog files status,Tablespaces sizes,Datafile sizes actualled used and various other things which is really happening your database so I founded one stop solution for this in the form of my Database diagnosis report.

Note 1:This script should be ran as sys user as sysdba privilege as follows.
Note 2:This process can take several minutes to complete depending on size and load on your database so wait for some time for it to complete.
Note 3: This script is very big,If you want the entire script separately mail me.
or else you can run this script by making parts Eg:Undo related,High water mark related as per your convenient

-- +----------------------------------------------------------------------------+
-- | /******* Rafi Oracle DBA & Apps DBA Blog *******\ |
-- | http://rafioracledba.blogspot.com/ |
-- | Database diagnosis report |
-- |----------------------------------------------------------------------------|
-- | |
-- |----------------------------------------------------------------------------|
-- | 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_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 | ******* http://rafioracledba.blogspot.com ******* |
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="Snapshot Database 10g Release 2
******* http://rafioracledba.blogspot.com *******)

"


-- +----------------------------------------------------------------------------+
-- | 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 ' -
Database Report -
' -
body 'BGCOLOR="#C0C0C0"' -
table 'WIDTH="90%" BORDER="1"'

spool c:\&FileName._&_dbname._&_spool_time..html

set markup html on entmap off


-- +----------------------------------------------------------------------------+
-- | - REPORT HEADER - |
-- +----------------------------------------------------------------------------+

prompt

prompt &reportHeader



-- +----------------------------------------------------------------------------+
-- | - REPORT INDEX - |
-- +----------------------------------------------------------------------------+

prompt


prompt

Report Index
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-



prompt -
-
-
-
-
-
-



prompt -
-
-
-
-
-
-



prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Database and Instance Information
Report HeaderVersionOptionsDatabase Registry
Feature Usage StatisticsHigh Water Mark StatisticsInstance OverviewDatabase Overview
Initialization ParametersControl FilesControl File RecordsOnline Redo Logs
Redo Log SwitchesOutstanding AlertsStatistics Level
Scheduler / Jobs
Jobs


Storage
TablespacesData FilesDatabase GrowthTablespace Extents
Tablespace to OwnerOwner to Tablespace

UNDO Segments
UNDO SegmentsUNDO Segment ContentionUNDO Retention Parameters
Backups
RMAN Backup JobsRMAN ConfigurationRMAN Backup SetsRMAN Backup Pieces
RMAN Backup Control FilesRMAN Backup SPFILEArchiving ModeArchive Destinations
Archiving Instance ParametersArchiving HistoryFlash Recovery Area ParametersFlash Recovery Area Status
Flashback Technologies
UNDO Retention ParametersFlashback Database ParametersFlashback Database StatusFlashback Database Redo Time Matrix
Recycle Bin


Performance
SGA InformationSGA Target AdviceSGA (ASMM) Dynamic ComponentsPGA Target Advice
File I/O StatisticsFile I/O TimingsAverage Overall I/O per SecondRedo Log Contention
Full Table ScansSortsOutlinesOutline Hints
SQL Statements With Most Buffer GetsSQL Statements With Most Disk ReadsEnabled TracesEnabled Aggregations
Automatic Workload Repository - (AWR)
Workload Repository InformationAWR Snapshot SettingsAWR Snapshot ListAWR Snapshot Size Estimates
AWR Baselines


Sessions
Current SessionsUser Session MatrixEnabled TracesEnabled Aggregations
Security
User AccountsUsers With DBA PrivilegesRolesDefault Passwords
DB Links


Objects
Object SummarySegment SummaryTop 100 Segments (by size)Top 100 Segments (by number of extents)
DirectoriesDirectory PrivilegesLibrariesTypes
Type AttributesType MethodsCollectionsLOB Segments
Objects Unable to ExtendObjects Which Are Nearing MAXEXTENTSInvalid ObjectsProcedural Object Errors
Objects Without StatisticsTables Suffering From Row Chaining/MigrationUsers With Default Tablespace - (SYSTEM)Users With Default Temp Tablespace - (SYSTEM)
Objects in the SYSTEM TablespaceRecycle Bin

Online Analytical Processing - (OLAP)
DimensionsDimension LevelsDimension AttributesDimension Hierarchies
CubesMaterialized ViewsMaterialized View LogsMaterialized View Refresh Groups
Data Pump
Data Pump JobsData Pump SessionsData Pump Job Progress
Networking
MTS Dispatcher StatisticsMTS Dispatcher Response Queue Wait StatsMTS Shared Server Wait Statistics
Replication
Replication SummaryDeferred TransactionsAdministrative Request JobsInitialization Parameters
(Schedule) - Purge Jobs(Schedule) - Push Jobs(Schedule) - Refresh Jobs(Multi-Master) - Master Groups
(Multi-Master) - Master Groups and Sites(Materialized View) - Master Site Summary(Materialized View) - Master Site Logs(Materialized View) - Master Site Templates
(Materialized View) - Summary(Materialized View) - Groups(Materialized View) - Materialized Views(Materialized View) - Refresh Groups


prompt








-- +============================================================================+
-- | |
-- | <<<<< Database and Instance Information >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

Database and Instance Information



-- +----------------------------------------------------------------------------+
-- | - REPORT HEADER - |
-- +----------------------------------------------------------------------------+

prompt
prompt
prompt Report Header


prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Report Name&FileName._&_dbname._&_spool_time..html
Snapshot Database Version&versionNumber
Run Date / Time / Timezone&_date_time_timezone
Host Name&_host_name
Database Name&_dbname
Database ID&_dbid
Global Database Name&_global_name
Platform Name / ID&_platform_name / &_platform_id
Clustered Database?&_cluster_database
Clustered Database Instances&_cluster_database_instances
Instance Name&_instance_name
Instance Number&_instance_number
Thread Number&_thread_number
Database Startup Time&_startup_time
Database Block Size&_blocksize
Report Run User&_reportRunUser


prompt
[Top]






-- SET TIMING ON




-- +----------------------------------------------------------------------------+
-- | - VERSION - |
-- +----------------------------------------------------------------------------+

prompt
prompt Version




CLEAR COLUMNS BREAKS COMPUTES

COLUMN banner FORMAT a120 HEADING 'Banner'

SELECT * FROM v$version;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - OPTIONS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Options




CLEAR COLUMNS BREAKS COMPUTES

COLUMN parameter HEADING 'Option Name' ENTMAP off
COLUMN value HEADING 'Installed?' ENTMAP off

SELECT
DECODE( value
, 'FALSE'
, '' || parameter || ''
, '' || parameter || '') parameter
, DECODE( value
, 'FALSE'
, '
' || value || '
'
, '
' || value || '
' ) value
FROM v$option
ORDER BY parameter;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - DATABASE REGISTRY - |
-- +----------------------------------------------------------------------------+

prompt
prompt Database Registry




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
'' || comp_id || '' comp_id
, '
' || comp_name || '
' comp_name
, version
, DECODE( status
, 'VALID', '
' || status || '
'
, 'INVALID', '
' || status || '
'
, '
' || status || '
' ) status
, '
' || modified || '
' modified
, control
, schema
, procedure
FROM dba_registry
ORDER BY comp_name;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - FEATURE USAGE STATISTICS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Feature Usage Statistics




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
'
' || name || '
' feature_name
, DECODE( detected_usages
, 0
, version
, '' || version || '') version
, DECODE( detected_usages
, 0
, '
' || NVL(TO_CHAR(detected_usages), '
') || '
'
, '
' || NVL(TO_CHAR(detected_usages), '
') || '
') detected_usages
, DECODE( detected_usages
, 0
, '
' || NVL(TO_CHAR(total_samples), '
') || '
'
, '
' || NVL(TO_CHAR(total_samples), '
') || '
') total_samples
, DECODE( detected_usages
, 0
, '
' || NVL(currently_used, '
') || '
'
, '
' || NVL(currently_used, '
') || '
') currently_used
, DECODE( detected_usages
, 0
, '
' || NVL(TO_CHAR(first_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
'
, '
' || NVL(TO_CHAR(first_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
') first_usage_date
, DECODE( detected_usages
, 0
, '
' || NVL(TO_CHAR(last_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
'
, '
' || NVL(TO_CHAR(last_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
') last_usage_date
, DECODE( detected_usages
, 0
, '
' || NVL(TO_CHAR(last_sample_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
'
, '
' || NVL(TO_CHAR(last_sample_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
') last_sample_date
, DECODE( detected_usages
, 0
, '
' || NVL(TO_CHAR((last_sample_date+SAMPLE_INTERVAL/60/60/24), 'mm/dd/yyyy HH24:MI:SS'), '
') || '
'
, '
' || NVL(TO_CHAR((last_sample_date+SAMPLE_INTERVAL/60/60/24), 'mm/dd/yyyy HH24:MI:SS'), '
') || '
') next_sample_date
FROM dba_feature_usage_statistics
ORDER BY name;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - HIGH WATER MARK STATISTICS - |
-- +----------------------------------------------------------------------------+

prompt
prompt High Water Mark Statistics




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
'
' || name || '
' statistic_name
, '
' || version || '
' version
, highwater highwater
, last_value last_value
, description description
FROM dba_high_water_mark_statistics
ORDER BY name;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - INSTANCE OVERVIEW - |
-- +----------------------------------------------------------------------------+

prompt
prompt Instance Overview




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
'
' || instance_name || '
' instance_name_print
, '
' || instance_number || '
' instance_number_print
, '
' || thread# || '
' thread_number_print
, '
' || host_name || '
' host_name_print
, '
' || version || '
' version
, '
' || TO_CHAR(startup_time,'mm/dd/yyyy HH24:MI:SS') || '
' start_time
, ROUND(TO_CHAR(SYSDATE-startup_time), 2) uptime
, '
' || parallel || '
' parallel
, '
' || status || '
' instance_status
, '
' || logins || '
' logins
, DECODE( archiver
, 'FAILED'
, '
' || archiver || '
'
, '
' || archiver || '
') archiver
FROM gv$instance
ORDER BY instance_number;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - DATABASE OVERVIEW - |
-- +----------------------------------------------------------------------------+

prompt
prompt Database Overview




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
'
' || name || '
' name
, '
' || dbid || '
' dbid
, '
' || db_unique_name || '
' db_unique_name
, '
' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '
' creation_date
, '
' || platform_name || '
' platform_name_print
, '
' || current_scn || '
' current_scn
, '
' || log_mode || '
' log_mode
, '
' || open_mode || '
' open_mode
, '
' || force_logging || '
' force_logging
, '
' || flashback_on || '
' flashback_on
, '
' || controlfile_type || '
' controlfile_type
, '
' || last_open_incarnation# || '
' last_open_incarnation_number
FROM v$database;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - INITIALIZATION PARAMETERS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Initialization Parameters




CLEAR COLUMNS BREAKS COMPUTES

COLUMN spfile HEADING 'SPFILE Usage'

SELECT
'This database '||
DECODE( (1-SIGN(1-SIGN(count(*) - 0)))
, 1
, 'IS'
, 'IS NOT') ||
' 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'
, '' || SUBSTR(p.name,0,512) || ''
, '' || SUBSTR(p.name,0,512) || '' ) pname
, DECODE( p.isdefault
, 'FALSE'
, '' || i.instance_name || ''
, i.instance_name ) instance_name_print
, DECODE( p.isdefault
, 'FALSE'
, '' || SUBSTR(p.value,0,512) || ''
, SUBSTR(p.value,0,512) ) value
, DECODE( p.isdefault
, 'FALSE'
, '
' || p.isdefault || '
'
, '
' || p.isdefault || '
') isdefault
, DECODE( p.isdefault
, 'FALSE'
, '
' || p.issys_modifiable || '
'
, '
' || p.issys_modifiable || '
') issys_modifiable
FROM
gv$parameter p
, gv$instance i
WHERE
p.inst_id = i.inst_id
ORDER BY
p.name
, i.instance_name;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - CONTROL FILES - |
-- +----------------------------------------------------------------------------+

prompt
prompt Control Files




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
'' || c.name || '' name
, DECODE( c.status
, NULL
, '
VALID
'
, '
' || c.status || '
') status
, '
' || TO_CHAR(block_size * file_size_blks, '999,999,999,999') || '
' file_size
FROM
v$controlfile c
ORDER BY
c.name;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - CONTROL FILE RECORDS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Control File Records




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 'Total: ' of record_size records_total bytes_alloc records_used bytes_used ON report
COMPUTE avg LABEL 'Average: ' of pct_used ON report

SELECT
'
' || type || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - ONLINE REDO LOGS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Online Redo Logs




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
'
' || i.instance_name || '
' instance_name_print
, '
' || i.thread# || '
' thread_number_print
, f.group# groupno
, '' || f.member || '' member
, f.type redo_file_type
, DECODE( l.status
, 'CURRENT'
, '
' || l.status || '
'
, '
' || l.status || '
') log_status
, l.bytes bytes
, '
' || l.archived || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - REDO LOG SWITCHES - |
-- +----------------------------------------------------------------------------+

prompt
prompt Redo Log Switches




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 'Total:' avg label 'Average:' OF total ON report

SELECT
'
' || SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - OUTSTANDING ALERTS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Outstanding Alerts




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'
, '
' || alert_state || '
'
, '
' || alert_state || '
') 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
, '
' || TO_CHAR(collection_timestamp, 'mm/dd/yyyy HH24:MI:SS') || '
' alert_triggered
FROM
mgmt$alert_current
ORDER BY
alert_state
, collection_timestamp;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - STATISTICS LEVEL - |
-- +----------------------------------------------------------------------------+

prompt
prompt Statistics Level




prompt "Automatic Database Management" was first introduced in Oracle10g 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
'
' || i.instance_name || '
' instance_name_print
, '
' || s.statistics_name || '
' statistics_name
, DECODE( s.session_status
, 'ENABLED'
, '
' || s.session_status || '
'
, '
' || s.session_status || '
') session_status
, DECODE( s.system_status
, 'ENABLED'
, '
' || s.system_status || '
'
, '
' || s.system_status || '
') system_status
, (CASE s.activation_level
WHEN 'TYPICAL' THEN '
' || s.activation_level || '
'
WHEN 'ALL' THEN '
' || s.activation_level || '
'
WHEN 'BASIC' THEN '
' || s.activation_level || '
'
ELSE
'
' || s.activation_level || '
'
END) activation_level
, s.statistics_view_name statistics_view_name
, '
' || s.session_settable || '
' 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
[Top]








-- +============================================================================+
-- | |
-- | <<<<< SCHEDULER / JOBS >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

Scheduler / Jobs



-- +----------------------------------------------------------------------------+
-- | - JOBS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Jobs


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'
, '
' || job || '
'
, '
' || job || '
') job_id
, DECODE( broken
, 'Y'
, '' || log_user || ''
, log_user ) username
, DECODE( broken
, 'Y'
, '' || what || ''
, what ) what
, DECODE( broken
, 'Y'
, '
' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
'
, '
' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
') next_date
, DECODE( broken
, 'Y'
, '' || interval || ''
, interval ) interval
, DECODE( broken
, 'Y'
, '
' || NVL(TO_CHAR(last_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
'
, '
' || NVL(TO_CHAR(last_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
') last_date
, DECODE( broken
, 'Y'
, '
' || NVL(failures, 0) || '
'
, '
' || NVL(failures, 0) || '
') failures
, DECODE( broken
, 'Y'
, '
' || broken || '
'
, '
' || broken || '
') broken
FROM
dba_jobs
ORDER BY
job;

prompt
[Top]








-- +============================================================================+
-- | |
-- | <<<<< STORAGE >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

Storage



-- +----------------------------------------------------------------------------+
-- | - TABLESPACES - |
-- +----------------------------------------------------------------------------+

prompt
prompt Tablespaces


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 'Total:' OF ts_size used free ON report

SELECT
DECODE( d.status
, 'OFFLINE'
, '
' || d.status || '
'
, '
' || d.status || '
') status
, '' || d.tablespace_name || '' 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
, '
' ||
DECODE (
(1-SIGN(1-SIGN(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) - 90)))
, 1
, '' || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || ''
, '' || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || ''
)
|| '
%
' 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'
, '
' || d.status || '
'
, '
' || d.status || '
') status
, '' || d.tablespace_name || '' 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
, '
' ||
DECODE (
(1-SIGN(1-SIGN(TRUNC(NVL(t.bytes / a.bytes * 100, 0)) - 90)))
, 1
, '' || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || ''
, '' || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || ''
)
|| '
%
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - DATA FILES - |
-- +----------------------------------------------------------------------------+

prompt
prompt Data Files




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 'Total: ' OF filesize ON report

SELECT /*+ ordered */
'' || d.tablespace_name || '' tablespace
, '' || d.file_name || '' filename
, d.bytes filesize
, '
' || NVL(d.autoextensible, '
') || '
' 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
'' || d.tablespace_name || '' tablespace
, '' || d.file_name || '' filename
, d.bytes filesize
, '
' || NVL(d.autoextensible, '
') || '
' 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
'[ ONLINE REDO LOG ]'
, '' || a.member || ''
, b.bytes
, null
, null
, null
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
'[ CONTROL FILE ]'
, '' || a.name || ''
, null
, null
, null
, null
FROM
v$controlfile a
ORDER BY
1
, 2;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - DATABASE GROWTH - |
-- +----------------------------------------------------------------------------+

prompt
prompt Database Growth




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 'Total:' OF growth ON report

SELECT
'
' || TO_CHAR(creation_time, 'RRRR-MM') || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - TABLESPACE EXTENTS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Tablespace Extents




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 'Total:' of largest_ext smallest_ext total_free pieces on report

SELECT
'' || tablespace_name || '' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - TABLESPACE TO OWNER - |
-- +----------------------------------------------------------------------------+

prompt
prompt Tablespace to Owner




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 'Total: ' of seg_count bytes ON report

SELECT
'' || tablespace_name || '' tablespace_name
, '
' || owner || '
' owner
, '
' || segment_type || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - OWNER TO TABLESPACE - |
-- +----------------------------------------------------------------------------+

prompt
prompt Owner to Tablespace




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 'Total: ' of seg_count bytes on report

SELECT
'' || owner || '' owner
, '
' || tablespace_name || '
' tablespace_name
, '
' || segment_type || '
' 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
[Top]








-- +============================================================================+
-- | |
-- | <<<<< UNDO Segments >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

UNDO Segments



-- +----------------------------------------------------------------------------+
-- | - UNDO RETENTION PARAMETERS - |
-- +----------------------------------------------------------------------------+

prompt
prompt UNDO Retention Parameters


prompt undo_retention is specified in minutes

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
'
' || i.instance_name || '
' instance_name_print
, '
' || i.thread# || '
' thread_number_print
, '
' || p.name || '
' name
, (CASE p.name
WHEN 'undo_retention' THEN '
' || TO_CHAR(TO_NUMBER(p.value)/60, '999,999,999,999,999') || '
'
ELSE
'
' || p.value || '
'
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
[Top]






-- +----------------------------------------------------------------------------+
-- | - UNDO SEGMENTS - |
-- +----------------------------------------------------------------------------+

prompt
prompt UNDO Segments




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 'Total:' OF bytes extents shrinks wraps ON report

SELECT
'
' || NVL(i.instance_name, '
') || '
' instance_name
, '
' || a.tablespace_name || '
' tablespace
, '
' || a.owner || '.' || a.segment_name || '
' roll_name
, '
' ||
TO_CHAR(a.initial_extent) || ' / ' ||
TO_CHAR(a.next_extent) ||
'
' in_extents
, '
' ||
TO_CHAR(a.min_extents) || ' / ' ||
TO_CHAR(a.max_extents) ||
'
' m_extents
, DECODE( a.status
, 'OFFLINE'
, '
' || a.status || '
'
, '
' || a.status || '
') 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - UNDO SEGMENT CONTENTION - |
-- +----------------------------------------------------------------------------+

prompt
prompt UNDO Segment Contention




prompt UNDO statistics from V$ROLLSTAT - (ordered by waits)

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 'Total:' OF gets waits ON report

SELECT
'' || b.name || '' roll_name
, gets gets
, waits waits
, '
' || TO_CHAR(ROUND(((gets - waits)*100)/gets, 2)) || '%
' hit_ratio
FROM
sys.v_$rollstat a
, sys.v_$rollname b
WHERE
a.USN = b.USN
ORDER BY
waits DESC;


prompt
prompt Wait statistics

CLEAR COLUMNS BREAKS COMPUTES

COLUMN class HEADING 'Class'
COLUMN ratio HEADING 'Wait Ratio'

SELECT
'' || w.class || '' class
, '
' || TO_CHAR(ROUND(100*(w.count/SUM(s.value)),8)) || '%
' 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
[Top]








-- +============================================================================+
-- | |
-- | <<<<< BACKUPS >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

Backups



-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP JOBS - |
-- +----------------------------------------------------------------------------+

prompt
prompt RMAN Backup Jobs


prompt Last 10 RMAN backup jobs

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
'
' || r.command_id || '
' backup_name
, '
' || TO_CHAR(r.start_time, 'mm/dd/yyyy HH24:MI:SS') || '
' start_time
, '
' || r.time_taken_display || '
' elapsed_time
, DECODE( r.status
, 'COMPLETED'
, '
' || r.status || '
'
, 'RUNNING'
, '
' || r.status || '
'
, 'FAILED'
, '
' || r.status || '
'
, '
' || r.status || '
'
) status
, r.input_type input_type
, r.output_device_type output_device_type
, '
' || r.input_bytes_display || '
' input_size
, '
' || r.output_bytes_display || '
' output_size
, '
' || r.output_bytes_per_sec_display || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - RMAN CONFIGURATION - |
-- +----------------------------------------------------------------------------+

prompt
prompt RMAN Configuration




prompt All non-default RMAN configuration settings

CLEAR COLUMNS BREAKS COMPUTES

COLUMN name FORMAT a130 HEADING 'Name' ENTMAP off
COLUMN value HEADING 'Value' ENTMAP off

SELECT
'
' || name || '
' name
, value
FROM
v$rman_configuration
ORDER BY
name;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP SETS - |
-- +----------------------------------------------------------------------------+

prompt
prompt RMAN Backup Sets




prompt Available backup sets contained in the control file including available and expired backup sets

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 'Total:' OF pieces elapsed_seconds ON report

SELECT
'
' || bs.recid || '
' bs_key
, DECODE(backup_type
, 'L', '
Archived Redo Logs
'
, 'D', '
Datafile Full Backup
'
, 'I', '
Incremental Backup
') backup_type
, '
' || device_type || '
' device_type
, '
' ||
DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included) || '
' controlfile_included
, '
' || NVL(sp.spfile_included, '-') || '
' spfile_included
, bs.incremental_level incremental_level
, bs.pieces pieces
, '
' || TO_CHAR(bs.start_time, 'mm/dd/yyyy HH24:MI:SS') || '
' start_time
, '
' || TO_CHAR(bs.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '
' completion_time
, bs.elapsed_seconds elapsed_seconds
, bp.tag tag
, bs.block_size block_size
, '
' || bs.keep || '
' keep
, '
' || NVL(TO_CHAR(bs.keep_until, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP PIECES - |
-- +----------------------------------------------------------------------------+

prompt
prompt RMAN Backup Pieces




prompt Available backup pieces contained in the control file including available and expired backup sets

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
'
' || bs.recid || '
' bs_key
, bp.piece# piece#
, bp.copy# copy#
, bp.recid bp_key
, DECODE( status
, 'A', '
Available
'
, 'D', '
Deleted
'
, 'X', '
Expired
') status
, handle handle
, '
' || TO_CHAR(bp.start_time, 'mm/dd/yyyy HH24:MI:SS') || '
' start_time
, '
' || TO_CHAR(bp.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP CONTROL FILES - |
-- +----------------------------------------------------------------------------+

prompt
prompt RMAN Backup Control Files




prompt Available automatic control files within all available (and expired) backup sets

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
'
' || bs.recid || '
' bs_key
, bp.piece# piece#
, bp.copy# copy#
, bp.recid bp_key
, '
' ||
DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included) ||
'
' controlfile_included
, DECODE( status
, 'A', '
Available
'
, 'D', '
Deleted
'
, 'X', '
Expired
') 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP SPFILE - |
-- +----------------------------------------------------------------------------+

prompt
prompt RMAN Backup SPFILE




prompt Available automatic SPFILE backups within all available (and expired) backup sets

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
'
' || bs.recid || '
' bs_key
, bp.piece# piece#
, bp.copy# copy#
, bp.recid bp_key
, '
' ||
NVL(sp.spfile_included, '-') ||
'
' spfile_included
, DECODE( status
, 'A', '
Available
'
, 'D', '
Deleted
'
, 'X', '
Expired
') 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - ARCHIVING MODE - |
-- +----------------------------------------------------------------------------+

prompt
prompt Archiving Mode




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
'
' || d.log_mode || '
' db_log_mode
, '
' || p.log_archive_start || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - ARCHIVE DESTINATIONS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Archive Destinations




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
'
' || a.dest_id || '
' dest_id
, a.dest_name dest_name
, a.destination destination
, DECODE( a.status
, 'VALID', '
' || status || '
'
, 'INACTIVE', '
' || status || '
'
, '
' || status || '
' ) status
, DECODE( a.schedule
, 'ACTIVE', '
' || schedule || '
'
, 'INACTIVE', '
' || schedule || '
'
, '
' || schedule || '
' ) schedule
, a.archiver archiver
, a.log_sequence log_sequence
FROM
v$archive_dest a
ORDER BY
a.dest_id
/


prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - ARCHIVING INSTANCE PARAMETERS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Archiving Instance Parameters




CLEAR COLUMNS BREAKS COMPUTES

COLUMN name HEADING 'Parameter Name' ENTMAP off
COLUMN value HEADING 'Parameter Value' ENTMAP off

SELECT
'' || a.name || '' name
, a.value value
FROM
v$parameter a
WHERE
a.name like 'log_%'
ORDER BY
a.name;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - ARCHIVING HISTORY - |
-- +----------------------------------------------------------------------------+

prompt
prompt Archiving History




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
'
' || thread# || '
' thread#
, '
' || sequence# || '
' sequence#
, name
, first_change#
, '
' || TO_CHAR(first_time, 'mm/dd/yyyy HH24:MI:SS') || '
' first_time
, next_change#
, '
' || TO_CHAR(next_time, 'mm/dd/yyyy HH24:MI:SS') || '
' next_time
, (blocks * block_size) log_size
, '
' || archived || '
' archived
, '
' || applied || '
' applied
, '
' || deleted || '
' deleted
, DECODE( status
, 'A', '
Available
'
, 'D', '
Deleted
'
, 'U', '
Unavailable
'
, 'X', '
Expired
'
) status
FROM
v$archived_log
WHERE
status in ('A')
ORDER BY
thread#
, sequence#;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - FLASH RECOVERY AREA PARAMETERS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Flash Recovery Area Parameters




prompt db_recovery_file_dest_size is specified in bytes

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
'
' || i.instance_name || '
' instance_name_print
, '
' || i.thread# || '
' thread_number_print
, '
' || p.name || '
' name
, (CASE p.name
WHEN 'db_recovery_file_dest_size' THEN '
' || TO_CHAR(p.value, '999,999,999,999,999') || '
'
ELSE
'
' || NVL(p.value, '(null)') || '
'
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
[Top]






-- +----------------------------------------------------------------------------+
-- | - FLASH RECOVERY AREA STATUS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Flash Recovery Area Status




prompt Current location, disk quota, space in use, space reclaimable by deleting files, and number of files in the Flash Recovery Area

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
'
' || name || '
' 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
'
' || file_type || '
' 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
[Top]








-- +============================================================================+
-- | |
-- | <<<<< FLASHBACK TECHNOLOGIES >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

Flashback Technologies



-- +----------------------------------------------------------------------------+
-- | - FLASHBACK DATABASE PARAMETERS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Flashback Database Parameters


prompt db_flashback_retention_target is specified in minutes
prompt db_recovery_file_dest_size is specified in bytes

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
'
' || i.instance_name || '
' instance_name_print
, '
' || i.thread# || '
' thread_number_print
, '
' || p.name || '
' name
, (CASE p.name
WHEN 'db_recovery_file_dest_size' THEN '
' || TO_CHAR(p.value, '999,999,999,999,999') || '
'
WHEN 'db_flashback_retention_target' THEN '
' || TO_CHAR(p.value, '999,999,999,999,999') || '
'
ELSE
'
' || NVL(p.value, '(null)') || '
'
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
[Top]






-- +----------------------------------------------------------------------------+
-- | - FLASHBACK DATABASE STATUS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Flashback Database Status




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
'
' || dbid || '
' dbid
, '
' || name || '
' name
, '
' || log_mode || '
' log_mode
, '
' || flashback_on || '
' 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
'
' || TO_CHAR(oldest_flashback_time,'mm/dd/yyyy HH24:MI:SS') || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - FLASHBACK DATABASE REDO TIME MATRIX - |
-- +----------------------------------------------------------------------------+

prompt
prompt Flashback Database Redo Time Matrix




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
'
' || TO_CHAR(begin_time,'mm/dd/yyyy HH24:MI:SS') || '
' begin_time
, '
' || TO_CHAR(end_time,'mm/dd/yyyy HH24:MI:SS') || '
' end_time
, flashback_data
, db_data
, redo_data
, estimated_flashback_size
FROM
v$flashback_database_stat
ORDER BY
begin_time;

prompt
[Top]








-- +============================================================================+
-- | |
-- | <<<<< PERFORMANCE >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

Performance



-- +----------------------------------------------------------------------------+
-- | - SGA INFORMATION - |
-- +----------------------------------------------------------------------------+

prompt
prompt SGA Information


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 'Total:' OF value ON instance_name

SELECT
'
' || i.instance_name || '
' instance_name
, '
' || s.name || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - SGA TARGET ADVICE - |
-- +----------------------------------------------------------------------------+

prompt
prompt SGA Target Advice




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
'
' || i.instance_name || '
' instance_name
, p.name name
, (CASE p.name
WHEN 'sga_max_size' THEN '
' || TO_CHAR(p.value, '999,999,999,999,999') || '
'
WHEN 'sga_target' THEN '
' || TO_CHAR(p.value, '999,999,999,999,999') || '
'
ELSE
'
' || p.value || '
'
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
'
' || i.instance_name || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - SGA (ASMM) DYNAMIC COMPONENTS - |
-- +----------------------------------------------------------------------------+

prompt
prompt SGA (ASMM) Dynamic Components




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
'
' || i.instance_name || '
' 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
, '
' || NVL(TO_CHAR(sdc.last_oper_time, 'mm/dd/yyyy HH24:MI:SS'), '
') || '
' last_oper_time
, sdc.granule_size
FROM
gv$sga_dynamic_components sdc
, gv$instance i
ORDER BY
i.instance_name
, sdc.component DESC;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - PGA TARGET ADVICE - |
-- +----------------------------------------------------------------------------+

prompt
prompt PGA Target Advice




prompt The V$PGA_TARGET_ADVICE 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 AUTO 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
'
' || i.instance_name || '
' instance_name
, p.name name
, (CASE p.name
WHEN 'pga_aggregate_target' THEN '
' || TO_CHAR(p.value, '999,999,999,999,999') || '
'
ELSE
'
' || p.value || '
'
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
'
' || i.instance_name || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - FILE I/O STATISTICS - |
-- +----------------------------------------------------------------------------+

prompt
prompt File I/O Statistics




prompt Ordered by "Physical Reads" since last startup of the Oracle instance

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 'Total: ' OF phyrds phywrts total_io ON report

SELECT
'' || df.tablespace_name || '' tablespace_name
, df.file_name fname
, fs.phyrds phyrds
, '
' || ROUND((fs.phyrds * 100) / (fst.pr + tst.pr), 2) || '%
' read_pct
, fs.phywrts phywrts
, '
' || ROUND((fs.phywrts * 100) / (fst.pw + tst.pw), 2) || '%
' 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
'' || tf.tablespace_name || '' tablespace_name
, tf.file_name fname
, ts.phyrds phyrds
, '
' || ROUND((ts.phyrds * 100) / (fst.pr + tst.pr), 2) || '%
' read_pct
, ts.phywrts phywrts
, '
' || ROUND((ts.phywrts * 100) / (fst.pw + tst.pw), 2) || '%
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - FILE I/O TIMINGS - |
-- +----------------------------------------------------------------------------+

prompt
prompt File I/O Timings




prompt Average time (in milliseconds) for an I/O call per datafile since last startup of the Oracle instance - (ordered by Physical Reads)

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
(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
(milliseconds per write)' ENTMAP off

BREAK ON REPORT
COMPUTE sum LABEL 'Total: ' OF phyrds phywrts ON report
COMPUTE avg LABEL 'Average: ' OF read_rate write_rate ON report

SELECT
'' || d.name || '' 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
'' || t.name || '' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - AVERAGE OVERALL I/O PER SECOND - |
-- +----------------------------------------------------------------------------+

prompt
prompt Average Overall I/O per Second




prompt Average overall I/O calls (physical read/write calls) since last startup of the Oracle instance

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('');

DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');

DBMS_OUTPUT.PUT_LINE('
Elapsed Time (in seconds)' || TO_CHAR(elapsed_time_seconds, '9,999,999,999,999') || '
Datafile I/O Calls per Second' || TO_CHAR(datafile_io_per_sec, '9,999,999,999,999') || '
Tempfile I/O Calls per Second' || TO_CHAR(tempfile_io_per_sec, '9,999,999,999,999') || '
Total I/O Calls per Second' || TO_CHAR(total_io_per_sec, '9,999,999,999,999') || '
');
END;
/

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - REDO LOG CONTENTION - |
-- +----------------------------------------------------------------------------+

prompt
prompt Redo Log Contention




prompt All latches like redo% - (ordered by misses)

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 'Total:' OF gets misses sleeps immediate_gets immediate_misses ON report

SELECT
'
' || INITCAP(name) || '
' name
, gets
, misses
, sleeps
, immediate_gets
, immediate_misses
FROM sys.v_$latch
WHERE name LIKE 'redo%'
ORDER BY 1;


prompt
prompt System statistics like redo%

CLEAR COLUMNS BREAKS COMPUTES

COLUMN name FORMAT a95 HEADING 'Statistics Name'
COLUMN value FORMAT 999,999,999,999,999 HEADING 'Value'

SELECT
'
' || INITCAP(name) || '
' name
, value
FROM v$sysstat
WHERE name LIKE 'redo%'
ORDER BY 1;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - FULL TABLE SCANS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Full Table Scans




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
, '
' || ROUND(100*a.value/DECODE((a.value+b.value),0,1,(a.value+b.value)),2) || '%
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - SORTS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Sorts




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
, '
' || ROUND(100*a.value/DECODE((a.value+b.value),0,1,(a.value+b.value)),2) || '%
' pct_disk_sorts
FROM
v$sysstat a
, v$sysstat b
WHERE
a.name = 'sorts (disk)'
AND b.name = 'sorts (memory)';

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - OUTLINES - |
-- +----------------------------------------------------------------------------+

prompt
prompt Outlines




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
'
' || category || '
' category
, owner
, name
, used
, '
' || TO_CHAR(timestamp, 'mm/dd/yyyy HH24:MI:SS') || '
' timestamp
, version
, sql_text
FROM
dba_outlines
ORDER BY
category
, owner
, name;

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - OUTLINE HINTS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Outline Hints




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
'
' || a.category || '
' category
, a.owner owner
, a.name name
, '
' || b.node || '
' node
, '
' || b.join_pos || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - SQL STATEMENTS WITH MOST BUFFER GETS - |
-- +----------------------------------------------------------------------------+

prompt
prompt SQL Statements With Most Buffer Gets




prompt Top 100 SQL statements with buffer gets greater than 1000

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
'' || UPPER(b.username) || '' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - SQL STATEMENTS WITH MOST DISK READS - |
-- +----------------------------------------------------------------------------+

prompt
prompt SQL Statements With Most Disk Reads




prompt Top 100 SQL statements with disk reads greater than 1000

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
'' || UPPER(b.username) || '' 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
[Top]








-- +============================================================================+
-- | |
-- | <<<<< AUTOMATIC WORKLOAD REPOSITORY - (AWR) >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

Automatic Workload Repository - (AWR)



-- +----------------------------------------------------------------------------+
-- | - WORKLOAD REPOSITORY INFORMATION - |
-- +----------------------------------------------------------------------------+

prompt
prompt Workload Repository Information


prompt Instances found in the "Workload Repository"
prompt The instance running this report (&_instance_name) is indicated in "GREEN"

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 '
' || wr.dbid || '
'
ELSE '
' || wr.dbid || '
'
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
[Top]




-- +----------------------------------------------------------------------------+
-- | - AWR SNAPSHOT SETTINGS - |
-- +----------------------------------------------------------------------------+

prompt
prompt AWR Snapshot Settings




prompt Use the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS 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
'
' || s.dbid || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - AWR SNAPSHOT LIST - |
-- +----------------------------------------------------------------------------+

prompt
prompt AWR Snapshot List




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
'
' || i.instance_name || '
' instance_name_print
, '
' || s.snap_id || '
' snap_id
, '
' || TO_CHAR(s.startup_time, 'mm/dd/yyyy HH24:MI:SS') || '
' startup_time
, '
' || TO_CHAR(s.begin_interval_time, 'mm/dd/yyyy HH24:MI:SS') || '
' begin_interval_time
, '
' || TO_CHAR(s.end_interval_time, 'mm/dd/yyyy HH24:MI:SS') || '
' 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
, '
' ||
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)
|| ' %
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - AWR SNAPSHOT SIZE ESTIMATES - |
-- +----------------------------------------------------------------------------+

prompt
prompt AWR Snapshot Size Estimates




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('');

DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('' );
DBMS_OUTPUT.PUT_LINE('' );
IF (num_instances > 1) THEN
DBMS_OUTPUT.PUT_LINE('' );
END IF;

DBMS_OUTPUT.PUT_LINE('');

DBMS_OUTPUT.PUT_LINE('' );
DBMS_OUTPUT.PUT_LINE('' );
IF (num_instances > 1) THEN
DBMS_OUTPUT.PUT_LINE('' );
END IF;

DBMS_OUTPUT.PUT_LINE('
Estimates based on ' || ROUND(snapshot_interval/60) || ' minute snapshot intervals
AWR size/day'
|| TO_CHAR(awr_average_size, mb_format)
|| ' MB
(' || TRIM(TO_CHAR(snap_size*1024, kb_format)) || ' K/snap * '
|| ROUND(86400/snapshot_interval) || ' snaps/day)
AWR size/wk'
|| TO_CHAR(awr_average_size * 7, mb_format)
|| ' MB
(size_per_day * 7) per instance
AWR size/wk'
|| TO_CHAR(awr_average_size * 7 * num_instances, mb_format)
|| ' MB
(size_per_day * 7) per database
Estimates based on ' || ROUND(today_snaps) || ' snaps in past 24 hours
AWR size/day'
|| TO_CHAR(awr_size_past24, mb_format)
|| ' MB
('
|| 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)
AWR size/wk'
|| TO_CHAR(awr_size_past24 * 7, mb_format)
|| ' MB
(size_per_day * 7) per instance
AWR size/wk'
|| TO_CHAR(awr_size_past24 * 7 * num_instances, mb_format)
|| ' MB
(size_per_day * 7) per database
');

END;
/

prompt
[Top]






-- +----------------------------------------------------------------------------+
-- | - AWR BASELINES - |
-- +----------------------------------------------------------------------------+

prompt
prompt AWR Baselines




prompt Use the DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE 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
'
' || b.dbid || '
' dbbid
, d.name dbb_name
, b.baseline_id baseline_id
, baseline_name baseline_name
, b.start_snap_id start_snap_id
, '
' || TO_CHAR(b.start_snap_time, 'mm/dd/yyyy HH24:MI:SS') || '
' start_snap_time
, b.end_snap_id end_snap_id
, '
' || TO_CHAR(b.end_snap_time, 'mm/dd/yyyy HH24:MI:SS') || '
' end_snap_time
FROM
dba_hist_baseline b
, v$database d
WHERE
b.dbid = d.dbid
ORDER BY
dbbid
, b.baseline_id;

prompt
[Top]








-- +============================================================================+
-- | |
-- | <<<<< SESSIONS >>>>> |
-- | |
-- +============================================================================+


prompt
prompt

Sessions



-- +----------------------------------------------------------------------------+
-- | - CURRENT SESSIONS - |
-- +----------------------------------------------------------------------------+

prompt
prompt Current Sessions


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
'
' || a.instance_name || '
' instance_name_print
, '
' || a.thread# || '
' thread_number_print
, '
' || TO_CHAR(a.count) || '
' count
, '
' || b.value || '
' value
, '
' || TO_CHAR(ROUND(100*(a.count / b.value), 2)) || '%
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - USER SESSION MATRIX - |
-- +----------------------------------------------------------------------------+

prompt
prompt User Session Matrix




prompt User sessions (excluding SYS and background processes)

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
'
' || i.instance_name || '
' instance_name_print
, '
' || i.thread# || '
' thread_number_print
, '
' || NVL(sess.username, '[B.G. Process]') || '
' 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
[Top]






-- +----------------------------------------------------------------------------+
-- | - ENABLED TRACES - |
-- +----------------------------------------------------------------------------+

prompt
prompt Enabled Traces




prompt End-to-End Application Tracing from View DBA_ENABLED_TRACES.
prompt
  • Trace Type: Possible values are CLIENT_ID, SESSION, SERVICE, SERVICE_MODULE, SERVICE_MODULE_ACTION, and DATABASE, based on the type of tracing enabled.
    prompt
  • Primary ID: Specific client identifier (username) or service name.
    prompt



    prompt Application tracing is enabled using the DBMS_MONITOR package and the following procedures:
    prompt

  • CLIENT_ID_TRACE_ENABLE: Enable tracing based on client identifier (username).
    prompt
  • CLIENT_ID_TRACE_DISABLE: Disable client identifier tracing.
    prompt
  • SESSION_TRACE_ENABLE: Enable tracing based on SID and SERIAL# of V$SESSION.
    prompt
  • SESSION_TRACE_DISABLE: Disable session tracing.
    prompt
  • SERV_MOD_ACT_TRACE_ENABLE: Enable tracing for a given combination of service name, module, and action.
    prompt
  • SERV_MOD_ACT_TRACE_DISABLE: Disable service, module, and action tracing.
    prompt
  • DATABASE_TRACE_ENABLE: Enable tracing for the entire database.
    prompt
  • DATABASE_TRACE_DISABLE: Disable database tracing.
    prompt



    prompt Hint: 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 Oracle10g introduces the trcsess 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
    '

    ' || trace_type || '
    ' trace_type
    , '
    ' || NVL(primary_id, '
    ') || '
    ' primary_id
    , '
    ' || NVL(qualifier_id1, '
    ') || '
    ' qualifier_id1
    , '
    ' || NVL(qualifier_id2, '
    ') || '
    ' qualifier_id2
    , '
    ' || waits || '
    ' waits
    , '
    ' || binds || '
    ' binds
    , '
    ' || NVL(instance_name, '
    ') || '
    ' instance_name_print
    FROM
    dba_enabled_traces
    ORDER BY
    trace_type
    , primary_id
    , qualifier_id1
    , qualifier_id2;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - ENABLED AGGREGATIONS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Enabled Aggregations




    prompt Statistics Aggregation from View DBA_ENABLED_AGGREGATIONS.
    prompt
  • Aggregation Type: Possible values are CLIENT_ID, SERVICE_MODULE, and SERVICE_MODULE_ACTION, based on the type of statistics being gathered.
    prompt
  • Primary ID: Specific client identifier (username) or service name.
    prompt



    prompt Statistics aggregation is enabled using the DBMS_MONITOR package and the following procedures.
    prompt Note that statistics gathering is global for the database and is persistent across instance starts
    prompt and restarts.
    prompt

  • CLIENT_ID_STAT_ENABLE: Enable statistics gathering based on client identifier (username).
    prompt
  • CLIENT_ID_STAT_DISABLE: Disable client identifier statistics gathering.
    prompt
  • SERV_MOD_ACT_STAT_ENABLE: Enable statistics gathering for a given combination of service name, module, and action.
    prompt
  • SERV_MOD_ACT_STAT_DISABLE: Disable service, module, and action statistics gathering.
    prompt



    prompt Hint: 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
    '

    ' || aggregation_type || '
    ' aggregation_type
    , '
    ' || NVL(primary_id, '
    ') || '
    ' primary_id
    , '
    ' || NVL(qualifier_id1, '
    ') || '
    ' qualifier_id1
    , '
    ' || NVL(qualifier_id2, '
    ') || '
    ' qualifier_id2
    FROM
    dba_enabled_aggregations
    ORDER BY
    aggregation_type
    , primary_id
    , qualifier_id1
    , qualifier_id2;

    prompt
    [Top]








    -- +============================================================================+
    -- | |
    -- | <<<<< SECURITY >>>>> |
    -- | |
    -- +============================================================================+


    prompt
    prompt

    Security



    -- +----------------------------------------------------------------------------+
    -- | - USER ACCOUNTS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt User Accounts


    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
    '' || a.username || '' username
    , DECODE( a.account_status
    , 'OPEN'
    , '
    ' || a.account_status || '
    '
    , '
    ' || a.account_status || '
    ') account_status
    , '
    ' || NVL(TO_CHAR(a.expiry_date, 'mm/dd/yyyy HH24:MI:SS'), '
    ') || '
    ' expiry_date
    , a.default_tablespace default_tablespace
    , a.temporary_tablespace temporary_tablespace
    , '
    ' || TO_CHAR(a.created, 'mm/dd/yyyy HH24:MI:SS') || '
    ' created
    , a.profile profile
    , '
    ' || NVL(DECODE(p.sysdba,'TRUE', 'TRUE',''), '
    ') || '
    ' sysdba
    , '
    ' || NVL(DECODE(p.sysoper,'TRUE','TRUE',''), '
    ') || '
    ' sysoper
    FROM
    dba_users a
    , v$pwfile_users p
    WHERE
    p.username (+) = a.username
    ORDER BY
    username;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - USERS WITH DBA PRIVILEGES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Users With DBA Privileges




    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
    '' || grantee || '' grantee
    , '
    ' || granted_role || '
    ' granted_role
    , DECODE( admin_option
    , 'YES'
    , '
    ' || admin_option || '
    '
    , 'NO'
    , '
    ' || admin_option || '
    '
    , '
    ' || admin_option || '
    ') admin_option
    , DECODE( default_role
    , 'YES'
    , '
    ' || default_role || '
    '
    , 'NO'
    , '
    ' || default_role || '
    '
    , '
    ' || default_role || '
    ') default_role
    FROM
    dba_role_privs
    WHERE
    granted_role = 'DBA'
    ORDER BY
    grantee
    , granted_role;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - ROLES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Roles




    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.role || '' role
    , a.grantee grantee
    , DECODE( a.admin_option
    , null
    , '
    '
    , 'YES'
    , '
    ' || a.admin_option || '
    '
    , 'NO'
    , '
    ' || a.admin_option || '
    '
    , '
    ' || a.admin_option || '
    ') admin_option
    , DECODE( a.default_role
    , null
    , '
    '
    , 'YES'
    , '
    ' || a.default_role || '
    '
    , 'NO'
    , '
    ' || a.default_role || '
    '
    , '
    ' || a.default_role || '
    ') default_role
    FROM
    dba_role_privs a
    , dba_roles b
    WHERE
    granted_role(+) = b.role
    ORDER BY
    b.role
    , a.grantee;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DEFAULT PASSWORDS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Default Passwords




    prompt User(s) with default password

    CLEAR COLUMNS BREAKS COMPUTES

    COLUMN username HEADING 'Username' ENTMAP off
    COLUMN account_status FORMAT a75 HEADING 'Account Status' ENTMAP off

    SELECT
    '' || username || '' username
    , DECODE( account_status
    , 'OPEN'
    , '
    ' || account_status || '
    '
    , '
    ' || account_status || '
    ') 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DB LINKS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt DB Links




    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
    '' || owner || '' owner
    , db_link
    , username
    , host
    , '
    ' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '
    ' created
    FROM dba_db_links
    ORDER BY owner, db_link;

    prompt
    [Top]








    -- +============================================================================+
    -- | |
    -- | <<<<< OBJECTS >>>>> |
    -- | |
    -- +============================================================================+


    prompt
    prompt

    Objects



    -- +----------------------------------------------------------------------------+
    -- | - OBJECT SUMMARY - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Object Summary


    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 'Grand Total: ' of obj_count on report
    COMPUTE sum LABEL 'Total: ' OF obj_count ON report

    SELECT
    '' || owner || '' owner
    , object_type object_type
    , count(*) obj_count
    FROM
    dba_objects
    GROUP BY
    owner
    , object_type
    ORDER BY
    owner
    , object_type;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - SEGMENT SUMMARY - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Segment Summary




    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 'Total: ' OF seg_count bytes ON report

    SELECT
    '' || owner || '' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - TOP 100 SEGMENTS (BY SIZE) - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Top 100 Segments (by size)




    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 'Total: ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - TOP 100 SEGMENTS (BY EXTENTS) - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Top 100 Segments (by number of extents)




    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 'Total: ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DIRECTORIES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Directories




    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
    '
    ' || owner || '
    ' owner
    , '' || directory_name || '' directory_name
    , '' || directory_path || '' directory_path
    FROM
    dba_directories
    ORDER BY
    owner
    , directory_name;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DIRECTORY PRIVILEGES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Directory Privileges




    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
    '' || table_name || '' table_name
    , '' || grantee || '' grantee
    , privilege privilege
    , DECODE( grantable
    , 'YES'
    , '
    ' || grantable || '
    '
    , 'NO'
    , '
    ' || grantable || '
    '
    , '
    ' || grantable || '
    ') grantable
    FROM
    dba_tab_privs
    WHERE
    privilege IN ('READ', 'WRITE')
    ORDER BY
    table_name
    , grantee
    , privilege;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - LIBRARIES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Libraries




    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
    '
    ' || owner || '
    ' owner
    , '' || library_name || '' library_name
    , file_spec file_spec
    , '
    ' || dynamic || '
    ' dynamic
    , DECODE( status
    , 'VALID'
    , '
    ' || status || '
    '
    , '
    ' || status || '
    ' ) status
    FROM
    dba_libraries
    ORDER BY
    owner
    , library_name;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - TYPES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Types




    prompt Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)

    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
    '
    ' || t.owner || '
    ' owner
    , '
    ' || t.type_name || '
    ' type_name
    , '
    ' || t.typecode || '
    ' typecode
    , '
    ' || TO_CHAR(t.attributes, '999,999') || '
    ' attributes
    , '
    ' || TO_CHAR(t.methods, '999,999') || '
    ' methods
    , '
    ' || t.predefined || '
    ' predefined
    , '
    ' || t.incomplete || '
    ' incomplete
    , '
    ' || t.final || '
    ' final
    , '
    ' || t.instantiable || '
    ' instantiable
    , '
    ' || NVL(t.supertype_owner, '
    ') || '
    ' supertype_owner
    , '
    ' || NVL(t.supertype_name, '
    ') || '
    ' supertype_name
    , '
    ' || NVL(TO_CHAR(t.local_attributes, '999,999'), '
    ') || '
    ' local_attributes
    , '
    ' || NVL(TO_CHAR(t.local_methods, '999,999'), '
    ') || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - TYPE ATTRIBUTES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Type Attributes




    prompt Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)

    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
    '
    ' || t.owner || '
    ' owner
    , '
    ' || t.type_name || '
    ' type_name
    , '
    ' || t.typecode || '
    ' typecode
    , '
    ' || a.attr_name || '
    ' 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'
    , '
    ' || a.inherited || '
    '
    , 'NO'
    , '
    ' || a.inherited || '
    '
    , '
    ' || a.inherited || '
    ') 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - TYPE METHODS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Type Methods




    prompt Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)

    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
    '
    ' || t.owner || '
    ' owner
    , '
    ' || t.type_name || '
    ' type_name
    , '
    ' || t.typecode || '
    ' typecode
    , '
    ' || m.method_name || '
    ' method_name
    , '
    ' || m.method_type || '
    ' method_type
    , '
    ' || TO_CHAR(m.parameters, '999,999') || '
    ' num_parameters
    , '
    ' || TO_CHAR(m.results, '999,999') || '
    ' results
    , '
    ' || m.final || '
    ' final
    , '
    ' || m.instantiable || '
    ' instantiable
    , '
    ' || m.overriding || '
    ' overriding
    , DECODE( m.inherited
    , 'YES'
    , '
    ' || m.inherited || '
    '
    , 'NO'
    , '
    ' || m.inherited || '
    '
    , '
    ' || m.inherited || '
    ') 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - COLLECTIONS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Collections




    prompt Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)

    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
    '
    ' || c.owner || '
    ' owner
    , '
    ' || c.type_name || '
    ' type_name
    , '
    ' || c.coll_type || '
    ' coll_type
    , '
    ' || NVL(TO_CHAR(c.upper_bound, '9,999,999,999'), '
    ') || '
    ' upper_bound
    , '
    ' || NVL(c.elem_type_owner, '
    ') || '
    ' 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
    , '
    ' || NVL(c.character_set_name, '
    ') || '
    ' character_set_name
    , '
    ' || NVL(c.elem_storage, '
    ') || '
    ' elem_storage
    , DECODE( c.nulls_stored
    , 'YES'
    , '
    ' || c.nulls_stored || '
    '
    , 'NO'
    , '
    ' || c.nulls_stored || '
    '
    , '
    ' || c.nulls_stored || '
    ') 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - LOB SEGMENTS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt LOB Segments




    prompt Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)

    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
    '
    ' || l.owner || '
    ' owner
    , '
    ' || l.table_name || '
    ' table_name
    , '
    ' || l.column_name || '
    ' column_name
    , '
    ' || l.segment_name || '
    ' segment_name
    , '
    ' || s.tablespace_name || '
    ' tablespace_name
    , '
    ' || TO_CHAR(s.bytes, '999,999,999,999,999') || '
    ' lob_segment_bytes
    , '
    ' || l.index_name || '
    ' index_name
    , DECODE( l.in_row
    , 'YES'
    , '
    ' || l.in_row || '
    '
    , 'NO'
    , '
    ' || l.in_row || '
    '
    , '
    ' || l.in_row || '
    ') 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - OBJECTS UNABLE TO EXTEND - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Objects Unable to Extend




    prompt Segments that cannot extend because of MAXEXTENTS or not enough space

    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
    '
    ' || ds.owner || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - OBJECTS WHICH ARE NEARING MAXEXTENTS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Objects Which Are Nearing MAXEXTENTS




    prompt Segments where number of EXTENTS is less than 1/2 of MAXEXTENTS

    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
    , '
    ' || ROUND((extents/max_extents)*100, 2) || '%
    ' pct_util
    FROM
    dba_segments
    WHERE
    extents > max_extents/2
    AND max_extents != 0
    ORDER BY
    (extents/max_extents) DESC;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - INVALID OBJECTS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Invalid Objects




    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 'Grand Total: ' OF object_name ON report

    SELECT
    '
    ' || owner || '
    ' owner
    , object_name
    , object_type
    , DECODE( status
    , 'VALID'
    , '
    ' || status || '
    '
    , '
    ' || status || '
    ' ) status
    FROM dba_objects
    WHERE status <> 'VALID'
    ORDER BY
    owner
    , object_name;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - PROCEDURAL OBJECT ERRORS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Procedural Object Errors




    prompt All records from DBA_ERRORS

    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
    '
    ' || owner || '
    ' owner
    , name
    , type
    , sequence
    , line
    , position
    , text
    FROM
    dba_errors
    ORDER BY
    1
    , 2
    , 3;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - OBJECTS WITHOUT STATISTICS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Objects Without Statistics




    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 'Total: ' OF object_name ON report

    SELECT
    '
    ' || owner || '
    ' 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
    '
    ' || owner || '
    ' 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
    '
    ' || table_owner || '
    ' 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
    '
    ' || index_owner || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - TABLES SUFFERING FROM ROW CHAINING/MIGRATION - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Tables Suffering From Row Chaining/Migration




    prompt NOTE: Tables must have statistics gathered

    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
    , '
    ' || ROUND((chain_cnt/num_rows)*100, 2) || '%
    ' 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
    , '
    ' || ROUND((chain_cnt/num_rows)*100, 2) || '%
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - USERS WITH DEFAULT TABLESPACE - (SYSTEM) - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Users With Default Tablespace - (SYSTEM)




    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
    '' || username || '' username
    , '
    ' || default_tablespace || '
    ' default_tablespace
    , '
    ' || temporary_tablespace || '
    ' temporary_tablespace
    , '
    ' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '
    ' created
    , DECODE( account_status
    , 'OPEN'
    , '
    ' || account_status || '
    '
    , '
    ' || account_status || '
    ') account_status
    FROM
    dba_users
    WHERE
    default_tablespace = 'SYSTEM'
    ORDER BY
    username;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - USERS WITH DEFAULT TEMPORARY TABLESPACE - (SYSTEM) - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Users With Default Temporary Tablespace - (SYSTEM)




    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
    '' || username || '' username
    , '
    ' || default_tablespace || '
    ' default_tablespace
    , '
    ' || temporary_tablespace || '
    ' temporary_tablespace
    , '
    ' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '
    ' created
    , DECODE( account_status
    , 'OPEN'
    , '
    ' || account_status || '
    '
    , '
    ' || account_status || '
    ') account_status
    FROM
    dba_users
    WHERE
    temporary_tablespace = 'SYSTEM'
    ORDER BY
    username;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - OBJECTS IN THE SYSTEM TABLESPACE - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Objects in the SYSTEM Tablespace




    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 'Total Count: ' OF segment_name ON report
    COMPUTE sum LABEL 'Total Bytes: ' OF bytes ON report

    SELECT
    '
    ' || owner || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - RECYCLE BIN - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Recycle Bin




    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
    '
    ' || owner || '
    ' owner
    , original_name
    , type
    , object_name
    , ts_name
    , operation
    , '
    ' || NVL(createtime, '
    ') || '
    ' createtime
    , '
    ' || NVL(droptime, '
    ') || '
    ' droptime
    , DECODE( can_undrop
    , null
    , '
    '
    , 'YES'
    , '
    ' || can_undrop || '
    '
    , 'NO'
    , '
    ' || can_undrop || '
    '
    , '
    ' || can_undrop || '
    ') can_undrop
    , DECODE( can_purge
    , null
    , '
    '
    , 'YES'
    , '
    ' || can_purge || '
    '
    , 'NO'
    , '
    ' || can_purge || '
    '
    , '
    ' || can_purge || '
    ') 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
    [Top]








    -- +============================================================================+
    -- | |
    -- | <<<<< ONLINE ANALYTICAL PROCESSING - (OLAP) >>>>> |
    -- | |
    -- +============================================================================+


    prompt
    prompt

    Online Analytical Processing - (OLAP)



    -- +----------------------------------------------------------------------------+
    -- | - DIMENSIONS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Dimensions


    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
    '
    ' || dd.owner || '
    ' owner
    , dd.dimension_name dimension_name
    , '
    ' || dd.invalid || '
    ' invalid
    , DECODE( dd.compile_state
    , 'VALID'
    , '
    ' || dd.compile_state || '
    '
    , '
    ' || dd.compile_state || '
    ' ) compile_state
    , '
    ' || dd.revision || '
    ' revision
    FROM
    dba_dimensions dd
    ORDER BY
    dd.owner
    , dd.dimension_name;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DIMENSION LEVELS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Dimension Levels




    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
    '
    ' || d.owner || '
    ' 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
    , '
    ' || TO_CHAR(k.key_position, '999,999') || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DIMENSION ATTRIBUTES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Dimension Attributes




    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
    '
    ' || d.owner || '
    ' 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
    , '
    ' || a.inferred || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DIMENSION HIERARCHIES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Dimension Hierarchies




    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
    '
    ' || d.owner || '
    ' 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
    , '
    ' || TO_CHAR(c.position, '999,999') || '
    ' position
    , '
    ' || NVL(c.join_key_id,'
    ') || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - CUBES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Cubes




    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
    '
    ' || c.owner || '
    ' owner
    , c.cube_name cube_name
    , DECODE( c.invalid
    , 'O'
    , '
    Yes
    '
    , '1'
    , '
    No
    '
    , 'Y'
    , '
    No
    '
    , 'N'
    , '
    Yes
    '
    , '
    ' || invalid || '
    ') invalid
    , c.display_name display_name
    , REPLACE(REPLACE(c.description, '<', '\<'), '>', '\>') description
    FROM
    dba_olap_cubes c
    ORDER BY
    c.owner
    , c.cube_name;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - MATERIALIZED VIEWS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Materialized Views




    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
    '
    ' || m.owner || '
    ' owner
    , m.mview_name mview_name
    , m.master_link master_link
    , '
    ' || NVL(m.updatable,'
    ') || '
    ' updatable
    , update_log update_log
    , '
    ' || NVL(m.rewrite_enabled,'
    ') || '
    ' 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
    , '
    ' || TO_CHAR(m.last_refresh_date, 'mm/dd/yyyy HH24:MI:SS') || '
    ' last_refresh_date
    , m.staleness staleness
    , DECODE( m.compile_state
    , 'VALID'
    , '
    ' || m.compile_state || '
    '
    , '
    ' || m.compile_state || '
    ' ) compile_state
    FROM
    dba_mviews m
    ORDER BY
    owner
    , mview_name
    /

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - MATERIALIZED VIEW LOGS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Materialized View Logs




    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
    '
    ' || ml.log_owner || '
    ' log_owner
    , ml.log_table log_table
    , ml.master master
    , ml.log_trigger log_trigger
    , '
    ' || NVL(ml.rowids,'
    ') || '
    ' rowids
    , '
    ' || NVL(ml.primary_key,'
    ') || '
    ' primary_key
    , '
    ' || NVL(ml.object_id,'
    ') || '
    ' object_id
    , '
    ' || NVL(ml.filter_columns,'
    ') || '
    ' filter_columns
    , '
    ' || NVL(ml.sequence,'
    ') || '
    ' sequence
    , '
    ' || NVL(ml.include_new_values,'
    ') || '
    ' include_new_values
    FROM
    dba_mview_logs ml
    ORDER BY
    ml.log_owner
    , ml.master;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - MATERIALIZED VIEW REFRESH GROUPS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Materialized View Refresh Groups




    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
    '
    ' || rowner || '
    ' owner
    , '
    ' || rname || '
    ' name
    , '
    ' || broken || '
    ' broken
    , '
    ' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
    ') || '
    ' next_date
    , '
    ' || interval || '
    ' interval
    FROM
    dba_refresh
    ORDER BY
    rowner
    , rname
    /

    prompt
    [Top]








    -- +============================================================================+
    -- | |
    -- | <<<<< DATA PUMP >>>>> |
    -- | |
    -- +============================================================================+


    prompt
    prompt

    Data Pump



    -- +----------------------------------------------------------------------------+
    -- | - DATA PUMP JOBS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Data Pump Jobs


    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
    '
    ' || dpj.owner_name || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DATA PUMP SESSIONS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Data Pump Sessions




    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
    '
    ' || i.instance_name || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DATA PUMP JOB PROGRESS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Data Pump Job Progress




    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
    '
    ' || i.instance_name || '
    ' instance_name_print
    , dj.owner_name owner_name
    , dj.job_name job_name
    , ds.type session_type
    , '
    ' || TO_CHAR(sl.start_time,'mm/dd/yyyy HH24:MI:SS') || '
    ' start_time
    , ROUND(sl.time_remaining/60,0) time_remaining
    , sl.sofar sofar
    , sl.totalwork totalwork
    , '
    ' || TRUNC(ROUND((sl.sofar/sl.totalwork) * 100, 1)) || '%
    ' 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
    [Top]








    -- +============================================================================+
    -- | |
    -- | <<<<< NETWORKING >>>>> |
    -- | |
    -- +============================================================================+


    prompt
    prompt

    Networking



    -- +----------------------------------------------------------------------------+
    -- | - MTS DISPATCHER STATISTICS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt MTS Dispatcher Statistics


    prompt Dispatcher rate

    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 Dispatcher busy rate

    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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - MTS DISPATCHER RESPONSE QUEUE WAIT STATS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt MTS Dispatcher Response Queue Wait Stats




    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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - MTS SHARED SERVER WAIT STATISTICS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt MTS Shared Server Wait Statistics




    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
    [Top]








    -- +============================================================================+
    -- | |
    -- | <<<<< REPLICATION >>>>> |
    -- | |
    -- +============================================================================+


    prompt
    prompt

    Replication



    -- +----------------------------------------------------------------------------+
    -- | - REPLICATION SUMMARY - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Replication Summary


    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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - DEFERRED TRANSACTIONS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Deferred Transactions




    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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - ADMINISTRATIVE REQUEST JOBS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Administrative Request Jobs




    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
    , '
    ' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
    ') || '
    ' next_date
    , interval
    FROM
    sys.dba_jobs
    WHERE
    what LIKE '%dbms_repcat.do_deferred_repcat_admin%'
    ORDER BY
    1;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - INITIALIZATION PARAMETERS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt Initialization Parameters




    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'
    , '' || SUBSTR(name,0,512) || ''
    , '' || SUBSTR(name,0,512) || '' ) pname
    , DECODE( isdefault
    , 'FALSE'
    , '' || SUBSTR(value,0,512) || ''
    , SUBSTR(value,0,512) ) value
    , DECODE( isdefault
    , 'FALSE'
    , '
    ' || isdefault || '
    '
    , '
    ' || isdefault || '
    ') isdefault
    , DECODE( isdefault
    , 'FALSE'
    , '
    ' || issys_modifiable || '
    '
    , '
    ' || issys_modifiable || '
    ') 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (SCHEDULE) - PURGE JOBS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Schedule) - Purge Jobs




    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
    , '
    ' || NVL(TO_CHAR(s.next_date, 'mm/dd/yyyy HH24:MI:SS'), '
    ') || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (SCHEDULE) - PUSH JOBS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Schedule) - Push Jobs




    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
    , '
    ' || NVL(TO_CHAR(s.next_date, 'mm/dd/yyyy HH24:MI:SS'), '
    ') || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (SCHEDULE) - REFRESH JOBS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Schedule) - Refresh Jobs




    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
    , '
    ' || NVL(TO_CHAR(j.next_date, 'mm/dd/yyyy HH24:MI:SS'), '
    ') || '
    ' next_date
    , '
    ' || j.interval || '
    ' interval
    FROM
    sys.dba_refresh r
    , sys.dba_jobs j
    WHERE
    r.job = j.job
    order by
    1;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MULTI-MASTER) - MASTER GROUPS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Multi-Master) - Master Groups




    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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MULTI-MASTER) - MASTER GROUPS AND SITES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Multi-Master) - Master Groups and Sites




    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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MATERIALIZED VIEW) - MASTER SITE SUMMARY - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Materialized View) - Master Site Summary




    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
    '
    ' || log_owner || '
    ' log_owner
    , log_table
    , master
    , '
    ' || rowids || '
    ' rowids
    , '
    ' || primary_key || '
    ' primary_key
    , '
    ' || filter_columns || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MATERIALIZED VIEW) - MASTER SITE LOGS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Materialized View) - Master Site Logs




    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
    '
    ' || log_owner || '
    ' log_owner
    , log_table
    , master
    , '
    ' || rowids || '
    ' rowids
    , '
    ' || primary_key || '
    ' primary_key
    , '
    ' || filter_columns || '
    ' filter_columns
    FROM
    sys.dba_snapshot_logs
    ORDER BY
    log_owner;

    prompt
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MATERIALIZED VIEW) - MASTER SITE TEMPLATES - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Materialized View) - Master Site Templates




    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
    '
    ' || owner || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MATERIALIZED VIEW) - SITE SUMMARY - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Materialized View) - Site Summary




    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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MATERIALIZED VIEW) - SITE GROUPS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Materialized View) - Site Groups




    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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MATERIALIZED VIEW) - SITE MATERIALIZED VIEWS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Materialized View) - Site Materialized Views




    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
    '
    ' || s.owner || '
    ' owner
    , s.name name
    , s.master_owner master_owner
    , s.master master_table
    , s.master_link master_link
    , nls_initcap(s.type) type
    , '
    ' || DECODE(s.updatable, 'YES', 'YES', 'NO') || '
    ' updatable
    , '
    ' || DECODE(s.can_use_log,'YES', 'YES', 'NO') || '
    ' can_use_log
    , '
    ' || NVL(TO_CHAR(m.last_refresh_date, 'mm/dd/yyyy HH24:MI:SS'), '
    ') || '
    ' 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
    [Top]






    -- +----------------------------------------------------------------------------+
    -- | - (MATERIALIZED VIEW) - SITE REFRESH GROUPS - |
    -- +----------------------------------------------------------------------------+

    prompt
    prompt (Materialized View) - Site Refresh Groups




    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
    '
    ' || rowner || '
    ' owner
    , '
    ' || rname || '
    ' name
    , '
    ' || broken || '
    ' broken
    , '
    ' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
    ') || '
    ' next_date
    , '
    ' || interval || '
    ' interval
    FROM
    sys.dba_refresh
    ORDER BY
    rowner
    , rname;

    prompt
    [Top]










    -- +----------------------------------------------------------------------------+
    -- | - END OF REPORT - |
    -- +----------------------------------------------------------------------------+

    SPOOL OFF

    SET MARKUP HTML OFF

    SET TERMOUT ON
    prompt
    prompt Output written to: &FileName._&_dbname._&_spool_time..html

    EXIT;

    Hope it helps.


    Best regards,

    Rafi.

  • No comments:

    Post a Comment