update : 28-06-2017

DBA Script : dba_snapshot_database_9i.sql

-- | FILE : dba_snapshot_database_9i.sql |
-- +----------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_snapshot_database_9i.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 Oracle9i. |
-- | USAGE : |
-- | |
-- | sqlplus -s /@ @dba_snapshot_database_9i.sql |
-- | |
-- | TESTING : This script has been successfully tested on the following |
-- | platforms: |
-- | |
-- | Solaris : Oracle Database 9.2.0.8.0 |
-- | |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
define reportHeader="Snapshot Database 9i
www.high-oracle.com (www.idevelopment.info)

"

-- +----------------------------------------------------------------------------+
-- | 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_9i
define versionNumber=3.6
define statsPackUser=PERFSTAT
-- +----------------------------------------------------------------------------+
-- | 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 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 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 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 &FileName._&_dbname._&_spool_time..html
set markup html on entmap off
-- +----------------------------------------------------------------------------+
-- | - REPORT HEADER - |
-- +----------------------------------------------------------------------------+
prompt &reportHeader
-- +----------------------------------------------------------------------------+
-- | - REPORT INDEX - |
-- +----------------------------------------------------------------------------+
prompt
prompt
Report Index
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Database and Instance Information
Report Header Version Options Database Registry
Instance Overview Database Overview Initialization Parameters Control Files
Control File Records Online Redo Logs Redo Log Switches
Jobs
Jobs


Storage
Tablespaces Data Files Database Growth Tablespace Extents
Tablespace to Owner Owner to Tablespace

UNDO Segments
UNDO Segments UNDO Segment Contention

Archiving
Archiving Mode Archiving Parameters Archiving History
Recovery Manager - (RMAN)
RMAN Configuration Backup Sets Backup Pieces Backup Control Files
Backup SPFILE


Performance
SGA Information DB Buffer Cache Hit Ratio Dictionary Cache Hit Ratio Library Cache Hit Ratio
Latch Contention System Wait Statistics System Statistics System Event Statistics
File I/O Statistics File I/O Timings Average Overall I/O per Second Redo Log Contention
Full Table Scans Sorts Outlines Outline Hints
SQL Statements With Most Buffer Gets SQL Statements With Most Disk Reads Top 10 Tables Top 10 Procedures
Statspack
List Parameters

Sessions
Current Sessions User Session Matrix

Security
User Accounts Users With DBA Privileges Roles Default Passwords
DB Links


