set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 120
set pagesize 120

set termout off
set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT
PROMPT ###################################################################### 
PROMPT 
PROMPT --  Name         : listDatabaseOverviewInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description  :This script lists the high level information 
PROMPT --                for the database to which you are connected
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named databaseOverviewInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column value          format a50 wrap
column name           format a50 wrap
column database_name  format a30 wrap
column member         format a50 wrap
column parallel       format a8       heading "Parallel"
column host_name      format a20      heading "Host Name"  wrap
column banner                         heading "Database Component Versions"
column sganame                        heading "SGA Name"
column sgavalue                       heading "SGA Value|(Bytes)"
column parameter                      heading 'Option Name'
column inst           format a20      heading "Installed"  word_wrap
column startup_time   format a21      heading "Startup Date and Time"

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name from v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today from dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on
set feedback off

set head on

spool databaseOverviewInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "Database Information for "  &s_dbname " on " &s_machine skip 2

select name database_name,
       created,
       log_mode 
from v$database;

ttitle off

select instance_name, 
       version, 
       host_name, 
       to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') startup_time,
       parallel 
from v$instance;

select banner 
from v$version;

select parameter,
       value inst 
from v$option;

ttitle left &s_curDate -
       center "SGA Information for Database "  &s_dbname " on " &s_machine skip 2

break on report
compute sum label "Total SGA Value" of sgavalue on report

select name sganame,
       value sgavalue 
from v$sga;

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no database information was found for the database &s_dbname
PROMPT
PROMPT
PROMPT Output file databaseOverviewInfo_&s_dbname._&s_machine..LST produced
PROMPT

undefine u_name
undefine extentsMax

@sqlplus_settings