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