Objects
Object Summary Segment Summary Top 200 Segments (by size) Top 200 Segments (by number of extents)
Directories Libraries Types Type Attributes
Type Methods Collections LOB Segments Objects Unable to Extend
Objects Which Are Nearing MAXEXTENTS Invalid Objects Procedural Object Errors Objects Without Statistics
Tables Suffering From Row Chaining/Migration Users With Default Tablespace - (SYSTEM) Users With Default Temp Tablespace - (SYSTEM) Objects in the SYSTEM Tablespace
Online Analytical Processing - (OLAP)
Dimensions Dimension Levels Dimension Attributes Dimension Hierarchies
Cubes Materialized Views Materialized View Logs
Networking
MTS Dispatcher Statistics MTS Dispatcher Response Queue Wait Stats MTS Shared Server Wait Statistics
Replication
Replication Summary Deferred Transactions Administrative Request Jobs Initialization 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 >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - REPORT HEADER - |
-- +----------------------------------------------------------------------------+
prompt
prompt
prompt Report Header
prompt -
-
-
-
-
-
-
-
-
-
-
-
-
Report Name&FileName._&_dbname._&_spool_time..html
Snapshot Database Version&versionNumber
Run Date / Time&_date_time
Host Name&_host_name
Database Name&_dbname
Global Database Name&_global_name
Clustered Database?&_cluster_database
Clustered Database Instances&_cluster_database_instances
Database Startup Time&_startup_time
Database Block Size&_blocksize
Report Run User&_reportRunUser
Statspack User&statsPackUser
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 version
, DECODE( status
, 'VALID', '
' || status || '
'
, 'INVALID', '
' || status || '
'
, '
' || status || '
' ) status
, '
' || modified || '
' modified
, control control
, schema schema
, procedure procedure
FROM dba_registry
ORDER BY comp_name;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - INSTANCE OVERVIEW - |
-- +----------------------------------------------------------------------------+
prompt
prompt Instance Overview
CLEAR COLUMNS BREAKS COMPUTES
COLUMN instance_name FORMAT a75 HEADING 'Instance|Name' ENTMAP off
COLUMN instance_number FORMAT a75 HEADING 'Instance|Number' ENTMAP off
COLUMN host_name_print FORMAT a75 HEADING 'Host|Name' ENTMAP off
COLUMN oracle_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 status FORMAT a75 HEADING 'Instance|Status' ENTMAP off
COLUMN logins FORMAT a75 HEADING 'Logins' ENTMAP off
COLUMN archiver FORMAT a75 HEADING 'Archiver' ENTMAP off
SELECT
'
' || instance_name || '
' instance_name
, '
' || instance_number || '
' instance_number
, '
' || host_name || '
' host_name_print
, '
' || version || '
' oracle_version
, '
' || TO_CHAR(startup_time,'mm/dd/yyyy HH24:MI:SS') || '
' start_time
, ROUND(TO_CHAR(SYSDATE-startup_time), 2) uptime
, '
' || parallel || '
' parallel
, '
' || status || '
' status
, '
' || logins || '
' logins
, DECODE( archiver
, 'FAILED'
, '
' || archiver || '
'
, '
' || archiver || '
') archiver
FROM v$instance
ORDER BY instance_number;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - DATABASE OVERVIEW - |
-- +----------------------------------------------------------------------------+
prompt
prompt Database Overview
CLEAR COLUMNS BREAKS COMPUTES
COLUMN database_name FORMAT a75 HEADING 'Database Name' ENTMAP off
COLUMN dbid HEADING 'Database ID' ENTMAP off
COLUMN creation_date FORMAT a75 HEADING 'Creation Date' 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 controlfile_type HEADING 'Controlfile Type' ENTMAP off
SELECT
'
' || name || '
' database_name
, '
' || dbid || '
' dbid
, '
' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '
' creation_date
, '
' || log_mode || '
' log_mode
, '
' || open_mode || '
' open_mode
, '
' || force_logging || '
' force_logging
, '
' || controlfile_type || '
' controlfile_type
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 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
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
ORDER BY
name;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - CONTROL FILES - |
-- +----------------------------------------------------------------------------+
prompt
prompt Control Files
CLEAR COLUMNS BREAKS COMPUTES
COLUMN name HEADING 'Controlfile Name' ENTMAP off
COLUMN status HEADING 'Status' ENTMAP off
SELECT
'' || c.name || '' name
, DECODE( c.status
, NULL
, '
VALID
'
, '
' || c.status || '
') status
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
, records_total
, (records_total * record_size) bytes_alloc
, records_used
, (records_used * record_size) bytes_used
, NVL(records_used/records_total * 100, 0) pct_used
, first_index
, last_index
, 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 groupno FORMAT a75 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 groupno
SELECT
'
' || 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
v$logfile f
, v$log l
WHERE
f.group# = l.group#
ORDER BY
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)
/
prompt
[Top]

-- +============================================================================+
-- | |
-- | <<<<< 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'
, '
' || failures || '
'
, '
' || failures || '
') failures
, DECODE( broken
, 'Y'
, '
' || broken || '
'
, '
' || broken || '
') broken
FROM
dba_jobs
ORDER BY
job;
prompt
[Top]

-- +============================================================================+
-- | |
-- | <<<<< 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 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 >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - UNDO SEGMENTS - |
-- +----------------------------------------------------------------------------+
prompt
prompt UNDO Segments
CLEAR COLUMNS BREAKS COMPUTES
COLUMN roll_name HEADING 'UNDO Segment Name' ENTMAP off
COLUMN tablespace HEADING 'Tablspace' 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
COMPUTE sum LABEL 'Total:' OF bytes extents shrinks wraps ON report
SELECT
'' || a.owner || '.' || a.segment_name || '' roll_name
, a.tablespace_name tablespace
, '
' ||
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
WHERE
a.segment_name = b.segment_name
AND a.segment_name = c.name (+)
AND c.usn = d.usn (+)
ORDER BY
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]

-- +============================================================================+
-- | |
-- | <<<<< ARCHIVING >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - 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( a.value
, 'TRUE', 'Enabled'
, 'FALSE', 'Disabled') log_archive_start
from
v$parameter a
where
a.name = 'log_archive_start'
) p
, (select a.sequence# current_log_seq
from v$log a
where a.status = 'CURRENT'
) c
, (select min(a.sequence#) oldest_online_log_sequence
from v$log a
) o
/
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - ARCHIVING PARAMETERS - |
-- +----------------------------------------------------------------------------+
prompt
prompt Archiving 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 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
SELECT
'
' || 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
sequence#;
prompt
[Top]

-- +============================================================================+
-- | |
-- | <<<<< RMAN >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - 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]

-- +----------------------------------------------------------------------------+
-- | - BACKUP SETS - |
-- +----------------------------------------------------------------------------+
prompt
prompt 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]

