Database Report

-- +----------------------------------------------------------------------------+ -- | Jeffrey M. Hunter | -- | jhunter@idevelopment.info | -- | www.idevelopment.info | -- |----------------------------------------------------------------------------| -- | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | -- |----------------------------------------------------------------------------| -- | DATABASE : Oracle | -- | FILE : dba_snapshot_database_10g.sql | -- | CLASS : Database Administration | -- | PURPOSE : This SQL script provides a detailed report (in HTML format) on | -- | all database metrics including installed options, storage, | -- | performance data, and security. | -- | VERSION : This script was designed for Oracle Database 10g Release 2. | -- | Although this script will also work with Oracle Database 10g | -- | Release 1, several sections will error out from missing tables | -- | or columns. | -- | USAGE : | -- | | -- | sqlplus -s /@ @dba_snapshot_database_10g.sql | -- | | -- | TESTING : This script has been successfully tested on the following | -- | platforms: | -- | | -- | Linux : Oracle Database 10.2.0.3.0 | -- | Linux : Oracle RAC 10.2.0.3.0 | -- | Solaris : Oracle Database 10.2.0.2.0 | -- | Solaris : Oracle Database 10.2.0.3.0 | -- | Windows XP : Oracle Database 10.2.0.3.0 | -- | | -- | NOTE : As with any code, ensure to test this script in a development | -- | environment before attempting to run it in production. | -- +----------------------------------------------------------------------------+ prompt prompt +-----------------------------------------------------------------------------------------+ prompt | Snapshot Database 10g Release 2 | prompt |-----------------------------------------------------------------------------------------+ prompt | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. (www.idevelopment.info) | prompt +-----------------------------------------------------------------------------------------+ prompt prompt Creating database report. prompt This script must be run as a user with SYSDBA privileges. prompt This process can take several minutes to complete. prompt define reportHeader="Snapshot Database 10g Release 2

" -- +----------------------------------------------------------------------------+ -- | SCRIPT SETTINGS | -- +----------------------------------------------------------------------------+ set termout off set echo off set feedback off set heading off set verify off set wrap on set trimspool on set serveroutput on set escape on set pagesize 50000 set linesize 175 set long 2000000000 clear buffer computes columns breaks define fileName=dba_snapshot_database_10g define versionNumber=5.3 -- +----------------------------------------------------------------------------+ -- | GATHER DATABASE REPORT INFORMATION | -- +----------------------------------------------------------------------------+ COLUMN tdate NEW_VALUE _date NOPRINT SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') tdate FROM dual; COLUMN time NEW_VALUE _time NOPRINT SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual; COLUMN date_time NEW_VALUE _date_time NOPRINT SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual; COLUMN date_time_timezone NEW_VALUE _date_time_timezone NOPRINT SELECT TO_CHAR(systimestamp, 'Mon DD, YYYY (') || TRIM(TO_CHAR(systimestamp, 'Day')) || TO_CHAR(systimestamp, ') "at" HH:MI:SS AM') || TO_CHAR(systimestamp, ' "in Timezone" TZR') date_time_timezone FROM dual; COLUMN spool_time NEW_VALUE _spool_time NOPRINT SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual; COLUMN dbname NEW_VALUE _dbname NOPRINT SELECT name dbname FROM v$database; COLUMN dbid NEW_VALUE _dbid NOPRINT SELECT dbid dbid FROM v$database; COLUMN platform_id NEW_VALUE _platform_id NOPRINT SELECT platform_id platform_id FROM v$database; COLUMN platform_name NEW_VALUE _platform_name NOPRINT SELECT platform_name platform_name FROM v$database; COLUMN global_name NEW_VALUE _global_name NOPRINT SELECT global_name global_name FROM global_name; COLUMN blocksize NEW_VALUE _blocksize NOPRINT SELECT value blocksize FROM v$parameter WHERE name='db_block_size'; COLUMN startup_time NEW_VALUE _startup_time NOPRINT SELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance; COLUMN host_name NEW_VALUE _host_name NOPRINT SELECT host_name host_name FROM v$instance; COLUMN instance_name NEW_VALUE _instance_name NOPRINT SELECT instance_name instance_name FROM v$instance; COLUMN instance_number NEW_VALUE _instance_number NOPRINT SELECT instance_number instance_number FROM v$instance; COLUMN thread_number NEW_VALUE _thread_number NOPRINT SELECT thread# thread_number FROM v$instance; COLUMN cluster_database NEW_VALUE _cluster_database NOPRINT SELECT value cluster_database FROM v$parameter WHERE name='cluster_database'; COLUMN cluster_database_instances NEW_VALUE _cluster_database_instances NOPRINT SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances'; COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINT SELECT user reportRunUser FROM dual; -- +----------------------------------------------------------------------------+ -- | GATHER DATABASE REPORT INFORMATION | -- +----------------------------------------------------------------------------+ set heading on set markup html on spool on preformat off entmap on - head ' - Database Report - ' - body 'BGCOLOR="#C0C0C0"' - table 'WIDTH="90%" BORDER="1"' spool &FileName._&_dbname._&_spool_time..html set markup html on entmap off -- +----------------------------------------------------------------------------+ -- | - REPORT HEADER - | -- +----------------------------------------------------------------------------+ prompt prompt &reportHeader -- +----------------------------------------------------------------------------+ -- | - REPORT INDEX - | -- +----------------------------------------------------------------------------+ prompt prompt

Report Index

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

Database and Instance Information
Report Header	Version	Options	Database Registry
Feature Usage Statistics	High Water Mark Statistics	Instance Overview	Database Overview
Initialization Parameters	Control Files	Control File Records	Online Redo Logs
Redo Log Switches	Outstanding Alerts	Statistics Level
Scheduler / Jobs
Jobs
Storage
Tablespaces	Data Files	Database Growth	Tablespace Extents
Tablespace to Owner	Owner to Tablespace
UNDO Segments
UNDO Segments	UNDO Segment Contention	UNDO Retention Parameters
Backups
RMAN Backup Jobs	RMAN Configuration	RMAN Backup Sets	RMAN Backup Pieces
RMAN Backup Control Files	RMAN Backup SPFILE	Archiving Mode	Archive Destinations
Archiving Instance Parameters	Archiving History	Flash Recovery Area Parameters	Flash Recovery Area Status
Flashback Technologies
UNDO Retention Parameters	Flashback Database Parameters	Flashback Database Status	Flashback Database Redo Time Matrix
Recycle Bin
Performance
SGA Information	SGA Target Advice	SGA (ASMM) Dynamic Components	PGA Target Advice
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	Enabled Traces	Enabled Aggregations
Automatic Workload Repository - (AWR)
Workload Repository Information	AWR Snapshot Settings	AWR Snapshot List	AWR Snapshot Size Estimates
AWR Baselines
Sessions
Current Sessions	User Session Matrix	Enabled Traces	Enabled Aggregations
Security
User Accounts	Users With DBA Privileges	Roles	Default Passwords
DB Links
Objects
Object Summary	Segment Summary	Top 100 Segments (by size)	Top 100 Segments (by number of extents)
Directories	Directory Privileges	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	Recycle Bin
Online Analytical Processing - (OLAP)
Dimensions	Dimension Levels	Dimension Attributes	Dimension Hierarchies
Cubes	Materialized Views	Materialized View Logs	Materialized View Refresh Groups
Data Pump
Data Pump Jobs	Data Pump Sessions	Data Pump Job Progress
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 >>>>> | -- | | -- +============================================================================+ prompt prompt

Database and Instance Information -- +----------------------------------------------------------------------------+ -- | - REPORT HEADER - | -- +----------------------------------------------------------------------------+ prompt prompt prompt Report Header

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

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

prompt [Top]

-- SET TIMING ON -- +----------------------------------------------------------------------------+ -- | - VERSION - | -- +----------------------------------------------------------------------------+ prompt prompt Version

CLEAR COLUMNS BREAKS COMPUTES COLUMN banner FORMAT a120 HEADING 'Banner' SELECT * FROM v$version; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - OPTIONS - | -- +----------------------------------------------------------------------------+ prompt prompt Options

