©2000RevealNet Inc

HTML Database Information Report
Verified with IE 5,  Oracle 7,  and Oracle 8

Jody Wisecup,   August 2000
OCP DBA 7.3, 8
Butler Manufacturing Company
Kansas City, Missouri

Table of Contents

Overview
Scheduling
Database Permissions
HTML Report Generation Script
Database Information Report
Conclusion

Overview

The ability  to quickly and easily lookup information about databases can save a DBA a lot of time throughout the day.  Information on items such as database versions, data files, tablespaces,  SGA size, rollback segments, options, database links, and parameter values are things that a DBA use quite frequently in their day-to-day operations .  This type of information can also be invaluable to the DBA in disaster recovery scenarios.

In order to eliminate the need for the DBA to run SQL scripts each time this type of information is required,  a web based reporting solution was developed.  The following paper outlines this process and provides the code necessary to set up an automated Database Information Reporting procedure in a web based format.

Note:  This paper and the source code provided is from a Windows NT point of view but could easily be modified to work under other platforms.  In our situation we generate the reports from an NT server but report on both NT and VMS based Oracle databases.

Return to Table of Contents

Scheduling

The information on the database report is not the type of information that is likely to change frequently during each business day.  For this reason,  we determined that generating database information reports once each morning would be sufficient for our needs.

In order to accomplish this in an NT environment,  the AT scheduler was used.

The first step is to create a .BAT file that can be placed in the AT scheduler to be ran at a specific time(s) each day.  Below is an example of this type of .BAT file.   You will have to make minor alterations to this code in order to fit your environment.

rem**********************************************************************************
rem create_db_report_HTML.bat
rem
rem This .BAT will run a script which will generate DB status information in HTML
rem format for inclusion on the DBA web page.
rem This reporting function can be considered as part of disaster recovery and backup
rem procedures.
rem
rem **********************************************************************************

echo * Begin DB HTML report generation > c:\orant\dba_jobs\create_db_report_HTML.log

rem -----------------------------------------------------------------------
rem These values will only change if this is moved to a different NT server or
rem a different version of Oracle is driving the process.
rem -----------------------------------------------------------------------
set rsql=c:\orant\bin\plus80w.exe
set scrpt=@c:\orant\dba_jobs\create_db_report_HTML.sql

rem -----------------------------------------------------------------------
rem Set these values for each instance and run the script
rem -----------------------------------------------------------------------
set sidv=sid1
set cstr=sid1_connectstring
echo Generating report for Instance: %cstr% >> c:\orant\dba_jobs\create_db_report_HTML.log
set lval=dbreport/password@%cstr%
set target=\\ntserver\dba_reports\%sidv%_db_report.htm
START /WAIT %rsql% %lval% %scrpt% %target%
echo Report Complete: %cstr% >> c:\orant\dba_jobs\create_db_report_HTML.log

rem -----------------------------------------------------------------------
rem Set these values for each instance and run the script
rem -----------------------------------------------------------------------
set sidv=sid2
set cstr=sid2_connectstring
echo Generating report for Instance: %cstr% >> c:\orant\dba_jobs\create_db_report_HTML.log
set lval=dbreport/password@%cstr%
set target=\\ntserver\dba_reports\%sidv%_db_report.htm
START /WAIT %rsql% %lval% %scrpt% %target%
echo Report Complete: %cstr% >> c:\orant\dba_jobs\create_db_report_HTML.log

rem ....
rem continue the list will all of the databases you wish to report on....
rem ....

rem -----------------------------------------------------------------------
rem End of Batch file
rem -----------------------------------------------------------------------
echo * End DB HTML report generation >> c:\orant\dba_jobs\create_db_report_HTML.log

Return to Table of Contents

Database Permissions

In the above .BAT file a user name of "dbreport" was used as an example.   A user will be needed in each database that you wish to report on and that user will require at a minimum the CONNECT and SELECT ANY TABLE privileges.  Another option is to connect as SYS when running the SQL script.

Return to Table of Contents

HTML Report Generation Script