-- +----------------------------------------------------------------------------+
-- | - BACKUP PIECES - |
-- +----------------------------------------------------------------------------+
prompt
prompt 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]

-- +----------------------------------------------------------------------------+
-- | - BACKUP CONTROL FILES - |
-- +----------------------------------------------------------------------------+
prompt
prompt 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]

-- +----------------------------------------------------------------------------+
-- | - BACKUP SPFILE - |
-- +----------------------------------------------------------------------------+
prompt
prompt 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]

-- +============================================================================+
-- | |
-- | <<<<< PERFORMANCE >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - SGA INFORMATION - |
-- +----------------------------------------------------------------------------+
prompt
prompt SGA Information
CLEAR COLUMNS BREAKS COMPUTES
COLUMN name FORMAT a79 HEADING 'Pool Name' ENTMAP off
COLUMN value FORMAT 999,999,999,999 HEADING 'Bytes' ENTMAP off
BREAK ON report
COMPUTE sum LABEL 'Total:' OF value ON report
SELECT
'
' || name || '
' name
, value
FROM
v$sga;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - DB BUFFER CACHE HIT RATIO - |
-- +----------------------------------------------------------------------------+
prompt
prompt DB Buffer Cache Hit Ratio
CLEAR COLUMNS BREAKS COMPUTES
COLUMN consistent_gets FORMAT 999,999,999,999,999,999 HEADING 'Consistent Gets' ENTMAP off
COLUMN db_block_gets FORMAT 999,999,999,999,999,999 HEADING 'DB Block Gets' ENTMAP off
COLUMN phys_reads FORMAT 999,999,999,999,999,999 HEADING 'Physical Reads' ENTMAP off
COLUMN db_hit_ratio HEADING 'Hit Ratio' ENTMAP off
SELECT
SUM(DECODE(name, 'consistent gets', value, 0)) consistent_gets
, SUM(DECODE(name, 'db block gets', value, 0)) db_block_gets
, SUM(DECODE(name, 'physical reads', value, 0)) phys_reads
, '
' ||
TO_CHAR(ROUND((SUM(DECODE(name, 'consistent gets', value, 0)) +
SUM(DECODE(name, 'db block gets', value, 0)) -
SUM(DECODE(name, 'physical reads', value, 0))) /
(SUM(DECODE(name, 'consistent gets', value, 0)) +
SUM(DECODE(name, 'db block gets', value, 0)))*100, 2)) ||
'%
' db_hit_ratio
FROM v$sysstat;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - DICTIONARY CACHE HIT RATIO - |
-- +----------------------------------------------------------------------------+
prompt
prompt Dictionary Cache Hit Ratio
CLEAR COLUMNS BREAKS COMPUTES
COLUMN gets FORMAT 999,999,999,999,999,999 HEADING 'Misses' ENTMAP off
COLUMN misses FORMAT 999,999,999,999,999,999 HEADING 'Gets' ENTMAP off
COLUMN dc_hit_ratio HEADING 'Hit Ratio' ENTMAP off
SELECT
SUM(gets) gets
, SUM(getmisses) misses
, '
' ||
TO_CHAR(ROUND((((SUM(gets)-SUM(getmisses))/SUM(gets))*100), 2)) ||
'%
' dc_hit_ratio
FROM
v$rowcache;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - LIBRARY CACHE HIT RATIO - |
-- +----------------------------------------------------------------------------+
prompt
prompt Library Cache Hit Ratio
CLEAR COLUMNS BREAKS COMPUTES
COLUMN pins FORMAT 999,999,999,999,999,999 HEADING 'Pins - (Executions)' ENTMAP off
COLUMN reloads FORMAT 999,999,999,999,999,999 HEADING 'Reloads - (Cache Miss)' ENTMAP off
COLUMN lc_hit_ratio HEADING 'Hit Ratio' ENTMAP off
SELECT
SUM(pins) pins
, SUM(reloads) reloads
, '
' ||
TO_CHAR(ROUND((((SUM(pins)-SUM(reloads))/SUM(pins))*100),2)) ||
'%
' lc_hit_ratio
FROM
v$librarycache;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - LATCH CONTENTION - |
-- +----------------------------------------------------------------------------+
prompt
prompt Latch Contention
prompt Latches with "gets", "misses", "sleeps", "immediate gets", or "immediate misses" greater than 0 - (ordered by misses)
CLEAR COLUMNS BREAKS COMPUTES
COLUMN latch_name FORMAT a110 HEADING 'Latch Name' ENTMAP off
COLUMN gets FORMAT 999,999,999,999,999,999 HEADING 'Gets' ENTMAP off
COLUMN misses FORMAT 999,999,999,999,999,999 HEADING 'Misses' ENTMAP off
COLUMN sleeps FORMAT 999,999,999,999,999,999 HEADING 'Sleeps' ENTMAP off
COLUMN miss_ratio HEADING 'Willing to Wait Ratio' ENTMAP off
COLUMN imm_gets FORMAT 999,999,999,999,999,999 HEADING 'Immediate Gets' ENTMAP off
COLUMN imm_misses FORMAT 999,999,999,999,999,999 HEADING 'Immediate Misses' ENTMAP off
COLUMN imm_miss_ratio HEADING 'Immediate Ratio' ENTMAP off
SELECT
'' || SUBSTR(a.name,1,40) || '' latch_name
, gets gets
, misses misses
, sleeps sleeps
, '
' || ROUND((misses/(gets+.001))*100, 4) || '%
' miss_ratio
, immediate_gets imm_gets
, immediate_misses imm_misses
, '
' || ROUND((immediate_misses/(immediate_gets+.001))*100, 4) || '%
' imm_miss_ratio
FROM
v$latch a
, v$latchname b
WHERE
a.latch# = b.latch#
AND ( gets > 0
OR misses > 0
OR sleeps > 0
OR immediate_gets > 0
OR immediate_misses > 0
)
ORDER BY
misses DESC;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - SYSTEM WAIT STATISTICS - |
-- +----------------------------------------------------------------------------+
prompt
prompt System Wait Statistics
prompt Classes with counts greater than 0
CLEAR COLUMNS BREAKS COMPUTES
COLUMN class FORMAT A95 HEADING 'Class' ENTMAP off
COLUMN count FORMAT 99999999999990 HEADING 'Count' ENTMAP off
SELECT
'' || class || '' class
, count
FROM
v$waitstat
WHERE
count > 0
ORDER BY
2 DESC
, 1;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - SYSTEM STATISTICS - |
-- +----------------------------------------------------------------------------+
prompt
prompt System Statistics
prompt Statistics with values greater than 0
CLEAR COLUMNS BREAKS COMPUTES
COLUMN name FORMAT A95 HEADING 'Name' ENTMAP off
COLUMN value FORMAT 999,999,999,999,999,999,999,990 HEADING 'Value' ENTMAP off
SELECT
'' || name || '' name
, value
FROM
v$sysstat
WHERE
value > 0
ORDER BY
2 DESC
, 1;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - SYSTEM EVENT STATISTICS - |
-- +----------------------------------------------------------------------------+
prompt
prompt System Event Statistics
prompt Non-idle events with total waits greater than 0 - (ordered by "Time Waited")
prompt
prompt -
EVENT: The name of the wait event.
-
TOTAL_WAITS: The total number of waits for this event.
-
TOTAL_TIMEOUTS: The total number of timeouts for this event.
-
TIME_WAITED: The total amount of time waited for this event, in hundredths of a second.
-
AVERAGE_WAIT: The average amount of time waited for this event, in hundredths of a second.
CLEAR COLUMNS BREAKS COMPUTES
COLUMN event FORMAT a95 HEADING 'Event' ENTMAP off
COLUMN total_waits FORMAT 999,999,999,999,999,999 HEADING 'Total Waits' ENTMAP off
COLUMN total_timeouts FORMAT 999,999,999,999,999,999 HEADING 'Total Timeouts' ENTMAP off
COLUMN time_waited FORMAT 999,999,999,999,999,999 HEADING 'Time Waited' ENTMAP off
COLUMN average_wait FORMAT 999,999,999,999,999,999 HEADING 'Average Wait' ENTMAP off
SELECT
'' || event || '' event
, total_waits
, total_timeouts
, time_waited
, average_wait
FROM
v$system_event
WHERE
total_waits > 0
AND event NOT IN ( 'PX Idle Wait'
, 'pmon timer'
, 'smon timer'
, 'rdbms ipc message'
, 'parallel dequeue wait'
, 'parallel query dequeue'
, 'virtual circuit'
, 'SQL*Net message from client'
, 'SQL*Net message to client'
, 'SQL*Net more data to client'
, 'client message','Null event'
, 'WMON goes to sleep'
, 'virtual circuit status'
, 'dispatcher timer'
, 'pipe get'
, 'slave wait'
, 'KXFX: execution message dequeue - Slaves'
, 'parallel query idle wait - Slaves'
, 'lock manager wait for remote message')
ORDER BY
time_waited DESC;
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' ENTMAP off
COLUMN gets FORMAT 999,999,999,999,999,999 HEADING 'Gets' ENTMAP off
COLUMN misses FORMAT 999,999,999,999 HEADING 'Misses' ENTMAP off
COLUMN sleeps FORMAT 999,999,999,999 HEADING 'Sleeps' ENTMAP off
COLUMN immediate_gets FORMAT 999,999,999,999,999,999 HEADING 'Immediate Gets' ENTMAP off
COLUMN immediate_misses FORMAT 999,999,999,999 HEADING 'Immediate Misses' ENTMAP off
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]

