-- #############################################################################################
--
-- %Purpose: Show Contents of the Controlfile and Oracle Data Dictionary
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
ttitle  left    'Output generated by: ' sql.user ' at: ' format a8 datum -
left  'Oracle Version ' format a15 sql.release -
skip -
skip -
left  'Datafiles in Data Dictionary (View: dba_data_files)' -
skip 2

set feed off
set pagesize 10000

break on tablespace_name
column datum new_value datum noprint
column tablespace_name format a10 heading "Tablespace"
column file_name format a38 heading "Datafile"
column file_id format 99 heading "File-ID"
column bytes format 99,999,999 heading "Space|[KB]"
column status format a9 heading "Status"

select  to_char(sysdate, 'MM/DD/YY') datum,
  tablespace_name,
  file_name,
  file_id,
  bytes/1000 bytes,
  status
from dba_data_files
order by file_id;

ttitle left 'Datafiles in Controlfile (Table: v$dbfile)' -
skip 2

set feed off
set pagesize 10000
column name format a40 heading "Datafile"
column file# format 99 heading "File-ID"
select  name,file# from v$dbfile;

ttitle left 'Logfiles in Controlfile (Table: v$logfile)' -
skip 2

set feed off
set pagesize 10000
column member format a40 heading "Logfile"
column group# format 99 heading "Group-Nr"
column status format a10 heading "Status"
select  member,group#,status from v$logfile;