Oracle Alter log Viewer 조회 스크립트

Oracle Alter log Viewer 조회 스크립트

---

Oracle 11g 부터는 물리적 경로에 있는 alert log를 아래와 같은 절차를 통하여 SQL문으로 확인해 볼수 있다.

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;