set feedback off
set termout on

set pagesize 100
set linesize 75

spool dbinfo.txt

--
-- *************    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   'Sever Name: '
from     dual;

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

select   'Total System Global Area as of &p_currdate.:' hd1,
         sum(value) sgatot, 'bytes' hd2
from     v$sga;

--
-- *************    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 truncated
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 MG.' 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 truncated
column tbnumber  heading 'Table#' justify left format 99,990
column tbsize heading 'Size|in MG.' justify left format 99,990
column tbused heading 'Used|in MG.' justify right format 99,990
column tbfree heading 'Free|in MG.' justify right format 99,990
column tbusedpct heading '' justify left format a6
column tbfreepct heading '' justify left format a6

break on report
compute sum label 'Totals:' of tbnumber tbsize tbused tbfree on report

select    t.tablespace_name,
          d.num tbnumber,
          ROUND(a.bytes) tbsize,
          NVL(ROUND(b.bytes),0) tbused,
          '(' || TO_CHAR(ROUND(100*(NVL(b.bytes,0)/NVL(a.bytes,0.000001))))
            || '%)' tbusedpct,
          NVL(ROUND(c.bytes),0) tbfree,
          '(' || TO_CHAR(ROUND(100*(NVL(c.bytes,0)/NVL(a.bytes,0.000001))))
            || '%)' 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,
          ( select g.tablespace_name, count(g.table_name) as num
             from  dba_tables g
             where g.table_name not in ('SYSTEM','SYS')
             group by g.tablespace_name ) d
where     t.tablespace_name = a.tablespace_name(+) and
          t.tablespace_name = b.tablespace_name(+) and
          t.tablespace_name = c.tablespace_name(+) and
          t.tablespace_name = d.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 MG.' 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 truncated
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 990.9
column next_extent heading 'Next|(in M)' justify center format 990.9
column min_extents heading 'Min|Ext' justify center format 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 9,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