The following is the source code for the create_db_report_HTML.SQL script used to generate the database report(s).

--Create_DB_Report.SQL
--This SQL script will generate an HTML database
--report which provides information about a databases
--physical structure.
--
--Database Information includes: General, Data file,
--Tablespace,  File status, Liscense, SGA, Rollback
--segments, Options, DB links, and Parameters.

--This report can be ran manually or can be sceduled
--to run through a batch process by connecting to
--each desired database and passing in the output
--report name.

--Replace RETURN_PAGE.HTM in this source with the
--valid location of the link you want to return to.

--COMMENT out the direct spool line and UNCOMMENT the
--the 'spool &1'line if you are automating
--this process and want to pass the output file name
--into the script. (IE.  \\srv1\dba\<sid>_db_report.htm)
spool dbreport.htm
--spool &1

set termout off
set echo off
set verify off
set feedback off
set head off
set wrap on
set linesize 300
set pagesize 0

select '<html>' from dual;
select '<head>' from dual;
select '<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">' from dual;
select '<title>Physical DB Report</title>' from dual;
select '</head>' from dual;

select '<body vlink="#0000FF" alink="#0000FF">' from dual;

select '<table border="0" cellpadding="0" cellspacing="0" width="100%" bgcolor="#000080"' from dual;
select 'height="77">' from dual;
select '  <tr>' from dual;
select '    <td width="15%" height="71"><strong><font color="#FFFFFF" size="4" face="Tempus Sans ITC">Physical' from dual;
select '    Database<br>' from dual;
select '    Report</font><font color="#FFFFFF" size="4"><br>' from dual;
select '    </font></strong></td>' from dual;
select '    <td width="85%" height="71">' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<p align="center"><font color="#000080"><big>Physical Database Information Report </big>(Last Updated: '||sysdate||')   ' from dual;
select '<big> </big></font><small><font color="#0000FF"> </font><a' from dual;
select 'href="RETURN_PAGE.HTM"><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

-- <------------------------------------------------------------------------------------>

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '<tr><td width="10%"><B>'||'INSTANCE:'||'</B></td><td width="20%">'||name|| -
       '</td><td width="10%"><B>'||'Log Mode:'||'</B></td><td width="20%">'||log_mode||'</td>' -
       from v$database;
select distinct '<td width="10%"><B>'||'NODE: '||'</B></td><td width="30%">'||machine||'</td></tr>' -
       from v$session where type = 'BACKGROUND';
select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '<tr><td width="10%">'||' '||'</td><td width="90%">'||banner||'</td></tr>'  from v$version;
select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>Data File Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="20%"><b>Tablespace Name</b></td>' from dual;
select '    <td width="10%"><b>File ID</b></td>' from dual;
select '    <td width="40%"><b>File Name</b></td>' from dual;
select '    <td width="30%"><b>Bytes</b></td>' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

column file_name format a40
column bytes format 999,999,999,999
column totbytes format 999,999,999,999
break on report on tablespace_name
variable rc1 refcursor;
variable rc2 refcursor;
variable rc3 refcursor;
declare
t1 number(12);
t2 number(12);
t3 number(12);
t4 number(12);
begin
select sum(1048576)into t1
from v$controlfile;
select sum(a.bytes)into t2
from v$log a, v$logfile b
where a.group# = b.group#;
select sum(bytes) into t3
from dba_data_files;
t4 := t1 + t2 + t3;
open :rc1 for select '<tr><td width="20%">','online redo log group number :' tablespace_name,'</td><td width="10%">',b.group# file_id,'</td><td width="40%">',b.member file_name,'</td><td width="30%">',a.bytes,'</td></tr>'
from v$log a, v$logfile b, dual c where a.group# = b.group#
union
select '<tr><td width="20%">','controlfile number : ' tablespace_name,'</td><td width="10%">',rownum file_id,'</td><td width="40%">',name file_name,'</td><td width="30%">',1048576 bytes,'</td></tr>'
from v$controlfile
union
select '<tr><td width="20%">',tablespace_name,'</td><td width="10%">',file_id,'</td><td width="40%">',file_name,'</td><td width="30%">',bytes,'</td></tr>'
from dba_data_files
order by 2;
open :rc2 for select '<tr><td width="20%">',null,'</td><td width="10%">',null,'</td><td width="40%">',null,'</td><td width="30%">','---------------------','</td></tr>' from dual;
open :rc3 for select '<tr><td width="20%">','Total Bytes','</td><td width="10%">',null,'</td><td width="40%">',null,'</td><td width="30%">',t4 totbytes,'</td></tr>' from dual;
end;
/
print rc1
print rc2
print rc3
select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>Tablespace Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="30%"><b>Tablespace Name</b></td>' from dual;
select '    <td width="15%"><b>Free Blocks</b></td>' from dual;
select '    <td width="55%"><b>Free Bytes</b></td>' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

