1SELECT l.THREAD#, lf.GROUP#, lf.MEMBER,
2 trunc(l.BYTES / 1e6) AS SIZE_MB,
3 l.STATUS, l.ARCHIVED, lf.TYPE,
4 lf.IS_RECOVERY_DEST_FILE AS RDF,
5 l.SEQUENCE#, l.FIRST_CHANGE#,
6 l.NEXT_CHANGE#
7FROM V$LOGFILE lf JOIN V$LOG l ON l.GROUP# = lf.GROUP#
8ORDER BY l.THREAD#, lf.GROUP#, lf.MEMBER;
1CREATE OR REPLACE
2PROCEDURE "WRITELOG" (LOGLEVEL IN VARCHAR2, LOGCONTEXT IN VARCHAR2, LOGMESSAGE IN VARCHAR2)
3AUTHID CURRENT_USER
4AS
5 F1 UTL_FILE.FILE_TYPE;
6 PRAGMA AUTONOMOUS_TRANSACTION;
7 LOG_DIR VARCHAR2;
8 LOG_FILENAME VARCHAR2;
9BEGIN
10 LOG_DIR := 'MYLOG_DIR';
11 LOG_FILENAME := 'mylog';
12 F1 := UTL_FILE.FOPEN(LOG_DIR, TO_CHAR(SYSDATE,'YYYY-MM-DD') || '_' || LOG_FILENAME ||'.log','a');
13 UTL_FILE.PUT_LINE(F1, TO_CHAR(SYSDATE,'DD-MM-YYYY HH:MI:SS AM') || ' - [' || LOGLEVEL || '] ' || LOGCONTEXT || ': ' || LOGMESSAGE);
14 UTL_FILE.FCLOSE(F1);
15
16EXCEPTION
17 WHEN OTHERS THEN
18 DBMS_OUTPUT.PUT_LINE('ERROR: ' || TO_CHAR(SQLCODE) || SQLERRM);
19 IF UTL_FILE.IS_OPEN(F1) THEN
20 UTL_FILE.FCLOSE(F1);
21 END IF;
22END;
23
24