-- +----------------------------------------------------------------------------+
-- | - TOP 10 TABLES - |
-- +----------------------------------------------------------------------------+
prompt
prompt Top 10 Tables
CLEAR COLUMNS BREAKS COMPUTES
COLUMN ctyp FORMAT a79 HEADING 'Command Type' ENTMAP off
COLUMN obj FORMAT a30 HEADING 'Object Name' ENTMAP off
COLUMN noe FORMAT 999,999,999,999,999 HEADING 'Number of Executions' ENTMAP off
COLUMN gets FORMAT 999,999,999,999,999 HEADING 'Buffer Gets' ENTMAP off
COLUMN rowp FORMAT 999,999,999,999,999 HEADING 'Rows Processed' ENTMAP off
BREAK ON report
COMPUTE sum LABEL 'Total: ' OF noe gets rowp ON report
SELECT
'
' || ctyp || '
' ctyp
, obj
, 0 - exem noe
, gets
, rowp
FROM (
select distinct exem, ctyp, obj, gets, rowp
from (select
DECODE( s.command_type
, 2, 'INSERT INTO '
, 3, 'SELECT FROM '
, 6, 'UPDATE OF '
, 7, 'DELETE FROM '
, 26, 'LOCK OF ') ctyp
, o.owner || '.' || o.name obj
, SUM(0 - s.executions) exem
, SUM(s.buffer_gets) gets
, SUM(s.rows_processed) rowp
from
v$sql s
, v$object_dependency d
, v$db_object_cache o
where
s.command_type IN (2,3,6,7,26)
and d.from_address = s.address
and d.to_owner = o.owner
and d.to_name = o.name
and o.type = 'TABLE'
group by
s.command_type
, o.owner
, o.name
)
)
WHERE rownum <= 10;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - TOP 10 PROCEDURES - |
-- +----------------------------------------------------------------------------+
prompt
prompt Top 10 Procedures
CLEAR COLUMNS BREAKS COMPUTES
COLUMN ptyp FORMAT a79 HEADING 'Object Type' ENTMAP off
COLUMN obj FORMAT a42 HEADING 'Object Name' ENTMAP off
COLUMN noe FORMAT 999,999,999,999,999 HEADING 'Number of Executions' ENTMAP off
BREAK ON report
COMPUTE sum LABEL 'Total: ' OF noe ON report
SELECT
'
' || ptyp || '
' ptyp
, obj
, 0 - exem noe
FROM ( select distinct exem, ptyp, obj
from ( select
o.type ptyp
, o.owner || '.' || o.name obj
, 0 - o.executions exem
from v$db_object_cache O
where o.type in ( 'FUNCTION'
, 'PACKAGE'
, 'PACKAGE BODY'
, 'PROCEDURE'
, 'TRIGGER')
)
)
WHERE rownum <= 10;
prompt
[Top]

