René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

PL/SQL procedure to create an external table to read the alert.log in Oracle

The following procedure creates an external table that can be used to read the alert.log.
The user executing the procedure must have the create any directory privilege to execute external_alert_log. 
create or replace procedure external_alert_log as
  path_bdump varchar2(4000);
  name_alert varchar2(100);
begin

  select 
    value into path_bdump 
  from 
    sys.v_$parameter
  where
    name = 'background_dump_dest';

  select
    'alert_' || value || '.log' into name_alert
  from
    sys.v_$parameter
  where
    name = 'db_name';

  execute immediate 'create or replace directory background_dump_dest_dir as ''' || 
    path_bdump || '''';

  execute immediate 
    'create table alert_log_external '              ||
    ' (line  varchar2(4000) ) '                     ||
    '  organization external '                      ||
    ' (type oracle_loader '                         ||
    '  default directory background_dump_dest_dir ' ||
    '  access parameters ( '                        ||
    '    records delimited by newline '             ||
    '    nobadfile '                                ||
    '    nologfile '                                ||
    '    nodiscardfile '                            ||
    '    fields terminated by ''#$~=ui$X'''         ||
    '    missing field values are null '            ||
    '    (line)  '                                  ||
    '  ) '                                          ||
    '  location (''' || name_alert || ''') )'       ||
    '  reject limit unlimited ';
end;
/
Executing the procedure 
begin
  external_alert_log;
end;
/
Reading the alert log's content: 
select * from alert_log_external;

Errors

ORA-00942 when compiling

If an ORA-00942: table or view does not exist is thrown when the procedure is compiled, it is most likely because the user compiling the procedure does not have the select privilege on v_$parameter.
It should then be granted by sys to the user compiling the procedure: 
SQL> grant select on sys.v_$parameter to rene;

ORA-01031 when executing

If Oracle throws an ORA-01031: insufficient privileges when external_alert_log is called, the user executing this procedure probably lacks the create any directory and/or create table privilege: 
SQL> grant create any directory to rene;
SQL> grant create     table     to rene;

Links

See also On reading trace files with PL/SQL where a trace file is read using a directory.
See also this script to read the alert log which doesn't create a procedure.

Thanks

Thanks to Karl Reitschuster who pointed out a security issue: I used grant create any table instead of grant create table. This is now fixed.