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;





블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,