-- +============================================================================+
-- | |
-- | <<<<< STATSPACK >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - STATSPACK LIST - |
-- +----------------------------------------------------------------------------+
prompt
prompt Statspack List
CLEAR COLUMNS BREAKS COMPUTES
COLUMN snap_id FORMAT a75 HEAD 'Snap ID' ENTMAP off
COLUMN snap_time FORMAT a75 HEAD 'Statspack Snap Time' ENTMAP off
COLUMN startup_time FORMAT a75 HEAD 'Database Startup Time' ENTMAP off
BREAK ON startup_time SKIP 1
SELECT
'
' || a.snap_id || '
' snap_id
, '
' || TO_CHAR(a.startup_time, 'mm/dd/yyyy HH24:MI:SS') || '
' startup_time
, '
' || TO_CHAR(a.snap_time, 'mm/dd/yyyy HH24:MI:SS') || '
' snap_time
FROM
stats$snapshot a
, v$database b
ORDER BY
a.snap_id
/
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - STATSPACK PARAMETERS - |
-- +----------------------------------------------------------------------------+
prompt
prompt Statspack Parameters
CLEAR COLUMNS BREAKS COMPUTES
COLUMN name FORMAT a75 HEAD 'Database|Name' ENTMAP off
COLUMN snap_level FORMAT 999999 HEAD 'Snap|Level' ENTMAP off
COLUMN num_sql FORMAT 999,999 HEAD 'Number|SQL' ENTMAP off
COLUMN executions_th FORMAT 999,999 HEAD 'Executions|(TH)' ENTMAP off
COLUMN parse_calls_th FORMAT 999,999 HEAD 'Parse|Calls|(TH)' ENTMAP off
COLUMN disk_reads_th FORMAT 999,999 HEAD 'Disk|Reads|(TH)' ENTMAP off
COLUMN buffer_gets_th FORMAT 999,999 HEAD 'Buffer|Gets|(TH)' ENTMAP off
COLUMN sharable_mem_th FORMAT 999,999,999 HEAD 'Sharable|Mem.|(TH)' ENTMAP off
COLUMN version_count_th HEAD 'Version|Count|(TH)' ENTMAP off
COLUMN pin_statspack HEAD 'Pin|Statspack' ENTMAP off
COLUMN all_init HEAD 'All|Init' ENTMAP off
COLUMN last_modified FORMAT a75 HEAD 'Last|Modified' ENTMAP off
SELECT
'' || b.name || '' name
, a.snap_level
, a.num_sql
, a.executions_th
, a.parse_calls_th
, a.disk_reads_th
, a.buffer_gets_th
, a.sharable_mem_th
, a.version_count_th
, '
' || a.pin_statspack || '
' pin_statspack
, '
' || a.all_init || '
' all_init
, '
' || TO_CHAR(a.last_modified, 'mm/dd/yyyy HH24:MI:SS') || '
' last_modified
FROM
stats$statspack_parameter a
, v$database b
/
prompt
[Top]

