Get Oracle Database basic information

1. 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; 

  2. Tablespace/ Data files

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 9999

column file_name heading 'Datafile' justify center format a60 word_wrapped

column size Heading 'Size|in MB.' justify center format 9,99990.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; 

3. Space Usage

prompt Space usage (as of &p_currdate.):

prompt 

clear breaks

clear computes

clear columns 

set heading on

column tspace heading 'Tablespace' justify left format a20 truncated

column tot_ts_size heading 'Size|in MB.' justify left format 9,99999999990

column free_ts_size heading 'Used|in MB.' justify right format 9,9999999990

column tbusedpct heading '' justify left format a6

column tbfreepct heading '' justify left format a6 

break on report

compute sum label 'Totals:' of tot_ts_size free_ts_size on report 

col tspace form a25 Heading "Tablespace"

col tot_ts_size form 99999999999999 Heading "Size (Mb)"

col free_ts_size form 99999999999999 Heading "Free (Mb)"

col ts_pct form 999 Heading "% Free"

col ts_pct1 form 999 Heading "% Used"

SELECT df.tablespace_name tspace,

       df.bytes/(1024*1024) tot_ts_size,

       sum(fs.bytes)/(1024*1024) free_ts_size,

       round(sum(fs.bytes)*100/df.bytes) ts_pct,

       round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1

FROM dba_free_space fs,

     (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df

WHERE fs.tablespace_name = df.tablespace_name

GROUP BY df.tablespace_name, df.bytes;

4. 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 9999

column size heading 'Size|in MB.' justify center format 999990.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;

5. 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; 

6. 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 99990.9

column next_extent heading 'Next|(in M)' justify center format 99990.9

column min_extents heading 'Min|Ext' justify center format 99990

column max_extents heading 'Max|Ext' justify center format 999999999990

column pct_increase heading 'Pct|Inc' justify center format 99990

column rbsize heading 'Curr Size|(in M)' justify left format 9,99990 

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;

7. 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 order by name; 

8. User Details

prompt

prompt user details : 

clear breaks

clear computes

clear columns 

select username,profile,default_tablespace,temporary_tablespace from dba_users;

select * from nls_database_parameters;

select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;

SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;

spool off