column "Free Blocks" format 999,999,999,999
column "Free Bytes" format 999,999,999,999
column totbytes format 999,999,999,999
column totblocks format 999,999,999,999

variable rc1 refcursor;
variable rc2 refcursor;
variable rc3 refcursor;
declare
t1 number(12);
t2 number(12);
begin
select sum(blocks) into t1 from dba_free_space;
select sum(bytes)  into t2 from dba_free_space;
open :rc1 for select distinct '<tr><td width="30%">',tablespace_name,'</td><td width="15%">',sum(blocks) "Free Blocks",'</td><td width="55%">', sum(bytes) "Free Bytes",'</td></tr>'
from   dba_free_space
group by tablespace_name;
open :rc2 for select '<tr><td width="30%">',null,'</td><td width="15%">','-----------------','</td><td width="55%">','-----------------','</td></tr>' from dual;
open :rc3 for select '<tr><td width="30%">','Totals','</td><td width="15%">',t1 totblocks,'</td><td width="55%">',t2 totbytes,'</td></tr>' from dual;
end;
/
print rc1
print rc2
print rc3

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>V$FILESTAT Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="10%"><b>File#</b></td>' from dual;
select '    <td width="15%"><b>Phyrds</b></td>' from dual;
select '    <td width="15%"><b>Phywrts</b></td>' from dual;
select '    <td width="15%"><b>Phyblkrd</b></td>' from dual;
select '    <td width="15%"><b>Phyblkwrt</b></td>' from dual;
select '    <td width="15%"><b>Readtim</b></td>' from dual;
select '    <td width="15%"><b>Writetim</b></td>' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="10%">'||file#,'</td><td width="15%">'||phyrds,'</td><td width="15%">'||phywrts,'</td><td width="15%">'||phyblkrd,'</td><td width="15%">'||phyblkwrt,'</td><td width="15%">'||readtim,'</td><td width="15%">'||writetim||'</td></tr>'
from v$filestat
order by file#;

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>V$LICENSE Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="20%"><b>Sessions</b></td>' from dual;
select '    <td width="20%"><b>Sessions</b></td>' from dual;
select '    <td width="20%"><b>Sessions</b></td>' from dual;
select '    <td width="20%"><b>Sessions</b></td>' from dual;
select '    <td width="20%"><b>Users</b></td>' from dual;
select '  </tr>' from dual;
select '  <tr>' from dual;
select '    <td width="20%"><b>Max</b></td>' from dual;
select '    <td width="20%"><b>Warning</b></td>' from dual;
select '    <td width="20%"><b>Current</b></td>' from dual;
select '    <td width="20%"><b>Highwater</b></td>' from dual;
select '    <td width="20%"><b>Max</b></td>' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="20%">'||sessions_max,'</td><td width="20%">'||sessions_warning,'</td><td width="20%">'||sessions_current,'</td><td width="20%">'||sessions_highwater,'</td><td width="20%">'||users_max||'</td></tr>'
from v$license;

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>V$SGA Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="40%"><b>Name</b></td>' from dual;
select '    <td width="60%"><b>Value</b></td>' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="40%">'||name,'</td><td width="60%">'||value||'</td></tr>'
from v$sga
order by upper(name);

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>V$SGASTAT Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="40%"><b>Name</b></td>' from dual;
select '    <td width="60%"><b>Bytes</b></td>' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="40%">'||name,'</td><td width="60%">'||bytes||'</td></tr>'
from v$sgastat
order by upper(name);

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>DBA_ROLLBACK_SEGS Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="10%"><b>Segment</b></td>' from dual;
select '    <td width="10%"><b>   </b></td>' from dual;
select '    <td width="20%"><b>Tablespace</b></td>' from dual;
select '    <td width="10%"><b>Segment</b></td>' from dual;
select '    <td width="10%"><b>File</b></td>' from dual;
select '    <td width="10%"><b>Initial</b></td>' from dual;
select '    <td width="10%"><b>Next</b></td>' from dual;
select '    <td width="10%"><b>Max</b></td>' from dual;
select '    <td width="10%"><b>   </b></td>' from dual;
select '  </tr>' from dual;
select '  <tr>' from dual;
select '    <td width="10%"><b>Name</b></td>' from dual;
select '    <td width="10%"><b>Owner</b></td>' from dual;
select '    <td width="20%"><b>Name</b></td>' from dual;
select '    <td width="10%"><b>ID</b></td>' from dual;
select '    <td width="10%"><b>ID</b></td>' from dual;
select '    <td width="10%"><b>Extent</b></td>' from dual;
select '    <td width="10%"><b>Extent</b></td>' from dual;
select '    <td width="10%"><b>Extents</b></td>' from dual;
select '    <td width="10%"><b>Status</b></td>' from dual;
select '  </tr>' from dual;