-- +============================================================================+
-- | |
-- | <<<<< SESSIONS >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - CURRENT SESSIONS - |
-- +----------------------------------------------------------------------------+
prompt
prompt Current Sessions
CLEAR COLUMNS BREAKS COMPUTES
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
'
' || TO_char(a.count) || '
' count
, '
' || b.value || '
' value
, '
' || TO_CHAR(ROUND(100*(a.count / b.value), 2)) || '%
' pct_usage
FROM
(select count(*) count from v$session) a
, (select value from v$parameter where name='processes') b;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - USER SESSION MATRIX - |
-- +----------------------------------------------------------------------------+
prompt
prompt User Session Matrix
prompt User sessions (including SYS and background processes)
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
COMPUTE sum LABEL 'Total: ' OF num_user_sess count_a count_i count_k ON report
SELECT
'
' || 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
v$session sess
, (SELECT count(*) count, nvl(username, '[B.G. Process]') username
FROM v$session
WHERE status = 'ACTIVE'
GROUP BY username) act
, (SELECT count(*) count, nvl(username, '[B.G. Process]') username
FROM v$session
WHERE status = 'INACTIVE'
GROUP BY username) inact
, (SELECT count(*) count, nvl(username, '[B.G. Process]') username
FROM v$session
WHERE status = 'KILLED'
GROUP BY username) killed
WHERE
nvl(sess.username, '[B.G. Process]') = act.username (+)
and nvl(sess.username, '[B.G. Process]') = inact.username (+)
and nvl(sess.username, '[B.G. Process]') = killed.username (+)
GROUP BY
sess.username
, act.count
, inact.count
, killed.count
ORDER BY
username;
prompt
[Top]

