Rem Filename   : dbfiles.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.1.x, V9.x
Rem
Rem Modification history
Rem --------------------
Rem 25-May-2000   Initial creation.
Rem 23-mar-2001   Included 8i temporary datafiles.
Rem 03-jul-2001   Removed computation of the sum the size of the redo logs.
Rem 11-feb-2002   Added support for autoextensable data files. 
Rem               Added column "Maximum Size(MB)".
Rem               Suppress the printing of "sum" by using a dummy column.
Rem 21-Apr-2004   Added column "Increment Size(Mb).
Rem               Added database header.
Rem 23-Jun-2004   Fix divide by 0 issue in recently added Increment Size 
Rem               column.
Rem 22-Mar-2005   Corrected formatting issue with "Increment Size" column.
Rem 11-May-2005   Added support for ONLINE/STANDBY redo log files (Data Guard).
Rem 
Rem =============================================================================================================
Rem
Rem Description : Lists all control files, redo files and data files of the database.
Rem
Rem Sample output
Rem -------------
Rem
Rem -------------------------------------------------------------------------------------------------------------
Rem                          Overview of all files including Control Files and Redo Logs
Rem 
Rem                                       of database PDB1 on server deeisep1
Rem -------------------------------------------------------------------------------------------------------------
Rem Current date and time : Wednesday 11-MAY-2005 12:17:40
Rem -------------------------------------------------------------------------------------------------------------
Rem 
Rem Control file name                                            Status
Rem ------------------------------------------------------------ ---------------
Rem /oracle/oradata/PDB1/dbfiles/control01.ctl
Rem /oracle/oradata/PDB1/redo1/control02.ctl
Rem /oracle/oradata/PDB1/redo2/control03.ctl
Rem 
Rem Type     Redo file name                                               Group Size(Mb) Status
Rem -------- ------------------------------------------------------------ ----- -------- ---------------
Rem ONLINE   /oracle/oradata/PDB1/redo1/redo01a.log                           1       20 INACTIVE
Rem          /oracle/oradata/PDB1/redo1/redo02a.log                           2       20 INACTIVE
Rem          /oracle/oradata/PDB1/redo1/redo03a.log                           3       20 CURRENT
Rem          /oracle/oradata/PDB1/redo1/redo04a.log                           4       20 INACTIVE
Rem          /oracle/oradata/PDB1/redo2/redo01b.log                           1       20 INACTIVE
Rem          /oracle/oradata/PDB1/redo2/redo02b.log                           2       20 INACTIVE
Rem          /oracle/oradata/PDB1/redo2/redo03b.log                           3       20 CURRENT
Rem          /oracle/oradata/PDB1/redo2/redo04b.log                           4       20 INACTIVE
Rem 
Rem STANDBY  /oracle/oradata/PDB1/redo1/stbredo11a.log                       11       20 UNASSIGNED
Rem          /oracle/oradata/PDB1/redo1/stbredo12a.log                       12       20 UNASSIGNED
Rem          /oracle/oradata/PDB1/redo1/stbredo13a.log                       13       20 UNASSIGNED
Rem          /oracle/oradata/PDB1/redo1/stbredo14a.log                       14       20 UNASSIGNED
Rem          /oracle/oradata/PDB1/redo2/stbredo11b.log                       11       20 UNASSIGNED
Rem          /oracle/oradata/PDB1/redo2/stbredo12b.log                       12       20 UNASSIGNED
Rem          /oracle/oradata/PDB1/redo2/stbredo13b.log                       13       20 UNASSIGNED
Rem          /oracle/oradata/PDB1/redo2/stbredo14b.log                       14       20 UNASSIGNED
Rem 
Rem 
Rem                                                                            Maximum  Increment
Rem Data file name                                                 Nr Size(Mb) Size(Mb) Size(Mb)  Status
Rem ------------------------------------------------------------ ---- -------- -------- --------- ---------------
Rem /oracle/oradata/PDB1/dbfiles/aq_ts01.dbf                       14       50    2.000       50  AVAILABLE
Rem /oracle/oradata/PDB1/dbfiles/bms_indexes01.dbf                 10       50    2.000       50  AVAILABLE
Rem /oracle/oradata/PDB1/dbfiles/bms_tables01.dbf                  11       50    2.000       50  AVAILABLE
Rem /oracle/oradata/PDB1/dbfiles/core_indexes01.dbf                 5       50    2.000       50  AVAILABLE
Rem ...
Rem /oracle/oradata/PDB1/dbfiles/zsa_indexes01.dbf                  3       50    2.000       50  AVAILABLE
Rem /oracle/oradata/PDB1/dbfiles/zsa_tables01.dbf                   4       50    2.000       50  AVAILABLE
Rem                                                                   --------
Rem                                                                      1.410
Rem 
Rem =============================================================================================================

SET TRIMSPOOL ON
SET FEEDBACK  OFF
SET PAGESIZE  9999
SET LINESIZE 1000
alter session set nls_numeric_characters = ",.";

@@header_title 109 DC 'Overview of all files including Control Files and Redo Logs'


COLUMN CFName          FORMAT A60       HEADING "Control file name"
COLUMN RFName          FORMAT A60       HEADING "Redo file name"
COLUMN DFName          FORMAT A60       HEADING "Data file name"
COLUMN SizeMB          FORMAT 999G999   HEADING "Size(Mb)"
COLUMN LogType         FORMAT A8        HEADING "Type"
COLUMN MaxMB           FORMAT A8        HEADING "Maximum|Size(Mb)"
COLUMN Group           FORMAT 99        HEADING "Group"
COLUMN Status          FORMAT A15       HEADING "Status"
COLUMN FileNr          FORMAT 999       Heading "Nr"
COLUMN IncrementSize   FORMAT A9        Heading "Increment|Size(Mb)"
COLUMN dummy           NOPRINT

SELECT name                  "CFname",
       status                "Status"
  FROM v$controlfile
 ORDER BY name;


BREAK ON "LogType" SKIP 1 NODUP

SELECT lf.type               "LogType",
       lf.member             "RFname",
       lf.group#             "Group",       
       l.bytes/(1024*1024)   "SizeMB",
       l.status              "Status"
  FROM v$logfile lf, 
       ( SELECT group#, bytes, status FROM v$log 
         union all
         SELECT group#, bytes, status  FROM v$standby_log ) l
 WHERE lf.group# = l.group#
 ORDER BY lf.type, lf.member;

CLEAR BREAKS
BREAK ON REPORT
COMPUTE SUM OF SizeMB ON REPORT

SELECT 'a'                   dummy,
       file_name             "DFname",
       file_id               "FileNr",
       bytes/(1024*1024)     "SizeMB",
       decode(maxbytes,0,'',to_char(maxbytes/(1024*1024),'999G999'))  "MaxMB",
       decode(maxblocks,0,'',to_char(increment_by*(maxbytes/maxblocks)/(1024*1024),'999G999')) "IncrementSize",
       status                "Status"
  FROM sys.dba_temp_files
union all
SELECT 'a'                   dummy,
       file_name             "DFname",
       file_id               "FileNr",
       bytes/(1024*1024)     "SizeMB",
       decode(maxbytes,0,'',to_char(maxbytes/(1024*1024),'999G999'))  "MaxMB",
       decode(maxblocks,0,'',to_char(increment_by*(maxbytes/maxblocks)/(1024*1024),'999G999')) "IncrementSize",
       status                "Status"
  FROM sys.dba_data_files
 ORDER BY 2;

SET FEEDBACK ON
CLEAR BREAKS
