set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 100
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

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

column value       format a50  heading "Archive Log Destination"
column ctrl_name   format a50  heading "Controlfile Name"
column dtfl_name   format a50  heading "Datafile Name"
column member      format a50  heading "Logfile Member"

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

ttitle left &s_curDate -
       center "Locations of Control Files for Database "  &s_dbname " on " &s_machine skip 2

select name ctrl_name
from v$controlfile
order by name;

ttitle left &s_curDate -
       center "Locations of Data Files for Database "  &s_dbname " on " &s_machine skip 2

select name dtfl_name
from v$datafile
order by name;

ttitle left &s_curDate -
       center "Locations of Redo Log Files for Database "  &s_dbname " on " &s_machine skip 2

select member      
from v$logfile
order by member;

ttitle left &s_curDate -
       center "Locations of Archive Log Files for Database "  &s_dbname " on " &s_machine skip 2

select value 
from v$parameter
where name = 'log_archive_dest'
union
select value 
from v$parameter
where name like 'log_archive_dest\__' escape '\'
order by value;

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

@sqlplus_settings