SQL Script to Check Oracle Logfile Size, Status and Location

_____________________________________________________________________________________________________________________

This sql script provide you the logfile group, member, path and file size  details for a Oracle database. 

SQL Query

set pagesize 30 linesize 100
column member format a60
column status format a10
column size_MB format '999,999'select group#,thread#,members,status,bytes/1024/1024 size_MB from v$log;
select group#,member from v$logfile order by group#;

advertisements

 

Sample output

SQL> select group#,thread#,members,status,bytes/1024/1024 size_MB from v$log;

    GROUP#    THREAD#	 MEMBERS STATUS      SIZE_MB
---------- ---------- ---------- ---------- --------
	 1	    1	       1 CURRENT	 200
	 2	    1	       1 INACTIVE	 200
	 3	    1	       1 INACTIVE	 200

SQL> select group#,member from v$logfile order by group#;

    GROUP# MEMBER
---------- ------------------------------------------------------------
	 1 /u02/oradata/CDB/redo01.log
	 2 /u02/oradata/CDB/redo02.log
	 3 /u02/oradata/CDB/redo03.log

_____________________________________________________________________________________________________________________