-- +============================================================================+
-- | |
-- | <<<<< 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 a30 HEADING 'Granted Role' ENTMAP off
COLUMN admin_option FORMAT a40 HEADING 'Admin. Option?' ENTMAP off
COLUMN default_role FORMAT a40 HEADING 'Default Role?' ENTMAP off
SELECT
'' || grantee || '' grantee
, '
' || granted_role || '
' granted_role
, '
' || admin_option || '
' admin_option
, '
' || 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 a30 HEAD 'Grantee' ENTMAP off
COLUMN admin_option FORMAT a40 HEAD 'Admin Option?' ENTMAP off
COLUMN default_role FORMAT a40 HEAD 'Default Role?' ENTMAP off
BREAK ON role
SELECT
'' || b.role || '' role
, a.grantee grantee
, '
' || NVL(a.admin_option, '
') || '
' admin_option
, '
' || NVL(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 >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - 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;
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;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - TOP 200 SEGMENTS (BY SIZE) - |
-- +----------------------------------------------------------------------------+
prompt
prompt Top 200 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 < 200;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - TOP 200 SEGMENTS (BY EXTENTS) - |
-- +----------------------------------------------------------------------------+
prompt
prompt Top 200 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 < 200;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - DIRECTORIES - |
-- +----------------------------------------------------------------------------+
prompt
prompt Directories
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner HEADING 'Owner' ENTMAP off
COLUMN directory_name HEADING 'Directory Name' ENTMAP off
COLUMN directory_path HEADING 'Directory Path' ENTMAP off
SELECT
owner
, directory_name
, '' || directory_path || '' directory_path
FROM
dba_directories
ORDER BY
owner
, directory_name;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - LIBRARIES - |
-- +----------------------------------------------------------------------------+
prompt
prompt Libraries
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner HEADING 'Owner' ENTMAP off
COLUMN library_name HEADING 'Library Name' ENTMAP off
COLUMN file_spec HEADING 'File Spec' ENTMAP off
COLUMN dynamic FORMAT a40 HEADING 'Dynamic?' ENTMAP off
COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off
SELECT
owner
, library_name
, 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 the SYS, SYSTEM, and XDB schemas
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 ('SYS', 'SYSTEM', 'XDB')
ORDER BY
t.owner
, t.type_name;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - TYPE ATTRIBUTES - |
-- +----------------------------------------------------------------------------+
prompt
prompt Type Attributes
prompt Excluding the SYS, SYSTEM, and XDB schemas
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
, '
' || 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 ('SYS', 'SYSTEM', 'XDB')
ORDER BY
t.owner
, t.type_name
, t.typecode
, a.attr_no;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - TYPE METHODS - |
-- +----------------------------------------------------------------------------+
prompt
prompt Type Methods
prompt Excluding the SYS, SYSTEM, and XDB schemas
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
, '
' || 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 ('SYS', 'SYSTEM', 'XDB')
ORDER BY
t.owner
, t.type_name
, t.typecode
, m.method_no;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - COLLECTIONS - |
-- +----------------------------------------------------------------------------+
prompt
prompt Collections
prompt Excluding the SYS, SYSTEM, and XDB schemas
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
, '
' || c.nulls_stored || '
' nulls_stored
FROM
dba_coll_types c
WHERE
c.owner NOT IN ('SYS', 'SYSTEM', 'XDB')
ORDER BY
c.owner
, c.type_name;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - LOB SEGMENTS - |
-- +----------------------------------------------------------------------------+
prompt
prompt LOB Segments
prompt Excluding the SYS, SYSTEM, and XDB schemas
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner FORMAT a75 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
, '
' || 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 ('SYS', 'SYSTEM', '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 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
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 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 a65 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
COMPUTE count LABEL 'Grand Total: ' OF object_name ON report
SELECT
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 type FORMAT a15 HEAD 'Object Type' ENTMAP off
COLUMN owner FORMAT a17 HEAD 'Schema' ENTMAP off
COLUMN name FORMAT a30 HEAD 'Object Name' 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
SELECT
type
, owner
, name
, 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 a50 HEAD 'Owner' ENTMAP off
COLUMN object_type FORMAT a20 HEAD 'Object Type' ENTMAP off
COLUMN object_name HEAD 'Object Name' ENTMAP off
COLUMN partition_name FORMAT a35 HEAD 'Partition Name' ENTMAP off
BREAK ON report
COMPUTE count LABEL 'Total: ' OF object_name ON report
SELECT
owner owner
, 'Table' object_type
, table_name object_name
, NULL partition_name
FROM
sys.dba_tables
WHERE
last_analyzed IS NULL
AND owner NOT IN ('SYS','SYSTEM')
AND partitioned = 'NO'
UNION
SELECT
owner owner
, 'Index' object_type
, index_name object_name
, NULL partition_name
FROM
sys.dba_indexes
WHERE
last_analyzed IS NULL
AND owner NOT IN ('SYS','SYSTEM')
AND partitioned = 'NO'
UNION
SELECT
table_owner owner
, 'Table Partition' object_type
, table_name object_name
, partition_name partition_name
FROM
sys.dba_tab_partitions
WHERE
last_analyzed IS NULL
AND table_owner NOT IN ('SYS','SYSTEM')
UNION
SELECT
index_owner owner
, 'Index Partition' object_type
, index_name object_name
, partition_name partition_name
FROM
sys.dba_ind_partitions
WHERE
last_analyzed IS NULL
AND index_owner NOT IN ('SYS','SYSTEM')
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
COMPUTE count LABEL 'Total Count: ' OF segment_name ON report
COMPUTE sum LABEL 'Total Bytes: ' OF bytes ON report
SELECT
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]

