/*********
* File   : db_info.sql
*
* Author : P.Noonan ( SGA Consulting Ltd )
*
* Date   : JAN-97
*
* Purpose: This script reports on database information.
*
********/

set feedback off
col value format a20 head "DB NAME"
col "Date" format a30

col c1 format a30 head "Block Size" 
col c2 format a30 head "Data Buffer Blocks" 
col c3 format a30 head "Log Archive Buffer Size" 
col c4 format a30 head "Log Archive Buffers" 
col c5 format a30 head "Auto Archive" 
col c6 format a30 head "Archive Dest" 
col c7 format 999.99 head "Log Buffer KB"  
col c8 format 999.99 head "Sort Area Size MB "  
col c9 format 999.99 head "Shared Pool Size MB"

spool /tmp/db_info.log

select value, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Date"
from v$parameter where name='db_name'
/

select sum(bytes) / 1048576 MB
from dba_data_files
/

select value c1
from v$parameter where name='db_block_size'
/

select value c2
from v$parameter where name='db_block_buffers'
/

select value c3
from v$parameter where name='log_archive_buffer_size'
/

select value c4
from v$parameter where name='log_archive_buffers'
/

select value c5
from v$parameter where name='log_archive_start'
/

select value c6
from v$parameter where name='log_archive_dest'
/


select value / 1024 c7
from v$parameter where name='log_buffer'
/

select value / 1048576 c8
from v$parameter where name='sort_area_size'
/

select value / 1048576 c9
from v$parameter where name='shared_pool_size'
/

spool off
!lp /tmp/db_info.log
exit;