View alert log from sql

In 11g, there is an easy way to read you database's alert log, using table X$DBGALERTEXT:

SELECT ORIGINATING_TIMESTAMP,
         MODULE_ID,
         PROCESS_ID,
         MESSAGE_TEXT
    FROM X$DBGALERTEXT
ORDER BY ORIGINATING_TIMESTAMP DESC;

In pre-11g databases, you have to read it, using another way, like UTL_FILE package.
The following procedure, will put in an ALERT.LOG file a percent [default 5%] of your database's alert log.
If you want all your alert log to be fetched, then set V_PERCENT = 1.

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;