/*
Filename    : dbinfo.sql
Author      :
Description : collects info about a database for documentation
*/
set feedback off
set termout off
set pagesize 100
set linesize 75
spool ${backupdir}/dbinfo.${filename}
--
-- *************    NAME/VERSIONS    **************
--
set heading off
set verify off
column today NEW_VALUE p_currdate noprint
select TO_CHAR(SYSDATE,'fmMonth ddth, yyyy') today
from   dual;
clear breaks
clear computes
clear columns
set heading off
column name heading '' justify center format a74
select   'Database Name (SID): ' || name "name"
from     v$database;
prompt
prompt
prompt Version/SGA Information:
set heading off
select * from v$version;
column sgatot justify left format 9,999,999,999,999
select   'Total System Global Area as of &p_currdate.:' hd1,
         sum(value) sgatot, 'bytes' hd2
from     v$sga;

--
-- *************    DATABASE USER INFO    **************
--
prompt
prompt
prompt Database user info:
set heading on
column useranme format a15 heading 'USERNAME'
column password format a30 heading 'PASSWORD'
column  default_tablespace format a19 heading 'DEFAULT_TABLESPACE'
select username,password,default_tablespace from dba_users;
clear columns

--
-- *************    TABLESPACES/DATAFILES    **************
--
prompt
prompt
prompt Tablespaces and Datafiles:
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading 'Tablespace' justify left format a10
column file_id heading 'File|ID' justify center format 999
column file_name heading 'Datafile' justify center format a45 word_wrapped
column size Heading 'Size|in MB.' justify center format 999,990.99
break on tablespace_name skip 1 on report skip 2
compute sum label 'TS SIZE:' of size on tablespace_name
compute sum label 'DB SIZE:' of size on report
select tablespace_name,
       file_id,
       file_name,
       bytes/1024/1024 "size"
from dba_data_files
order by tablespace_name, file_id, file_name;

--
-- *************    SPACE USAGE    **************
--
prompt Space usage (as of &p_currdate.):
prompt

clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading 'Tablespace' justify left format a20
column tbsize heading 'Size|in MB.' justify left format 999,990
column tbused heading 'Used|in MB.' justify right format 999,990
column tbfree heading 'Free|in MB.' justify right format 999,990
column tbusedpct heading '' justify left format a6
column tbfreepct heading '' justify left format a6
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select    t.tablespace_name,
          ROUND(a.bytes) tbsize,
          NVL(ROUND(b.bytes),0) tbused,
          '(' || TO_CHAR(ROUND(100*(NVL(b.bytes,0)/NVL(a.bytes,0))))
            || '%)' tbusedpct,
          NVL(ROUND(c.bytes),0) tbfree,
          '(' || TO_CHAR(ROUND(100*(NVL(c.bytes,0)/NVL(a.bytes,0))))
            || '%)' tbfreepct
from      dba_tablespaces t,
          ( select tablespace_name,
                   sum(bytes)/1024/1024 bytes
             from  dba_data_files
             group by tablespace_name) a,
          ( select e.tablespace_name, sum(e.bytes)/1024/1024 bytes
             from  dba_extents e
             group by e.tablespace_name ) b,
          ( select f.tablespace_name, sum(f.bytes)/1024/1024 bytes
             from  dba_free_space f
             group by f.tablespace_name ) c
where     t.tablespace_name = a.tablespace_name(+) and
          t.tablespace_name = b.tablespace_name(+) and
          t.tablespace_name = c.tablespace_name(+);
--
-- *************    REDO LOG FILES    **************
--
prompt
prompt
prompt Online Redo Logfiles:
clear breaks
clear computes
clear columns
column member heading 'Logfile' justify center format a50 word_wrapped
column group heading 'Group|Number' justify center format 99
column size heading 'Size|in MB.' justify center format 990.99
select f.member "member",
       f.group# "group",
       l.bytes/1024/1024 "size"
from v$logfile f, v$log l
where f.group#=l.group#
order by f.group#,f.member;
--
-- *************    CONTROL FILES    **************
--
prompt
prompt
prompt Control files:
clear breaks
clear computes
clear columns
column name heading 'File Name' format a60 word_wrapped
select name from v$controlfile;
--
-- *************    ROLLBACK SEGMENTS    **************
--
prompt
prompt
prompt Rollback Segments (sizes as of &p_currdate.):
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading 'Tablespace' justify left format a10
column segment_name heading 'Seg|Name' justify center format a7
column status heading 'Status' justify center format a8
column initial_extent heading 'Initial|(in M)' justify center format 99,990.9
column next_extent heading 'Next|(in M)' justify center format 99,990.9
column min_extents heading 'Min|Ext' justify center format 99,990
column max_extents heading 'Max|Ext' justify center format 9999999990
column pct_increase heading 'Pct|Inc' justify center format 990
column rbsize heading 'Curr Size|(in M)' justify left format 999,990
break on tablespace_name skip 1 on report skip 2
select    r.tablespace_name,
          r.segment_name,
          r.status,
          r.initial_extent/1024/1024 "initial_extent",
          r.next_extent/1024/1024 "next_extent",
          r.min_extents,
          r.max_extents,
          r.pct_increase,
          sum(e.bytes)/1024/1024 "rbsize"
from      dba_rollback_segs r, dba_extents e
where     e.segment_name = r.segment_name
group by  r.tablespace_name, r.segment_name, r.status,
          r.initial_extent/1024, r.next_extent/1024,
          r.min_extents, r.max_extents, r.pct_increase;
--
-- *************    PARAMETERS    **************
--
prompt
prompt Parameters (non-defaults):
clear breaks
clear computes
clear columns
column name heading 'Name' format a35 word_wrapped
column pvalue heading 'Value' format a35 word_wrapped
select name, rtrim(value) "pvalue"
from v$parameter
where isdefault = 'FALSE'
order by name;
spool off