Getting information about the database

One of the tasks I've recently been given is to collate information about our Oracle and SQLServer databases. Stuff like sizes, init.ora parameters &c. I've developed a script that gets a lot of the stuff we need out of Oracle.

Here it is:

set echo off
set verify off
set feedback off
set linesize 132
set pagesize 60
set heading off
/* This script must be run as SYS (SYSDBA) */


Prompt "Audit Information"
select 'Run on: '||to_char(sysdate, 'HH24:MI DD-MON-YYYY') from dual
/
prompt
prompt

set heading on
column name heading "Database Name"
select name from v$database
/
prompt
column banner heading "Database Version Information"
select banner from v$version
/
prompt
prompt

column log_mode format a16 heading "Archivelog mode"
select log_mode from v$database
/
prompt
prompt


column option_param format a70 heading "Option"
prompt "Options Installed"
select parameter option_param
from v$option
where value='TRUE'
order by parameter
/
prompt
prompt
prompt

prompt "Parameter file parameters"
Prompt "Visible Parameters"
column name format a34 wrap heading "Parameter"
column value format a70 wrap heading "Value"
column isdefault format a9 heading "Default?"
set pagesize 300
select name, value, decode(isdefault, 'TRUE', 'Y', 'FALSE', 'N', '?') isdefault
from v$parameter
where value is not null
order by name
/
prompt
prompt "Hidden Paramteters that are non-default"
select
ksppinm NAME,
ksppstvl VALUE,
ksppdesc description,
ksppstcmnt update_comment
from sys.x$ksppi x,
sys.x$ksppcv y
where (x.indx = y.indx)
and
translate(ksppinm,'_','#') like '#%'
and
ksppstdf='FALSE'
/
prompt
prompt
prompt


Prompt "Tablespace and File Information"
column bigness format 999999.99 heading "Size (Gb)"
column tablespace_name format a20 heading "Tablespace Name"
column nofiles format 99999 heading "No. Files"
column file_name format a60 heading "Filename and Path"
column extent_management format a11 heading "Extent|Management"
prompt "Datafiles"
select a.tablespace_name tablespace_name, count(a.file_name) nofiles, sum(a.bytes)/(1024*1024*1024) bigness, b.extent_management
from dba_data_files a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name
group by a.tablespace_name, b.extent_management
/
column autoextensible heading "Auto|Extensible" format a10
select tablespace_name, file_name, bytes/(1024*1024*1024) bigness, autoextensible
from dba_data_files
order by tablespace_name, file_id
/
prompt
prompt

Prompt "Tempfiles"
select a.tablespace_name, count(a.file_name) nofiles, sum(a.bytes)/(1024*1024*1024) bigness, b.extent_management
from dba_temp_files a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name
group by a.tablespace_name, b.extent_management
/
select tablespace_name, file_name, bytes/(1024*1024*1024) bigness, autoextensible
from dba_temp_files
order by tablespace_name, file_id
/
prompt
prompt


Prompt "Online Redo Logs"
column group# format 999 heading "Group No."
column status format a10
column type format a10
column member format a100 heading "Filename and Path"
select group#, status, type, member
from v$logfile
order by group#, member
/
prompt

column redo_size format 99999.99 heading "File Size Mb"
select group#, bytes/(1024*1024) redo_size
from v$log
order by group#
/
prompt
prompt
prompt


prompt "System/Shared Global Area"
prompt "Summary"
column sga_size format 9999.99 heading "Size (Mb)"
select name parameter, value/(1024*1024) sga_size
from v$sga
/
prompt

prompt "Block Caches"
column cache format a30 heading "Cache Name"
select name cache, value/(1024*1024) sga_size
from v$parameter
where name like 'db_%ache_size'
/
prompt

prompt "Pools"
column pool heading "Pool|Name"
select pool, sum(bytes)/(1024*1024) sga_size
from v$sgastat
where pool is not null
group by pool
/
prompt
prompt
prompt


prompt "Users and Schemas"

select count(username) "No. Users" from dba_users
/
prompt "Users and Objects"
column no_objects format 99999999 heading "No.|Objects"
select owner, object_type, count(object_type) no_objects
from dba_objects
group by owner, object_type
/
prompt
prompt

Prompt "End Of Report

Usual disclaimers apply. I have tested this script under 9.2.0 but if you use it and it causes problems they're your problems. Test it yourself first, there's no warrantees or promises that it won't damage your systems.

It's been suggested that we use Remote Diagnostic Agent (RDA) [link require Metalink login]. I've been looking at RDA but it seems a bit clunky for what we need it for. Where I can see us using it, other than to collect information for Service Requests with Oracle Support, is maybe if we ran it for each database when it's created and each time it's patched or has some other major change (perhaps once every 6 months) and store the resulting .zip file sort of document management system indexed by the database name and date. I think that as a means of documenting the database like that it would be useful but a single report of the 'highlights' such as the script is more useful right now, the information from RDA is rather too lengthy and 'overkill'.