-- +============================================================================+
-- | |
-- | <<<<< 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
, 'N'
, '
Yes
'
, '
No
' ) 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]

-- +============================================================================+
-- | |
-- | <<<<< 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 >>>>> |
-- | |
-- +============================================================================+
-- +----------------------------------------------------------------------------+
-- | - 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 a75 HEADING 'Parameter 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
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 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
/
COLUMN log_table HEADING 'Log Table' ENTMAP off
COLUMN log_owner HEADING 'Log Owner' ENTMAP off
COLUMN master HEADING 'Master' ENTMAP off
COLUMN rowids FORMAT a10 HEADING 'Row ID' ENTMAP off
COLUMN primary_key FORMAT a13 HEADING 'Primary Key' ENTMAP off
COLUMN filter_columns FORMAT a15 HEADING 'Filter Columns' ENTMAP off
SELECT distinct
log_table
, log_owner
, master
, rowids
, primary_key
, filter_columns
FROM
sys.dba_snapshot_logs
ORDER BY
1
/
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 distinct
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
1
/
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - (MATERIALIZED VIEW) - MASTER SITE LOGS - |
-- +----------------------------------------------------------------------------+
prompt
prompt (Materialized View) - Master Site Logs
CLEAR COLUMNS BREAKS COMPUTES
COLUMN log_table HEADING 'Log Table' ENTMAP off
COLUMN log_owner HEADING 'Log Owner' ENTMAP off
COLUMN master HEADING 'Master' ENTMAP off
COLUMN rowids FORMAT a10 HEADING 'Row ID' ENTMAP off
COLUMN primary_key FORMAT a13 HEADING 'Primary Key' ENTMAP off
COLUMN filter_columns FORMAT a15 HEADING 'Filter Columns' ENTMAP off
SELECT distinct
log_table
, log_owner
, master
, rowids
, primary_key
, filter_columns
FROM
sys.dba_snapshot_logs
ORDER BY
1;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - (MATERIALIZED VIEW) - MASTER SITE TEMPLATES - |
-- +----------------------------------------------------------------------------+
prompt
prompt (Materialized View) - Master Site Templates
CLEAR COLUMNS BREAKS COMPUTES
COLUMN refresh_template_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 distinct
rt.refresh_template_name refresh_template_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
1;
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - (MATERIALIZED VIEW) - 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) - 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'
/
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - (MATERIALIZED VIEW) - MATERIALIZED VIEWS - |
-- +----------------------------------------------------------------------------+
prompt
prompt (Materialized View) - Site Materialized Views
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner 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 a11 HEADING 'Updatable?' ENTMAP off
COLUMN can_use_log FORMAT a13 HEADING 'Can Use Log?' ENTMAP off
COLUMN last_refresh FORMAT a75 HEADING 'Last Refresh' ENTMAP off
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
1
, 2
/
prompt
[Top]

-- +----------------------------------------------------------------------------+
-- | - (MATERIALIZED VIEW) - 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
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
1
, 2
/
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;

End Script

Oracle Database Error Messages



Oracle Database High Availability Any organization evaluating a database solution for enterprise data must also evaluate the High Availability (HA) capabilities of the database. Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Building a highly available data infrastructure is critical to the success of all organizations in today's fast moving economy.

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.