CLEAR COLUMNS BREAKS COMPUTES COLUMN parameter HEADING 'Option Name' ENTMAP off COLUMN value HEADING 'Installed?' ENTMAP off SELECT DECODE( value , 'FALSE' , '' || parameter || '' , '' || parameter || '') parameter , DECODE( value , 'FALSE' , '

' || value || '

' , '

' || value || '

' ) value FROM v$option ORDER BY parameter; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DATABASE REGISTRY - | -- +----------------------------------------------------------------------------+ prompt prompt Database Registry

CLEAR COLUMNS BREAKS COMPUTES COLUMN comp_id FORMAT a75 HEADING 'Component ID' ENTMAP off COLUMN comp_name FORMAT a75 HEADING 'Component Name' ENTMAP off COLUMN version HEADING 'Version' ENTMAP off COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off COLUMN modified FORMAT a75 HEADING 'Modified' ENTMAP off COLUMN control HEADING 'Control' ENTMAP off COLUMN schema HEADING 'Schema' ENTMAP off COLUMN procedure HEADING 'Procedure' ENTMAP off SELECT '' || comp_id || '' comp_id , '

' || comp_name || '

' comp_name , version , DECODE( status , 'VALID', '

' || status || '

' , 'INVALID', '

' || status || '

' , '

' || status || '

' ) status , '

' || modified || '

' modified , control , schema , procedure FROM dba_registry ORDER BY comp_name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - FEATURE USAGE STATISTICS - | -- +----------------------------------------------------------------------------+ prompt prompt Feature Usage Statistics

CLEAR COLUMNS BREAKS COMPUTES COLUMN feature_name FORMAT a115 HEADING 'Feature|Name' COLUMN version FORMAT a75 HEADING 'Version' COLUMN detected_usages FORMAT a75 HEADING 'Detected|Usages' COLUMN total_samples FORMAT a75 HEADING 'Total|Samples' COLUMN currently_used FORMAT a60 HEADING 'Currently|Used' COLUMN first_usage_date FORMAT a95 HEADING 'First Usage|Date' COLUMN last_usage_date FORMAT a95 HEADING 'Last Usage|Date' COLUMN last_sample_date FORMAT a95 HEADING 'Last Sample|Date' COLUMN next_sample_date FORMAT a95 HEADING 'Next Sample|Date' SELECT '

' || name || '

' feature_name , DECODE( detected_usages , 0 , version , '' || version || '') version , DECODE( detected_usages , 0 , '

' || NVL(TO_CHAR(detected_usages), '
') || '

' , '

' || NVL(TO_CHAR(detected_usages), '
') || '

') detected_usages , DECODE( detected_usages , 0 , '

' || NVL(TO_CHAR(total_samples), '
') || '

' , '

' || NVL(TO_CHAR(total_samples), '
') || '

') total_samples , DECODE( detected_usages , 0 , '

' || NVL(currently_used, '
') || '

' , '

' || NVL(currently_used, '
') || '

') currently_used , DECODE( detected_usages , 0 , '

' || NVL(TO_CHAR(first_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' , '

' || NVL(TO_CHAR(first_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

') first_usage_date , DECODE( detected_usages , 0 , '

' || NVL(TO_CHAR(last_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' , '

' || NVL(TO_CHAR(last_usage_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

') last_usage_date , DECODE( detected_usages , 0 , '

' || NVL(TO_CHAR(last_sample_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' , '

' || NVL(TO_CHAR(last_sample_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

') last_sample_date , DECODE( detected_usages , 0 , '

' || NVL(TO_CHAR((last_sample_date+SAMPLE_INTERVAL/60/60/24), 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' , '

' || NVL(TO_CHAR((last_sample_date+SAMPLE_INTERVAL/60/60/24), 'mm/dd/yyyy HH24:MI:SS'), '
') || '

') next_sample_date FROM dba_feature_usage_statistics ORDER BY name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - HIGH WATER MARK STATISTICS - | -- +----------------------------------------------------------------------------+ prompt prompt High Water Mark Statistics

CLEAR COLUMNS BREAKS COMPUTES COLUMN statistic_name FORMAT a115 HEADING 'Statistic Name' COLUMN version FORMAT a62 HEADING 'Version' COLUMN highwater FORMAT 9,999,999,999,999,999 HEADING 'Highwater' COLUMN last_value FORMAT 9,999,999,999,999,999 HEADING 'Last Value' COLUMN description FORMAT a120 HEADING 'Description' SELECT '

' || name || '

' statistic_name , '

' || version || '

' version , highwater highwater , last_value last_value , description description FROM dba_high_water_mark_statistics ORDER BY name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - INSTANCE OVERVIEW - | -- +----------------------------------------------------------------------------+ prompt prompt Instance Overview

CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name_print FORMAT a75 HEADING 'Instance|Name' ENTMAP off COLUMN instance_number_print FORMAT a75 HEADING 'Instance|Num' ENTMAP off COLUMN thread_number_print HEADING 'Thread|Num' ENTMAP off COLUMN host_name_print FORMAT a75 HEADING 'Host|Name' ENTMAP off COLUMN version HEADING 'Oracle|Version' ENTMAP off COLUMN start_time FORMAT a75 HEADING 'Start|Time' ENTMAP off COLUMN uptime HEADING 'Uptime|(in days)' ENTMAP off COLUMN parallel FORMAT a75 HEADING 'Parallel - (RAC)' ENTMAP off COLUMN instance_status FORMAT a75 HEADING 'Instance|Status' ENTMAP off COLUMN database_status FORMAT a75 HEADING 'Database|Status' ENTMAP off COLUMN logins FORMAT a75 HEADING 'Logins' ENTMAP off COLUMN archiver FORMAT a75 HEADING 'Archiver' ENTMAP off SELECT '

' || instance_name || '

' instance_name_print , '

' || instance_number || '

' instance_number_print , '

' || thread# || '

' thread_number_print , '

' || host_name || '

' host_name_print , '

' || version || '

' version , '

' || TO_CHAR(startup_time,'mm/dd/yyyy HH24:MI:SS') || '

' start_time , ROUND(TO_CHAR(SYSDATE-startup_time), 2) uptime , '

' || parallel || '

' parallel , '

' || status || '

' instance_status , '

' || logins || '

' logins , DECODE( archiver , 'FAILED' , '

' || archiver || '

' , '

' || archiver || '

') archiver FROM gv$instance ORDER BY instance_number; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DATABASE OVERVIEW - | -- +----------------------------------------------------------------------------+ prompt prompt Database Overview

CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT a75 HEADING 'Database|Name' ENTMAP off COLUMN dbid HEADING 'Database|ID' ENTMAP off COLUMN db_unique_name HEADING 'Database|Unique Name' ENTMAP off COLUMN creation_date HEADING 'Creation|Date' ENTMAP off COLUMN platform_name_print HEADING 'Platform|Name' ENTMAP off COLUMN current_scn HEADING 'Current|SCN' ENTMAP off COLUMN log_mode HEADING 'Log|Mode' ENTMAP off COLUMN open_mode HEADING 'Open|Mode' ENTMAP off COLUMN force_logging HEADING 'Force|Logging' ENTMAP off COLUMN flashback_on HEADING 'Flashback|On?' ENTMAP off COLUMN controlfile_type HEADING 'Controlfile|Type' ENTMAP off COLUMN last_open_incarnation_number HEADING 'Last Open|Incarnation Num' ENTMAP off SELECT '

' || name || '

' name , '

' || dbid || '

' dbid , '

' || db_unique_name || '

' db_unique_name , '

' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '

' creation_date , '

' || platform_name || '

' platform_name_print , '

' || current_scn || '

' current_scn , '

' || log_mode || '

' log_mode , '

' || open_mode || '

' open_mode , '

' || force_logging || '

' force_logging , '

' || flashback_on || '

' flashback_on , '

' || controlfile_type || '

' controlfile_type , '

' || last_open_incarnation# || '

' last_open_incarnation_number FROM v$database; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - INITIALIZATION PARAMETERS - | -- +----------------------------------------------------------------------------+ prompt prompt Initialization Parameters

CLEAR COLUMNS BREAKS COMPUTES COLUMN spfile HEADING 'SPFILE Usage' SELECT 'This database '|| DECODE( (1-SIGN(1-SIGN(count(*) - 0))) , 1 , 'IS' , 'IS NOT') || ' using an SPFILE.'spfile FROM v$spparameter WHERE value IS NOT null; COLUMN pname FORMAT a75 HEADING 'Parameter Name' ENTMAP off COLUMN instance_name_print FORMAT a45 HEADING 'Instance Name' ENTMAP off COLUMN value FORMAT a75 HEADING 'Value' ENTMAP off COLUMN isdefault FORMAT a75 HEADING 'Is Default?' ENTMAP off COLUMN issys_modifiable FORMAT a75 HEADING 'Is Dynamic?' ENTMAP off BREAK ON report ON pname SELECT DECODE( p.isdefault , 'FALSE' , '' || SUBSTR(p.name,0,512) || '' , '' || SUBSTR(p.name,0,512) || '' ) pname , DECODE( p.isdefault , 'FALSE' , '' || i.instance_name || '' , i.instance_name ) instance_name_print , DECODE( p.isdefault , 'FALSE' , '' || SUBSTR(p.value,0,512) || '' , SUBSTR(p.value,0,512) ) value , DECODE( p.isdefault , 'FALSE' , '

' || p.isdefault || '

' , '

' || p.isdefault || '

') isdefault , DECODE( p.isdefault , 'FALSE' , '

' || p.issys_modifiable || '

' , '

' || p.issys_modifiable || '

') issys_modifiable FROM gv$parameter p , gv$instance i WHERE p.inst_id = i.inst_id ORDER BY p.name , i.instance_name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - CONTROL FILES - | -- +----------------------------------------------------------------------------+ prompt prompt Control Files

CLEAR COLUMNS BREAKS COMPUTES COLUMN name HEADING 'Controlfile Name' ENTMAP off COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off COLUMN file_size FORMAT a75 HEADING 'File Size' ENTMAP off SELECT '' || c.name || '' name , DECODE( c.status , NULL , '

VALID

' , '

' || c.status || '

') status , '

' || TO_CHAR(block_size * file_size_blks, '999,999,999,999') || '

' file_size FROM v$controlfile c ORDER BY c.name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - CONTROL FILE RECORDS - | -- +----------------------------------------------------------------------------+ prompt prompt Control File Records

CLEAR COLUMNS BREAKS COMPUTES COLUMN type FORMAT a95 HEADING 'Record Section Type' ENTMAP off COLUMN record_size FORMAT 999,999 HEADING 'Record Size|(in bytes)' ENTMAP off COLUMN records_total FORMAT 999,999 HEADING 'Records Allocated' ENTMAP off COLUMN bytes_alloc FORMAT 999,999,999 HEADING 'Bytes Allocated' ENTMAP off COLUMN records_used FORMAT 999,999 HEADING 'Records Used' ENTMAP off COLUMN bytes_used FORMAT 999,999,999 HEADING 'Bytes Used' ENTMAP off COLUMN pct_used FORMAT B999 HEADING '% Used' ENTMAP off COLUMN first_index HEADING 'First Index' ENTMAP off COLUMN last_index HEADING 'Last Index' ENTMAP off COLUMN last_recid HEADING 'Last RecID' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total: ' of record_size records_total bytes_alloc records_used bytes_used ON report COMPUTE avg LABEL 'Average: ' of pct_used ON report SELECT '

' || type || '

' type , record_size record_size , records_total records_total , (records_total * record_size) bytes_alloc , records_used records_used , (records_used * record_size) bytes_used , NVL(records_used/records_total * 100, 0) pct_used , first_index first_index , last_index last_index , last_recid last_recid FROM v$controlfile_record_section ORDER BY type; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - ONLINE REDO LOGS - | -- +----------------------------------------------------------------------------+ prompt prompt Online Redo Logs

CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name_print FORMAT a95 HEADING 'Instance Name' ENTMAP off COLUMN thread_number_print FORMAT a95 HEADING 'Thread Number' ENTMAP off COLUMN groupno HEADING 'Group Number' ENTMAP off COLUMN member HEADING 'Member' ENTMAP off COLUMN redo_file_type FORMAT a75 HEADING 'Redo Type' ENTMAP off COLUMN log_status FORMAT a75 HEADING 'Log Status' ENTMAP off COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes' ENTMAP off COLUMN archived FORMAT a75 HEADING 'Archived?' ENTMAP off BREAK ON report ON instance_name_print ON thread_number_print SELECT '

' || i.instance_name || '

' instance_name_print , '

' || i.thread# || '

' thread_number_print , f.group# groupno , '' || f.member || '' member , f.type redo_file_type , DECODE( l.status , 'CURRENT' , '

' || l.status || '

' , '

' || l.status || '

') log_status , l.bytes bytes , '

' || l.archived || '

' archived FROM gv$logfile f , gv$log l , gv$instance i WHERE f.group# = l.group# AND l.thread# = i.thread# AND i.inst_id = f.inst_id AND f.inst_id = l.inst_id ORDER BY i.instance_name , f.group# , f.member; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - REDO LOG SWITCHES - | -- +----------------------------------------------------------------------------+ prompt prompt Redo Log Switches

CLEAR COLUMNS BREAKS COMPUTES COLUMN DAY FORMAT a75 HEADING 'Day / Time' ENTMAP off COLUMN H00 FORMAT 999,999B HEADING '00' ENTMAP off COLUMN H01 FORMAT 999,999B HEADING '01' ENTMAP off COLUMN H02 FORMAT 999,999B HEADING '02' ENTMAP off COLUMN H03 FORMAT 999,999B HEADING '03' ENTMAP off COLUMN H04 FORMAT 999,999B HEADING '04' ENTMAP off COLUMN H05 FORMAT 999,999B HEADING '05' ENTMAP off COLUMN H06 FORMAT 999,999B HEADING '06' ENTMAP off COLUMN H07 FORMAT 999,999B HEADING '07' ENTMAP off COLUMN H08 FORMAT 999,999B HEADING '08' ENTMAP off COLUMN H09 FORMAT 999,999B HEADING '09' ENTMAP off COLUMN H10 FORMAT 999,999B HEADING '10' ENTMAP off COLUMN H11 FORMAT 999,999B HEADING '11' ENTMAP off COLUMN H12 FORMAT 999,999B HEADING '12' ENTMAP off COLUMN H13 FORMAT 999,999B HEADING '13' ENTMAP off COLUMN H14 FORMAT 999,999B HEADING '14' ENTMAP off COLUMN H15 FORMAT 999,999B HEADING '15' ENTMAP off COLUMN H16 FORMAT 999,999B HEADING '16' ENTMAP off COLUMN H17 FORMAT 999,999B HEADING '17' ENTMAP off COLUMN H18 FORMAT 999,999B HEADING '18' ENTMAP off COLUMN H19 FORMAT 999,999B HEADING '19' ENTMAP off COLUMN H20 FORMAT 999,999B HEADING '20' ENTMAP off COLUMN H21 FORMAT 999,999B HEADING '21' ENTMAP off COLUMN H22 FORMAT 999,999B HEADING '22' ENTMAP off COLUMN H23 FORMAT 999,999B HEADING '23' ENTMAP off COLUMN TOTAL FORMAT 999,999,999 HEADING 'Total' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total:' avg label 'Average:' OF total ON report SELECT '

' || SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || '

' DAY , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23 , COUNT(*) TOTAL FROM v$log_history a GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) / prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - OUTSTANDING ALERTS - | -- +----------------------------------------------------------------------------+ prompt prompt Outstanding Alerts

CLEAR COLUMNS BREAKS COMPUTES COLUMN severity FORMAT a75 HEADING 'Severity' ENTMAP off COLUMN target_name FORMAT a75 HEADING 'Target Name' ENTMAP off COLUMN target_type FORMAT a75 HEADING 'Target Type' ENTMAP off COLUMN category FORMAT a75 HEADING 'Category' ENTMAP off COLUMN name FORMAT a75 HEADING 'Name' ENTMAP off COLUMN message FORMAT a125 HEADING 'Message' ENTMAP off COLUMN alert_triggered FORMAT a75 HEADING 'Alert Triggered' ENTMAP off SELECT DECODE( alert_state , 'Critical' , '

' || alert_state || '

' , '

' || alert_state || '

') severity , target_name target_name , (CASE target_type WHEN 'oracle_listener' THEN 'Oracle Listener' WHEN 'rac_database' THEN 'Cluster Database' WHEN 'cluster' THEN 'Clusterware' WHEN 'host' THEN 'Host' WHEN 'osm_instance' THEN 'OSM Instance' WHEN 'oracle_database' THEN 'Database Instance' WHEN 'oracle_emd' THEN 'Oracle EMD' WHEN 'oracle_emrep' THEN 'Oracle EMREP' ELSE target_type END) target_type , metric_label category , column_label name , message message , '

' || TO_CHAR(collection_timestamp, 'mm/dd/yyyy HH24:MI:SS') || '

' alert_triggered FROM mgmt$alert_current ORDER BY alert_state , collection_timestamp; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - STATISTICS LEVEL - | -- +----------------------------------------------------------------------------+ prompt prompt Statistics Level

prompt "Automatic Database Management" was first introduced in Oracle10g where the Oracle database prompt can now automatically perform many of the routine monitoring and administrative activities that had prompt to be manually executed by the DBA in previous versions. Several of the new components that make prompt up this new feature include (1) Automatic Workload Repository (2) Automatic Database Diagnostic prompt Monitoring (3) Automatic Shared Memory Management and (4) Automatic UNDO Retention Tuning. All prompt of these new components can only be enabled when the STATISTICS_LEVEL initialization parameter prompt is set to TYPICAL (the default) or ALL. A value of BASIC turns off these components and disables prompt all self-tuning capabilities of the database. The view V$STATISTICS_LEVEL shows the statistic prompt component, description, and at what level of the STATISTICS_LEVEL parameter the prompt component is enabled. CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name_print FORMAT a95 HEADING 'Instance Name' ENTMAP off COLUMN statistics_name FORMAT a95 HEADING 'Statistics Name' ENTMAP off COLUMN session_status FORMAT a95 HEADING 'Session Status' ENTMAP off COLUMN system_status FORMAT a95 HEADING 'System Status' ENTMAP off COLUMN activation_level FORMAT a95 HEADING 'Activation Level' ENTMAP off COLUMN statistics_view_name FORMAT a95 HEADING 'Statistics View Name' ENTMAP off COLUMN session_settable FORMAT a95 HEADING 'Session Settable?' ENTMAP off BREAK ON report ON instance_name_print SELECT '

' || i.instance_name || '

' instance_name_print , '

' || s.statistics_name || '

' statistics_name , DECODE( s.session_status , 'ENABLED' , '

' || s.session_status || '

' , '

' || s.session_status || '

') session_status , DECODE( s.system_status , 'ENABLED' , '

' || s.system_status || '

' , '

' || s.system_status || '

') system_status , (CASE s.activation_level WHEN 'TYPICAL' THEN '

' || s.activation_level || '

' WHEN 'ALL' THEN '

' || s.activation_level || '

' WHEN 'BASIC' THEN '

' || s.activation_level || '

' ELSE '

' || s.activation_level || '

' END) activation_level , s.statistics_view_name statistics_view_name , '

' || s.session_settable || '

' session_settable FROM gv$statistics_level s , gv$instance i WHERE s.inst_id = i.inst_id ORDER BY i.instance_name , s.statistics_name; prompt [Top]

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

Scheduler / Jobs -- +----------------------------------------------------------------------------+ -- | - JOBS - | -- +----------------------------------------------------------------------------+ prompt prompt Jobs

CLEAR COLUMNS BREAKS COMPUTES COLUMN job_id FORMAT a75 HEADING 'Job ID' ENTMAP off COLUMN username FORMAT a75 HEADING 'User' ENTMAP off COLUMN what FORMAT a175 HEADING 'What' ENTMAP off COLUMN next_date FORMAT a110 HEADING 'Next Run Date' ENTMAP off COLUMN interval FORMAT a75 HEADING 'Interval' ENTMAP off COLUMN last_date FORMAT a110 HEADING 'Last Run Date' ENTMAP off COLUMN failures FORMAT a75 HEADING 'Failures' ENTMAP off COLUMN broken FORMAT a75 HEADING 'Broken?' ENTMAP off SELECT DECODE( broken , 'Y' , '' || job || '
' , '' || job || '
') job_id , DECODE( broken , 'Y' , '' || log_user || '' , log_user ) username , DECODE( broken , 'Y' , '' || what || '' , what ) what , DECODE( broken , 'Y' , '

' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' , '

' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

') next_date , DECODE( broken , 'Y' , '' || interval || '' , interval ) interval , DECODE( broken , 'Y' , '

' || NVL(TO_CHAR(last_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' , '

' || NVL(TO_CHAR(last_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

') last_date , DECODE( broken , 'Y' , '' || NVL(failures, 0) || '
' , '

' || NVL(failures, 0) || '

') failures , DECODE( broken , 'Y' , '' || broken || '
' , '

' || broken || '

') broken FROM dba_jobs ORDER BY job; prompt [Top]

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

Storage -- +----------------------------------------------------------------------------+ -- | - TABLESPACES - | -- +----------------------------------------------------------------------------+ prompt prompt Tablespaces

CLEAR COLUMNS BREAKS COMPUTES COLUMN status HEADING 'Status' ENTMAP off COLUMN name HEADING 'Tablespace Name' ENTMAP off COLUMN type FORMAT a12 HEADING 'TS Type' ENTMAP off COLUMN extent_mgt FORMAT a10 HEADING 'Ext. Mgt.' ENTMAP off COLUMN segment_mgt FORMAT a9 HEADING 'Seg. Mgt.' ENTMAP off COLUMN ts_size FORMAT 999,999,999,999,999 HEADING 'Tablespace Size' ENTMAP off COLUMN free FORMAT 999,999,999,999,999 HEADING 'Free (in bytes)' ENTMAP off COLUMN used FORMAT 999,999,999,999,999 HEADING 'Used (in bytes)' ENTMAP off COLUMN pct_used HEADING 'Pct. Used' ENTMAP off BREAK ON report COMPUTE SUM label 'Total:' OF ts_size used free ON report SELECT DECODE( d.status , 'OFFLINE' , '

' || d.status || '

' , '

' || d.status || '

') status , '' || d.tablespace_name || '' name , d.contents type , d.extent_management extent_mgt , d.segment_space_management segment_mgt , NVL(a.bytes, 0) ts_size , NVL(f.bytes, 0) free , NVL(a.bytes - NVL(f.bytes, 0), 0) used , '

' || DECODE ( (1-SIGN(1-SIGN(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) - 90))) , 1 , '' || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || '' , '' || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || '' ) || ' %

' pct_used FROM sys.dba_tablespaces d , ( select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) a , ( select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name ) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT ( d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY' ) UNION ALL SELECT DECODE( d.status , 'OFFLINE' , '

' || d.status || '

' , '

' || d.status || '

') status , '' || d.tablespace_name || '' name , d.contents type , d.extent_management extent_mgt , d.segment_space_management segment_mgt , NVL(a.bytes, 0) ts_size , NVL(a.bytes - NVL(t.bytes,0), 0) free , NVL(t.bytes, 0) used , '

' || DECODE ( (1-SIGN(1-SIGN(TRUNC(NVL(t.bytes / a.bytes * 100, 0)) - 90))) , 1 , '' || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || '' , '' || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || '' ) || ' %

' pct_used FROM sys.dba_tablespaces d , ( select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name ) a , ( select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name ) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY' ORDER BY 2; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DATA FILES - | -- +----------------------------------------------------------------------------+ prompt prompt Data Files

CLEAR COLUMNS BREAKS COMPUTES COLUMN tablespace HEADING 'Tablespace Name / File Class' ENTMAP off COLUMN filename HEADING 'Filename' ENTMAP off COLUMN filesize FORMAT 999,999,999,999,999 HEADING 'File Size' ENTMAP off COLUMN autoextensible HEADING 'Autoextensible' ENTMAP off COLUMN increment_by FORMAT 999,999,999,999,999 HEADING 'Next' ENTMAP off COLUMN maxbytes FORMAT 999,999,999,999,999 HEADING 'Max' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total: ' OF filesize ON report SELECT /*+ ordered */ '' || d.tablespace_name || '' tablespace , '' || d.file_name || '' filename , d.bytes filesize , '

' || NVL(d.autoextensible, '
') || '

' autoextensible , d.increment_by * e.value increment_by , d.maxbytes maxbytes FROM sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e WHERE (d.file_name = v.name) UNION SELECT '' || d.tablespace_name || '' tablespace , '' || d.file_name || '' filename , d.bytes filesize , '

' || NVL(d.autoextensible, '
') || '

' autoextensible , d.increment_by * e.value increment_by , d.maxbytes maxbytes FROM sys.dba_temp_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e UNION SELECT '[ ONLINE REDO LOG ]' , '' || a.member || '' , b.bytes , null , null , null FROM v$logfile a , v$log b WHERE a.group# = b.group# UNION SELECT '[ CONTROL FILE ]' , '' || a.name || '' , null , null , null , null FROM v$controlfile a ORDER BY 1 , 2; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DATABASE GROWTH - | -- +----------------------------------------------------------------------------+ prompt prompt Database Growth

CLEAR COLUMNS BREAKS COMPUTES COLUMN month FORMAT a75 HEADING 'Month' COLUMN growth FORMAT 999,999,999,999,999 HEADING 'Growth (bytes)' BREAK ON report COMPUTE SUM label 'Total:' OF growth ON report SELECT '

' || TO_CHAR(creation_time, 'RRRR-MM') || '

' month , SUM(bytes) growth FROM sys.v_$datafile GROUP BY TO_CHAR(creation_time, 'RRRR-MM') ORDER BY TO_CHAR(creation_time, 'RRRR-MM'); prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - TABLESPACE EXTENTS - | -- +----------------------------------------------------------------------------+ prompt prompt Tablespace Extents

CLEAR COLUMNS BREAKS COMPUTES COLUMN tablespace_name HEADING 'Tablespace Name' ENTMAP off COLUMN largest_ext FORMAT 999,999,999,999,999 HEADING 'Largest Extent' ENTMAP off COLUMN smallest_ext FORMAT 999,999,999,999,999 HEADING 'Smallest Extent' ENTMAP off COLUMN total_free FORMAT 999,999,999,999,999 HEADING 'Total Free' ENTMAP off COLUMN pieces FORMAT 999,999,999,999,999 HEADING 'Number of Free Extents' ENTMAP off break on report compute sum label 'Total:' of largest_ext smallest_ext total_free pieces on report SELECT '' || tablespace_name || '' tablespace_name , max(bytes) largest_ext , min(bytes) smallest_ext , sum(bytes) total_free , count(*) pieces FROM dba_free_space GROUP BY tablespace_name ORDER BY tablespace_name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - TABLESPACE TO OWNER - | -- +----------------------------------------------------------------------------+ prompt prompt Tablespace to Owner

CLEAR COLUMNS BREAKS COMPUTES COLUMN tablespace_name FORMAT a75 HEADING 'Tablespace Name' ENTMAP off COLUMN owner FORMAT a75 HEADING 'Owner' ENTMAP off COLUMN segment_type FORMAT a75 HEADING 'Segment Type' ENTMAP off COLUMN bytes FORMAT 999,999,999,999,999 HEADING 'Size (in Bytes)' ENTMAP off COLUMN seg_count FORMAT 999,999,999,999 HEADING 'Segment Count' ENTMAP off BREAK ON report ON tablespace_name COMPUTE sum LABEL 'Total: ' of seg_count bytes ON report SELECT '' || tablespace_name || '' tablespace_name , '

' || owner || '

' owner , '

' || segment_type || '

' segment_type , sum(bytes) bytes , count(*) seg_count FROM dba_segments GROUP BY tablespace_name , owner , segment_type ORDER BY tablespace_name , owner , segment_type; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - OWNER TO TABLESPACE - | -- +----------------------------------------------------------------------------+ prompt prompt Owner to Tablespace

CLEAR COLUMNS BREAKS COMPUTES COLUMN owner FORMAT a75 HEADING 'Owner' ENTMAP off COLUMN tablespace_name FORMAT a75 HEADING 'Tablespace Name' ENTMAP off COLUMN segment_type FORMAT a75 HEADING 'Segment Type' ENTMAP off COLUMN bytes FORMAT 999,999,999,999,999 HEADING 'Size (in Bytes)' ENTMAP off COLUMN seg_count FORMAT 999,999,999,999 HEADING 'Segment Count' ENTMAP off break on report on owner compute sum label 'Total: ' of seg_count bytes on report SELECT '' || owner || '' owner , '

' || tablespace_name || '

' tablespace_name , '

' || segment_type || '

' segment_type , sum(bytes) bytes , count(*) seg_count FROM dba_segments GROUP BY owner , tablespace_name , segment_type ORDER BY owner , tablespace_name , segment_type; prompt [Top]

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

UNDO Segments -- +----------------------------------------------------------------------------+ -- | - UNDO RETENTION PARAMETERS - | -- +----------------------------------------------------------------------------+ prompt prompt UNDO Retention Parameters

prompt undo_retention is specified in minutes CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name_print FORMAT a95 HEADING 'Instance Name' ENTMAP off COLUMN thread_number_print FORMAT a95 HEADING 'Thread Number' ENTMAP off COLUMN name FORMAT a125 HEADING 'Name' ENTMAP off COLUMN value HEADING 'Value' ENTMAP off BREAK ON report ON instance_name_print ON thread_number_print SELECT '

' || i.instance_name || '

' instance_name_print , '

' || i.thread# || '

' thread_number_print , '

' || p.name || '

' name , (CASE p.name WHEN 'undo_retention' THEN '

' || TO_CHAR(TO_NUMBER(p.value)/60, '999,999,999,999,999') || '

' ELSE '

' || p.value || '

' END) value FROM gv$parameter p , gv$instance i WHERE p.inst_id = i.inst_id AND p.name LIKE 'undo%' ORDER BY i.instance_name , p.name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - UNDO SEGMENTS - | -- +----------------------------------------------------------------------------+ prompt prompt UNDO Segments

CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name FORMAT a75 HEADING 'Instance Name' ENTMAP off COLUMN tablespace FORMAT a85 HEADING 'Tablspace' ENTMAP off COLUMN roll_name HEADING 'UNDO Segment Name' ENTMAP off COLUMN in_extents HEADING 'Init/Next Extents' ENTMAP off COLUMN m_extents HEADING 'Min/Max Extents' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN wraps FORMAT 999,999,999 HEADING 'Wraps' ENTMAP off COLUMN shrinks FORMAT 999,999,999 HEADING 'Shrinks' ENTMAP off COLUMN opt FORMAT 999,999,999,999 HEADING 'Opt. Size' ENTMAP off COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes' ENTMAP off COLUMN extents FORMAT 999,999,999 HEADING 'Extents' ENTMAP off CLEAR COMPUTES BREAKS BREAK ON report ON instance_name ON tablespace -- COMPUTE sum LABEL 'Total:' OF bytes extents shrinks wraps ON report SELECT '

' || NVL(i.instance_name, '
') || '

' instance_name , '

' || a.tablespace_name || '

' tablespace , '

' || a.owner || '.' || a.segment_name || '

' roll_name , '

' || TO_CHAR(a.initial_extent) || ' / ' || TO_CHAR(a.next_extent) || '

' in_extents , '

' || TO_CHAR(a.min_extents) || ' / ' || TO_CHAR(a.max_extents) || '

' m_extents , DECODE( a.status , 'OFFLINE' , '

' || a.status || '

' , '

' || a.status || '

') status , b.bytes bytes , b.extents extents , d.shrinks shrinks , d.wraps wraps , d.optsize opt FROM dba_rollback_segs a , dba_segments b , v$rollname c , v$rollstat d , gv$parameter p , gv$instance i WHERE a.segment_name = b.segment_name AND a.segment_name = c.name (+) AND c.usn = d.usn (+) AND p.name (+) = 'undo_tablespace' AND p.value (+) = a.tablespace_name AND p.inst_id = i.inst_id (+) ORDER BY a.tablespace_name , a.segment_name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - UNDO SEGMENT CONTENTION - | -- +----------------------------------------------------------------------------+ prompt prompt UNDO Segment Contention

prompt UNDO statistics from V$ROLLSTAT - (ordered by waits) CLEAR COLUMNS BREAKS COMPUTES COLUMN roll_name HEADING 'UNDO Segment Name' ENTMAP off COLUMN gets FORMAT 999,999,999 HEADING 'Gets' ENTMAP off COLUMN waits FORMAT 999,999,999 HEADING 'Waits' ENTMAP off COLUMN immediate_misses FORMAT 999,999,999 HEADING 'Immediate Misses' ENTMAP off COLUMN hit_ratio HEADING 'Hit Ratio' ENTMAP off BREAK ON report COMPUTE SUM label 'Total:' OF gets waits ON report SELECT '' || b.name || '' roll_name , gets gets , waits waits , '

' || TO_CHAR(ROUND(((gets - waits)*100)/gets, 2)) || '%

' hit_ratio FROM sys.v_$rollstat a , sys.v_$rollname b WHERE a.USN = b.USN ORDER BY waits DESC; prompt prompt Wait statistics CLEAR COLUMNS BREAKS COMPUTES COLUMN class HEADING 'Class' COLUMN ratio HEADING 'Wait Ratio' SELECT '' || w.class || '' class , '

' || TO_CHAR(ROUND(100*(w.count/SUM(s.value)),8)) || '%

' ratio FROM v$waitstat w , v$sysstat s WHERE w.class IN ( 'system undo header' , 'system undo block' , 'undo header' , 'undo block' ) AND s.name IN ('db block gets', 'consistent gets') GROUP BY w.class , w.count; prompt [Top]

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

Backups -- +----------------------------------------------------------------------------+ -- | - RMAN BACKUP JOBS - | -- +----------------------------------------------------------------------------+ prompt prompt RMAN Backup Jobs

prompt Last 10 RMAN backup jobs CLEAR COLUMNS BREAKS COMPUTES COLUMN backup_name FORMAT a130 HEADING 'Backup Name' ENTMAP off COLUMN start_time FORMAT a75 HEADING 'Start Time' ENTMAP off COLUMN elapsed_time FORMAT a75 HEADING 'Elapsed Time' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN input_type HEADING 'Input Type' ENTMAP off COLUMN output_device_type HEADING 'Output Devices' ENTMAP off COLUMN input_size HEADING 'Input Size' ENTMAP off COLUMN output_size HEADING 'Output Size' ENTMAP off COLUMN output_rate_per_sec HEADING 'Output Rate Per Sec' ENTMAP off SELECT '

' || r.command_id || '

' backup_name , '

' || TO_CHAR(r.start_time, 'mm/dd/yyyy HH24:MI:SS') || '

' start_time , '

' || r.time_taken_display || '

' elapsed_time , DECODE( r.status , 'COMPLETED' , '

' || r.status || '

' , 'RUNNING' , '

' || r.status || '

' , 'FAILED' , '

' || r.status || '

' , '

' || r.status || '

' ) status , r.input_type input_type , r.output_device_type output_device_type , '

' || r.input_bytes_display || '

' input_size , '

' || r.output_bytes_display || '

' output_size , '

' || r.output_bytes_per_sec_display || '

' output_rate_per_sec FROM (select command_id , start_time , time_taken_display , status , input_type , output_device_type , input_bytes_display , output_bytes_display , output_bytes_per_sec_display from v$rman_backup_job_details order by start_time DESC ) r WHERE rownum < 11; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - RMAN CONFIGURATION - | -- +----------------------------------------------------------------------------+ prompt prompt RMAN Configuration

prompt All non-default RMAN configuration settings CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT a130 HEADING 'Name' ENTMAP off COLUMN value HEADING 'Value' ENTMAP off SELECT '

' || name || '

' name , value FROM v$rman_configuration ORDER BY name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - RMAN BACKUP SETS - | -- +----------------------------------------------------------------------------+ prompt prompt RMAN Backup Sets

prompt Available backup sets contained in the control file including available and expired backup sets CLEAR COLUMNS BREAKS COMPUTES COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off COLUMN backup_type FORMAT a70 HEADING 'Backup Type' ENTMAP off COLUMN device_type HEADING 'Device Type' ENTMAP off COLUMN controlfile_included FORMAT a30 HEADING 'Controlfile Included?' ENTMAP off COLUMN spfile_included FORMAT a30 HEADING 'SPFILE Included?' ENTMAP off COLUMN incremental_level HEADING 'Incremental Level' ENTMAP off COLUMN pieces FORMAT 999,999,999,999 HEADING '# of Pieces' ENTMAP off COLUMN start_time FORMAT a75 HEADING 'Start Time' ENTMAP off COLUMN completion_time FORMAT a75 HEADING 'End Time' ENTMAP off COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off COLUMN tag HEADING 'Tag' ENTMAP off COLUMN block_size FORMAT 999,999,999,999,999 HEADING 'Block Size' ENTMAP off COLUMN keep FORMAT a40 HEADING 'Keep?' ENTMAP off COLUMN keep_until FORMAT a75 HEADING 'Keep Until' ENTMAP off COLUMN keep_options FORMAT a15 HEADING 'Keep Options' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total:' OF pieces elapsed_seconds ON report SELECT '

' || bs.recid || '

' bs_key , DECODE(backup_type , 'L', '

Archived Redo Logs

' , 'D', '

Datafile Full Backup

' , 'I', '

Incremental Backup

') backup_type , '

' || device_type || '

' device_type , '

' || DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included) || '

' controlfile_included , '

' || NVL(sp.spfile_included, '-') || '

' spfile_included , bs.incremental_level incremental_level , bs.pieces pieces , '

' || TO_CHAR(bs.start_time, 'mm/dd/yyyy HH24:MI:SS') || '

' start_time , '

' || TO_CHAR(bs.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '

' completion_time , bs.elapsed_seconds elapsed_seconds , bp.tag tag , bs.block_size block_size , '

' || bs.keep || '

' keep , '

' || NVL(TO_CHAR(bs.keep_until, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' keep_until , bs.keep_options keep_options FROM v$backup_set bs , (select distinct set_stamp , set_count , tag , device_type from v$backup_piece where status in ('A', 'X')) bp , (select distinct set_stamp, set_count, 'YES' spfile_included from v$backup_spfile) sp WHERE bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bs.set_stamp = sp.set_stamp (+) AND bs.set_count = sp.set_count (+) ORDER BY bs.recid; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - RMAN BACKUP PIECES - | -- +----------------------------------------------------------------------------+ prompt prompt RMAN Backup Pieces

prompt Available backup pieces contained in the control file including available and expired backup sets CLEAR COLUMNS BREAKS COMPUTES COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off COLUMN piece# HEADING 'Piece #' ENTMAP off COLUMN copy# HEADING 'Copy #' ENTMAP off COLUMN bp_key HEADING 'BP Key' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN handle HEADING 'Handle' ENTMAP off COLUMN start_time FORMAT a75 HEADING 'Start Time' ENTMAP off COLUMN completion_time FORMAT a75 HEADING 'End Time' ENTMAP off COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off BREAK ON bs_key SELECT '

' || bs.recid || '

' bs_key , bp.piece# piece# , bp.copy# copy# , bp.recid bp_key , DECODE( status , 'A', '

Available

' , 'D', '

Deleted

' , 'X', '

Expired

') status , handle handle , '

' || TO_CHAR(bp.start_time, 'mm/dd/yyyy HH24:MI:SS') || '

' start_time , '

' || TO_CHAR(bp.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '

' completion_time , bp.elapsed_seconds elapsed_seconds FROM v$backup_set bs , v$backup_piece bp WHERE bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bp.status IN ('A', 'X') ORDER BY bs.recid , piece#; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - RMAN BACKUP CONTROL FILES - | -- +----------------------------------------------------------------------------+ prompt prompt RMAN Backup Control Files

prompt Available automatic control files within all available (and expired) backup sets CLEAR COLUMNS BREAKS COMPUTES COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off COLUMN piece# HEADING 'Piece #' ENTMAP off COLUMN copy# HEADING 'Copy #' ENTMAP off COLUMN bp_key HEADING 'BP Key' ENTMAP off COLUMN controlfile_included FORMAT a75 HEADING 'Controlfile Included?' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN handle HEADING 'Handle' ENTMAP off COLUMN start_time FORMAT a40 HEADING 'Start Time' ENTMAP off COLUMN completion_time FORMAT a40 HEADING 'End Time' ENTMAP off COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off BREAK ON bs_key SELECT '

' || bs.recid || '

' bs_key , bp.piece# piece# , bp.copy# copy# , bp.recid bp_key , '

' || DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included) || '

' controlfile_included , DECODE( status , 'A', '

Available

' , 'D', '

Deleted

' , 'X', '

Expired

') status , handle handle FROM v$backup_set bs , v$backup_piece bp WHERE bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bp.status IN ('A', 'X') AND bs.controlfile_included != 'NO' ORDER BY bs.recid , piece#; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - RMAN BACKUP SPFILE - | -- +----------------------------------------------------------------------------+ prompt prompt RMAN Backup SPFILE

prompt Available automatic SPFILE backups within all available (and expired) backup sets CLEAR COLUMNS BREAKS COMPUTES COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off COLUMN piece# HEADING 'Piece #' ENTMAP off COLUMN copy# HEADING 'Copy #' ENTMAP off COLUMN bp_key HEADING 'BP Key' ENTMAP off COLUMN spfile_included FORMAT a75 HEADING 'SPFILE Included?' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN handle HEADING 'Handle' ENTMAP off COLUMN start_time FORMAT a40 HEADING 'Start Time' ENTMAP off COLUMN completion_time FORMAT a40 HEADING 'End Time' ENTMAP off COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off BREAK ON bs_key SELECT '

' || bs.recid || '

' bs_key , bp.piece# piece# , bp.copy# copy# , bp.recid bp_key , '

' || NVL(sp.spfile_included, '-') || '

' spfile_included , DECODE( status , 'A', '

Available

' , 'D', '

Deleted

' , 'X', '

Expired

') status , handle handle FROM v$backup_set bs , v$backup_piece bp , (select distinct set_stamp, set_count, 'YES' spfile_included from v$backup_spfile) sp WHERE bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bp.status IN ('A', 'X') AND bs.set_stamp = sp.set_stamp AND bs.set_count = sp.set_count ORDER BY bs.recid , piece#; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - ARCHIVING MODE - | -- +----------------------------------------------------------------------------+ prompt prompt Archiving Mode

CLEAR COLUMNS BREAKS COMPUTES COLUMN db_log_mode FORMAT a95 HEADING 'Database|Log Mode' ENTMAP off COLUMN log_archive_start FORMAT a95 HEADING 'Automatic|Archival' ENTMAP off COLUMN oldest_online_log_sequence FORMAT 999999999999999 HEADING 'Oldest Online |Log Sequence' ENTMAP off COLUMN current_log_seq FORMAT 999999999999999 HEADING 'Current |Log Sequence' ENTMAP off SELECT '

' || d.log_mode || '

' db_log_mode , '

' || p.log_archive_start || '

' log_archive_start , c.current_log_seq current_log_seq , o.oldest_online_log_sequence oldest_online_log_sequence FROM (select DECODE( log_mode , 'ARCHIVELOG', 'Archive Mode' , 'NOARCHIVELOG', 'No Archive Mode' , log_mode ) log_mode from v$database ) d , (select DECODE( log_mode , 'ARCHIVELOG', 'Enabled' , 'NOARCHIVELOG', 'Disabled') log_archive_start from v$database ) p , (select a.sequence# current_log_seq from v$log a where a.status = 'CURRENT' and thread# = &_thread_number ) c , (select min(a.sequence#) oldest_online_log_sequence from v$log a where thread# = &_thread_number ) o / prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - ARCHIVE DESTINATIONS - | -- +----------------------------------------------------------------------------+ prompt prompt Archive Destinations

CLEAR COLUMNS BREAKS COMPUTES COLUMN dest_id HEADING 'Destination|ID' ENTMAP off COLUMN dest_name HEADING 'Destination|Name' ENTMAP off COLUMN destination HEADING 'Destination' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN schedule HEADING 'Schedule' ENTMAP off COLUMN archiver HEADING 'Archiver' ENTMAP off COLUMN log_sequence FORMAT 999999999999999 HEADING 'Current Log|Sequence' ENTMAP off SELECT '

' || a.dest_id || '

' dest_id , a.dest_name dest_name , a.destination destination , DECODE( a.status , 'VALID', '

' || status || '

' , 'INACTIVE', '

' || status || '

' , '

' || status || '

' ) status , DECODE( a.schedule , 'ACTIVE', '

' || schedule || '

' , 'INACTIVE', '

' || schedule || '

' , '

' || schedule || '

' ) schedule , a.archiver archiver , a.log_sequence log_sequence FROM v$archive_dest a ORDER BY a.dest_id / prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - ARCHIVING INSTANCE PARAMETERS - | -- +----------------------------------------------------------------------------+ prompt prompt Archiving Instance Parameters

CLEAR COLUMNS BREAKS COMPUTES COLUMN name HEADING 'Parameter Name' ENTMAP off COLUMN value HEADING 'Parameter Value' ENTMAP off SELECT '' || a.name || '' name , a.value value FROM v$parameter a WHERE a.name like 'log_%' ORDER BY a.name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - ARCHIVING HISTORY - | -- +----------------------------------------------------------------------------+ prompt prompt Archiving History

CLEAR COLUMNS BREAKS COMPUTES COLUMN thread# FORMAT a79 HEADING 'Thread#' ENTMAP off COLUMN sequence# FORMAT a79 HEADING 'Sequence#' ENTMAP off COLUMN name HEADING 'Name' ENTMAP off COLUMN first_change# HEADING 'First|Change #' ENTMAP off COLUMN first_time FORMAT a75 HEADING 'First|Time' ENTMAP off COLUMN next_change# HEADING 'Next|Change #' ENTMAP off COLUMN next_time FORMAT a75 HEADING 'Next|Time' ENTMAP off COLUMN log_size FORMAT 999,999,999,999,999 HEADING 'Size (in bytes)' ENTMAP off COLUMN archived FORMAT a31 HEADING 'Archived?' ENTMAP off COLUMN applied FORMAT a31 HEADING 'Applied?' ENTMAP off COLUMN deleted FORMAT a31 HEADING 'Deleted?' ENTMAP off COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off BREAK ON report ON thread# SELECT '

' || thread# || '

' thread# , '

' || sequence# || '

' sequence# , name , first_change# , '

' || TO_CHAR(first_time, 'mm/dd/yyyy HH24:MI:SS') || '

' first_time , next_change# , '

' || TO_CHAR(next_time, 'mm/dd/yyyy HH24:MI:SS') || '

' next_time , (blocks * block_size) log_size , '

' || archived || '

' archived , '

' || applied || '

' applied , '

' || deleted || '

' deleted , DECODE( status , 'A', '

Available

' , 'D', '

Deleted

' , 'U', '

Unavailable

' , 'X', '

Expired

' ) status FROM v$archived_log WHERE status in ('A') ORDER BY thread# , sequence#; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - FLASH RECOVERY AREA PARAMETERS - | -- +----------------------------------------------------------------------------+ prompt prompt Flash Recovery Area Parameters

prompt db_recovery_file_dest_size is specified in bytes CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name_print FORMAT a95 HEADING 'Instance Name' ENTMAP off COLUMN thread_number_print FORMAT a95 HEADING 'Thread Number' ENTMAP off COLUMN name FORMAT a125 HEADING 'Name' ENTMAP off COLUMN value HEADING 'Value' ENTMAP off BREAK ON report ON instance_name_print ON thread_number_print SELECT '

' || i.instance_name || '

' instance_name_print , '

' || i.thread# || '

' thread_number_print , '

' || p.name || '

' name , (CASE p.name WHEN 'db_recovery_file_dest_size' THEN '

' || TO_CHAR(p.value, '999,999,999,999,999') || '

' ELSE '

' || NVL(p.value, '(null)') || '

' END) value FROM gv$parameter p , gv$instance i WHERE p.inst_id = i.inst_id AND p.name IN ('db_recovery_file_dest_size', 'db_recovery_file_dest') ORDER BY 1 , 3; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - FLASH RECOVERY AREA STATUS - | -- +----------------------------------------------------------------------------+ prompt prompt Flash Recovery Area Status

prompt Current location, disk quota, space in use, space reclaimable by deleting files, and number of files in the Flash Recovery Area CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT a75 HEADING 'Name' ENTMAP off COLUMN space_limit FORMAT 99,999,999,999,999 HEADING 'Space Limit' ENTMAP off COLUMN space_used FORMAT 99,999,999,999,999 HEADING 'Space Used' ENTMAP off COLUMN space_used_pct FORMAT 999.99 HEADING '% Used' ENTMAP off COLUMN space_reclaimable FORMAT 99,999,999,999,999 HEADING 'Space Reclaimable' ENTMAP off COLUMN pct_reclaimable FORMAT 999.99 HEADING '% Reclaimable' ENTMAP off COLUMN number_of_files FORMAT 999,999 HEADING 'Number of Files' ENTMAP off SELECT '

' || name || '

' name , space_limit space_limit , space_used space_used , ROUND((space_used / DECODE(space_limit, 0, 0.000001, space_limit))*100, 2) space_used_pct , space_reclaimable space_reclaimable , ROUND((space_reclaimable / DECODE(space_limit, 0, 0.000001, space_limit))*100, 2) pct_reclaimable , number_of_files number_of_files FROM v$recovery_file_dest ORDER BY name; CLEAR COLUMNS BREAKS COMPUTES COLUMN file_type FORMAT a75 HEADING 'File Type' COLUMN percent_space_used HEADING 'Percent Space Used' COLUMN percent_space_reclaimable HEADING 'Percent Space Reclaimable' COLUMN number_of_files FORMAT 999,999 HEADING 'Number of Files' SELECT '

' || file_type || '

' file_type , percent_space_used percent_space_used , percent_space_reclaimable percent_space_reclaimable , number_of_files number_of_files FROM v$flash_recovery_area_usage; prompt [Top]

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

Flashback Technologies -- +----------------------------------------------------------------------------+ -- | - FLASHBACK DATABASE PARAMETERS - | -- +----------------------------------------------------------------------------+ prompt prompt Flashback Database Parameters

prompt db_flashback_retention_target is specified in minutes prompt db_recovery_file_dest_size is specified in bytes CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name_print FORMAT a95 HEADING 'Instance Name' ENTMAP off COLUMN thread_number_print FORMAT a95 HEADING 'Thread Number' ENTMAP off COLUMN name FORMAT a125 HEADING 'Name' ENTMAP off COLUMN value HEADING 'Value' ENTMAP off BREAK ON report ON instance_name_print ON thread_number_print SELECT '

' || i.instance_name || '

' instance_name_print , '

' || i.thread# || '

' thread_number_print , '

' || p.name || '

' name , (CASE p.name WHEN 'db_recovery_file_dest_size' THEN '

' || TO_CHAR(p.value, '999,999,999,999,999') || '

' WHEN 'db_flashback_retention_target' THEN '

' || TO_CHAR(p.value, '999,999,999,999,999') || '

' ELSE '

' || NVL(p.value, '(null)') || '

' END) value FROM gv$parameter p , gv$instance i WHERE p.inst_id = i.inst_id AND p.name IN ('db_flashback_retention_target', 'db_recovery_file_dest_size', 'db_recovery_file_dest') ORDER BY 1 , 3; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - FLASHBACK DATABASE STATUS - | -- +----------------------------------------------------------------------------+ prompt prompt Flashback Database Status

CLEAR COLUMNS BREAKS COMPUTES COLUMN dbid HEADING 'DB ID' ENTMAP off COLUMN name FORMAT A75 HEADING 'DB Name' ENTMAP off COLUMN log_mode FORMAT A75 HEADING 'Log Mode' ENTMAP off COLUMN flashback_on FORMAT A75 HEADING 'Flashback DB On?' ENTMAP off SELECT '

' || dbid || '

' dbid , '

' || name || '

' name , '

' || log_mode || '

' log_mode , '

' || flashback_on || '

' flashback_on FROM v$database; CLEAR COLUMNS BREAKS COMPUTES COLUMN oldest_flashback_time FORMAT a125 HEADING 'Oldest Flashback Time' ENTMAP off COLUMN oldest_flashback_scn HEADING 'Oldest Flashback SCN' ENTMAP off COLUMN retention_target FORMAT 999,999 HEADING 'Retention Target (min)' ENTMAP off COLUMN retention_target_hours FORMAT 999,999 HEADING 'Retention Target (hour)' ENTMAP off COLUMN flashback_size FORMAT 9,999,999,999,999 HEADING 'Flashback Size' ENTMAP off COLUMN estimated_flashback_size FORMAT 9,999,999,999,999 HEADING 'Estimated Flashback Size' ENTMAP off SELECT '

' || TO_CHAR(oldest_flashback_time,'mm/dd/yyyy HH24:MI:SS') || '

' oldest_flashback_time , oldest_flashback_scn oldest_flashback_scn , retention_target retention_target , retention_target/60 retention_target_hours , flashback_size flashback_size , estimated_flashback_size estimated_flashback_size FROM v$flashback_database_log ORDER BY 1; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - FLASHBACK DATABASE REDO TIME MATRIX - | -- +----------------------------------------------------------------------------+ prompt prompt Flashback Database Redo Time Matrix

CLEAR COLUMNS BREAKS COMPUTES COLUMN begin_time FORMAT a75 HEADING 'Begin Time' ENTMAP off COLUMN end_time FORMAT a75 HEADING 'End Time' ENTMAP off COLUMN flashback_data FORMAT 9,999,999,999,999 HEADING 'Flashback Data' ENTMAP off COLUMN db_data FORMAT 9,999,999,999,999 HEADING 'DB Data' ENTMAP off COLUMN redo_data FORMAT 9,999,999,999,999 HEADING 'Redo Data' ENTMAP off COLUMN estimated_flashback_size FORMAT 9,999,999,999,999 HEADING 'Estimated Flashback Size' ENTMAP off SELECT '

' || TO_CHAR(begin_time,'mm/dd/yyyy HH24:MI:SS') || '

' begin_time , '

' || TO_CHAR(end_time,'mm/dd/yyyy HH24:MI:SS') || '

' end_time , flashback_data , db_data , redo_data , estimated_flashback_size FROM v$flashback_database_stat ORDER BY begin_time; prompt [Top]

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

Performance -- +----------------------------------------------------------------------------+ -- | - SGA INFORMATION - | -- +----------------------------------------------------------------------------+ prompt prompt SGA Information

CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name FORMAT a79 HEADING 'Instance Name' ENTMAP off COLUMN name FORMAT a150 HEADING 'Pool Name' ENTMAP off COLUMN value FORMAT 999,999,999,999,999 HEADING 'Bytes' ENTMAP off BREAK ON report ON instance_name COMPUTE sum LABEL 'Total:' OF value ON instance_name SELECT '