select '</table>' from dual;

column initial_extent format 999,999,999
column next_extent format 999,999,999
column max_extents format 999,999,999,999

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="10%">'||substr(segment_name,1,15) "Seg Name",'</td><td width="10%">'||owner, -
'</td><td width="20%">'||substr(tablespace_name,1,20) "TS Name",'</td><td width="10%">'||segment_id, -
'</td><td width="10%">'||file_id,'</td><td width="10%">'||initial_extent,'</td><td width="10%">'||next_extent, -
'</td><td width="10%">'||max_extents,'</td><td width="10%">'||status||'</td></tr>'
from dba_rollback_segs
order by segment_name;

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>V$ROLLSTAT Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="10%"><b>USN</b></td>' from dual;
select '    <td width="10%"><b>Extents</b></td>' from dual;
select '    <td width="10%"><b>Rssize</b></td>' from dual;
select '    <td width="10%"><b>Writes</b></td>' from dual;
select '    <td width="10%"><b>Gets</b></td>' from dual;
select '    <td width="10%"><b>Waits</b></td>' from dual;
select '    <td width="10%"><b>Optsize</b></td>' from dual;
select '    <td width="10%"><b>Hwmsize</b></td>' from dual;
select '    <td width="20%"><b>Status</b></td>' from dual;
select '  </tr>' from dual;

select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="10%">'||usn,'</td><td width="10%">'||extents,'</td><td width="10%">'||rssize, -
'</td><td width="10%">'||writes,'</td><td width="10%">'||gets,'</td><td width="10%">'||waits, -
'</td><td width="10%">'||optsize,'</td><td width="10%">'||hwmsize,'</td><td width="20%">'||status||'</td></tr>'
from v$rollstat
order by usn;

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>V$OPTION Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="40%"><b>Parameter</b></td>' from dual;
select '    <td width="60%"><b>Value</b></td>' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="40%">'||substr(parameter,1,50),'</td><td width="60%">'||substr(value,1,80)||'</td></tr>'
from v$option
order by upper(parameter);

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>DBA_DB_LINKS Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="20%"><b>Owner</b></td>' from dual;
select '    <td width="20%"><b>DB Link</b></td>' from dual;
select '    <td width="20%"><b>Username</b></td>' from dual;
select '    <td width="40%"><b>Host</b></td>' from dual;
select '  </tr>' from dual;

select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="20%">'||owner,'</td><td width="20%">'||substr(db_link,1,30), -
       '</td><td width="20%">'||substr(username,1,20),'</td><td width="40%">'||substr(host,1,20)||'</td></tr>'
