REM REM Filename : dbinfo.sql
set feedback off
set termout on
set pagesize 100
set linesize 132
ttitle off
spool dbinfo.wri
--
-- ************* 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
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 a15
column file_id heading 'File|ID' justify center format 999
column file_name heading 'Datafile' justify center format a60 word_wrapped
column size Heading 'Size|in MG.' justify center format 9,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 tbsize heading 'Size|in MG.' justify left format 9,990
column tbused heading 'Used|in MG.' justify right format 9,990
column tbfree heading 'Free|in MG.' justify right format 9,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 a60 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 a15 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 a50 word_wrapped
select name, rtrim(value) "pvalue" from v$parameter where isdefault = 'FALSE' order by name;
spool off