set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 132
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         : listDatabaseParametersInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description  :This script will output parameter values 
PROMPT                   (not including hidden ones)
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named databaseParametersInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column param_name        format a40 heading "Parameter Name"
column param_value       format a38 heading "Parameter Value" word_wrap
column isdefault         format a7  heading "Default|Value"
column ismod                        heading "Is|Modified"
column isses_modifiable  format a10 heading "Session|Modifiable"
column issys_modifiable  format a10 heading "System|Modifiable"

-- 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 databaseParametersInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "List of Database Parameters in Instance "  &s_dbname " on " &s_machine skip 2

select name param_name, 
       value param_value, 
       isdefault,
       ismodified ismod, 
       isses_modifiable,
       issys_modifiable
from v$parameter
order by param_name;

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 parameter information was found for the database &s_dbname
PROMPT
PROMPT
PROMPT Output file databaseParametersInfo_&s_dbname._&s_machine..LST produced
PROMPT

@sqlplus_settings