from dba_db_links
order by upper(db_link);

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p align="center"><font color="#000080"><big>V$PARAMETER Information</big></font>' from dual;
select '<a href="RETURN_PAGE.HTM"><small><font color="#0000FF">'||'&'||'lt;Return'||'&'||'gt;</font></a></small></p>' from dual;

select '<p></p>' from dual;
select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;
select '  <tr>' from dual;
select '    <td width="40%"><b>Name</b></td>' from dual;
select '    <td width="60%"><b>Value</b></td>' from dual;
select '  </tr>' from dual;
select '</table>' from dual;

select '<table width="100%" border="0" bgcolor="#EFEFEF" cellspacing="0" cellpadding="0">' from dual;

select '<tr><td width="40%">'||substr(name,1,50),'</td><td width="60%">'||substr(value,1,80)||'</td></tr>' 
from v$parameter
order by upper(name);

select '</table>' from dual;

-- <------------------------------------------------------------------------------------>

select '<p><em><strong><br>' from dual;
select '- End of Report - </strong></em><a href="RETURN_PAGE.HTM"><font color="#0000FF"><small>'||'&'||'lt;Return'||'&'||'gt;</small></font></a></p>' from dual;
select '</body>' from dual;
select '</html>' from dual;

spool off

set head on
set echo on
set feedback on
set termout on

exit;

Return to Table of Contents

Database Information Report

The following is a sample of the HTML format report that is generated for a specified database.

Physical Database
Report
  

Physical Database Information Report (Last Updated: 02-AUG-00) 

INSTANCE: CLASS Log Mode: NOARCHIVELOG NODE: TICTOC
  Oracle7 Server Release 7.3.4.0.0 - Production
  PL/SQL Release 2.3.4.0.0 - Production
  CORE Version 3.5.4.0.0 - Production
  TNS for 32-bit Windows: Version 2.3.4.0.0 - Production
  NLSRTL Version 3.2.4.0.0 - Production

Data File Information  

 

Tablespace Name File ID File Name Bytes
INDEXES 4 E:\ORADATA\CLASS_INX.DBS 157,286,400
RBS1 5 F:\ORADATA\CLASS_RBS1.DBS 20,971,520
  6 F:\ORADATA\CLASS_RBS1A.DBS 52,428,800
SEDONA_1 9 F:\ORADATA\SEDONA_CLASS.DBF 82,720,768
SYSTEM 1 D:\ORANT\DATABASE\SYSCLAS.ORA 209,715,200
TEMP 7 D:\ORADATA\CLASS_TEMP.DBS 10,485,760
  8 D:\ORADATA\CLASS_TEMPA.DBS 52,428,800
USERS 2 F:\ORADATA\CLASS_USERS.DBS 220,200,960
  3 F:\ORADATA\CLASS_USERS2.DBS 136,314,880
controlfile number : 1 D:\ORANT\DATABASE\CTL1CLAS.ORA 1,048,576
  2 E:\ORADATA\CTL2CLAS.ORA 1,048,576
online redo log group number : 1 D:\ORANT\DATABASE\LOG1CLAS.ORA 1,048,576
  2 D:\ORANT\DATABASE\LOG2CLAS.ORA 1,048,576
      ---------------------
Total Bytes     946,747,392

Tablespace Information  

 

Tablespace Name Free Blocks Free Bytes
INDEXES 76,799 157,284,352
RBS1 30,638 62,746,624
SEDONA_1 10 20,480
SYSTEM 63,636 130,326,528
TEMP 30,718 62,910,464
USERS 172,568 353,419,264
  ----------------- -----------------
Totals 374,369 766,707,712

V$FILESTAT Information  

 

File# Phyrds Phywrts Phyblkrd Phyblkwrt Readtim Writetim
1 45187 63 106467 63 0 0
2 47 0 47 0 0 0
3 105 0 155 0 0 0
4 0 0 0 0 0 0
5 88 31 88 31 0 0
6 1 0 1 0 0 0
7 1 0 1 0 0 0
8 0 0 0 0 0 0
9 9600 0 76350 0 0 0

