Oracle Alert Log Viewer (11gR2)

Oracle Alert Log Viewer (11gR2)

---

SET SERVEROUTPUT ON SPOOL ALERT.LOG DECLARE    V_PERCENT    NUMBER := .05;    V_BDUMP      VARCHAR2 (1000);    V_NAME       VARCHAR2 (20);    V_FILE       VARCHAR2 (20);    V_LINE       VARCHAR2 (8000);    V_HANDLE     UTL_FILE.FILE_TYPE;    V_DIR_STMT   VARCHAR2 (100);    V_SIZE       INTEGER;    V_BSIZE      INTEGER;    V_EXISTS     BOOLEAN; BEGIN    SELECT VALUE      INTO V_BDUMP      FROM V$PARAMETER     WHERE NAME = 'background_dump_dest';    DBMS_OUTPUT.PUT_LINE (       '**************************** INFO *************************');    DBMS_OUTPUT.PUT_LINE ('BDUMP: ' || V_BDUMP);    SELECT VALUE      INTO V_NAME      FROM V$PARAMETER     WHERE NAME = 'instance_name';    DBMS_OUTPUT.PUT_LINE ('DB NAME: ' || V_NAME);    V_FILE := 'alert_' || V_NAME || '.log';    DBMS_OUTPUT.PUT_LINE ('FILENAME: ' || V_FILE);    V_DIR_STMT := 'CREATE DIRECTORY ALERT AS ''' || V_BDUMP || '''';    DBMS_OUTPUT.PUT_LINE ('COMMAND: ' || V_DIR_STMT);    EXECUTE IMMEDIATE V_DIR_STMT;    V_HANDLE := UTL_FILE.FOPEN ('ALERT', V_FILE, 'r');    UTL_FILE.FGETATTR ('ALERT',                       V_FILE,                       V_EXISTS,                       V_SIZE,                       V_BSIZE);    DBMS_OUTPUT.PUT_LINE ('ALERT SIZE: ' || V_SIZE / 1024 / 1024 || ' MB');    UTL_FILE.FSEEK (V_HANDLE, NULL, V_SIZE * (1 - V_PERCENT));    DBMS_OUTPUT.PUT_LINE (       'FETCHED ALERT SIZE: ' || V_SIZE * (V_PERCENT / 1024 / 1024) || ' MB');    DBMS_OUTPUT.PUT_LINE (       '********************************************************');    LOOP       BEGIN          UTL_FILE.GET_LINE (V_HANDLE, V_LINE);          DBMS_OUTPUT.PUT_LINE (V_LINE);       EXCEPTION          WHEN NO_DATA_FOUND          THEN             EXIT;       END;    END LOOP;    UTL_FILE.FCLOSE (V_HANDLE);    EXECUTE IMMEDIATE 'DROP DIRECTORY ALERT'; END; / SPOOL OFF; SELECT ORIGINATING_TIMESTAMP,          MODULE_ID,          PROCESS_ID,          MESSAGE_TEXT     FROM X$DBGALERTEXT ORDER BY ORIGINATING_TIMESTAMP DESC;