V$LICENSE Information  

 

Sessions Sessions Sessions Sessions Users
Max Warning Current Highwater Max
0 0 3 4 0

V$SGA Information  

 

Name Value
Database Buffers 2048000
Fixed Size 34192
Redo Buffers 8192
Variable Size 18713928

V$SGASTAT Information  

 

Name Bytes
calls 3800
character set memory 21488
db_block_buffers 2048000
db_block_hash_buckets 193328
db_block_multiple_hashcha 29120
db_files 8920
dictionary cache 671168
distributed_transactions- 5576
DML locks 10800
ENQUEUE STATS 6944
enqueue_locks 39832
enqueue_resources 11160
event statistics 72720
fixed allocation callback 240
fixed_sga 34192
free memory 12012712
gc_* 73568
ktlbk state objects 17688
latch nowait fails or sle 10320
LCCMD SGA Table 13264
library cache 1288752
log_buffer 8192
messages 7360
miscellaneous 32600
node map 16384
PL/SQL DIANA 362232
PL/SQL MPCODE 153928
PLS non-lib hp 2104
processes 37200
SEQ S.O. 6000
session param values 18792
sessions 96960
sql area 3441488
SYSTEM PARAMETERS 8192
transactions 25344
transaction_branches 9216
UNDO INFO 4728

DBA_ROLLBACK_SEGS Information  

 

Segment   Tablespace Segment File Initial Next Max  
Name Owner Name ID ID Extent Extent Extents Status
LRG_RBS PUBLIC RBS1 6 5 102400 102400 99 ONLINE
RBS1 SYS RBS1 2 6 102400 102400 100 OFFLINE
RBS2 SYS RBS1 3 5 102400 102400 100 OFFLINE
RBS3 SYS RBS1 4 5 102400 102400 100 OFFLINE
RBS4 SYS RBS1 5 5 102400 102400 100 OFFLINE
SYSTEM SYS SYSTEM 0 1 51200 51200 121 ONLINE

V$ROLLSTAT Information  

 

USN Extents Rssize Writes Gets Waits Optsize Hwmsize Status
0 16 817152 526 1802 0   817152 ONLINE
6 26 2660352 38045 1831 0   2660352 ONLINE

V$OPTION Information  

 

Parameter Value
distributed TRUE
parallel query TRUE
Parallel Server FALSE
procedural TRUE
replication TRUE
Spatial Data FALSE

DBA_DB_LINKS Information  

 

Owner DB Link Username Host

V$PARAMETER Information  

 

Name Value
always_anti_join NESTED_LOOPS
audit_trail NONE
background_dump_dest %RDBMS73%\trace
bitmap_merge_area_size 1048576
blank_trimming FALSE
b_tree_bitmap_plans FALSE
cache_size_threshold 100
checkpoint_process TRUE
cleanup_rollback_entries 20
close_cached_open_cursors FALSE
commit_point_strength 1
compatible 7.3.4
compatible_no_recovery  
control_files D:\ORANT\DATABASE\ctl1clas.ora, E:\ORADATA\ctl2clas.ora
cpu_count 1
create_bitmap_area_size 8388608
cursor_space_for_time FALSE
dblink_encrypt_login FALSE
db_block_buffers 1000
db_block_checkpoint_batch 8
db_block_checksum FALSE
db_block_lru_extended_statistics 0
db_block_lru_latches 1
db_block_lru_statistics FALSE
db_block_size 2048
db_domain WORLD
db_files 20
db_file_multiblock_read_count 8
db_file_simultaneous_writes 4
db_file_standby_name_convert  
db_name class
delayed_logging_block_cleanouts TRUE
discrete_transactions_enabled FALSE
distributed_lock_timeout 60
distributed_recovery_connection_hold_time 200
distributed_transactions 16
dml_locks 100
enqueue_resources 155
event  
fast_cache_flush FALSE
fixed_date  
gc_db_locks 100
gc_files_to_locks  
gc_freelist_groups 50
gc_lck_procs 1
gc_releasable_locks 1000
gc_rollback_locks 20
gc_rollback_segments 20
gc_save_rollback_locks 20
gc_segments 10
gc_tablespaces 5
global_names FALSE
hash_area_size 0
hash_join_enabled TRUE
hash_multiblock_io_count 1
ifile  
instance_number 0
job_queue_interval 60
job_queue_keep_connections FALSE
job_queue_processes 2
license_max_sessions 0
license_max_users 0
license_sessions_warning 0
log_archive_buffers 4
log_archive_buffer_size 127
log_archive_dest %RDBMS73%\
log_archive_format %%ORACLE_SID%%%S.%T
log_archive_start FALSE
log_block_checksum FALSE
log_buffer 8192
log_checkpoints_to_alert FALSE
log_checkpoint_interval 10000
log_checkpoint_timeout 0
log_files 255
log_file_standby_name_convert  
log_simultaneous_copies 0
log_small_entry_max_size 80
max_commit_propagation_delay 90000
max_dump_file_size 10240
max_enabled_roles 20
max_rollback_segments 30
max_transaction_branches 8
mts_dispatchers  
mts_listener_address (address=(protocol=ipc)(key=%s))
mts_max_dispatchers 0
mts_max_servers 0
mts_multiple_listeners FALSE
mts_servers 0
mts_service  
nls_currency  
nls_date_format DD-MON-RR
nls_date_language  
nls_iso_currency  
nls_language AMERICAN
nls_numeric_characters  
nls_sort  
nls_territory AMERICA
open_cursors 200
open_links 4
optimizer_mode CHOOSE
optimizer_parallel_pass TRUE
optimizer_percent_parallel 0
optimizer_search_limit 5
oracle_trace_collection_name oracled
oracle_trace_collection_path %OTRACE73%\ADMIN\CDF\
oracle_trace_collection_size 5242880
oracle_trace_enable FALSE
oracle_trace_facility_name oracled
oracle_trace_facility_path %OTRACE73%\ADMIN\FDF\
os_authent_prefix  
os_roles FALSE
parallel_default_max_instances 0
parallel_max_servers 5
parallel_min_percent 0
parallel_min_servers 0
parallel_server_idle_time 5
partition_view_enabled FALSE
pre_page_sga FALSE
processes 50
recovery_parallelism 0
remote_dependencies_mode timestamp
remote_login_passwordfile SHARED
remote_os_authent FALSE
remote_os_roles FALSE
resource_limit TRUE
rollback_segments  
row_cache_cursors 10
row_locking always
sequence_cache_entries 10
sequence_cache_hash_buckets 10
serializable FALSE
sessions 60
session_cached_cursors 0
shared_pool_reserved_min_alloc 5000
shared_pool_reserved_size 0
shared_pool_size 18000000
snapshot_refresh_interval 60
snapshot_refresh_keep_connections FALSE
snapshot_refresh_processes 2
sort_area_retained_size 65536
sort_area_size 65536
sort_direct_writes AUTO
sort_read_fac 20
sort_spacemap_size 512
sort_write_buffers 2
sort_write_buffer_size 32768
sql92_security FALSE
sql_trace FALSE
temporary_table_locks 60
text_enable FALSE
thread 0
timed_statistics FALSE
transactions 66
transactions_per_rollback_segment 16
unlimited_rollback_segments FALSE
user_dump_dest %RDBMS73%\trace
utl_file_dir  
v733_plans_enabled FALSE


- End of Report -

Return to Table of Contents

Conclusion

Using the methods described above, it is possible to generate HTML format database reports for any or all of the Oracle databases in your enterprise.  In our case, we generate  reports daily on all of our production databases and have links attached to our DBA Internal Web Page which provide easy access to all DBA's.

This paper is an overview of how we are currently using this type of reporting method.   You may find that there are better ways to write the SQL script and that you require different information that what is generated in this example.   The main purpose of the paper is to outline the process and provide samples of how to achieve this type of database reporting method.

Return to Table of Contents