-- alert_log_external_table.sql
-- requires oracle 9i+
-- jared still - jkstill@cybcon.com
-- create a directory and an external table so
-- that the alert.log may be queried via SQL
-- 
-- note the no* parameters in access parameters
-- without those there will be *.bad, *.dis and *.log
-- files created by loader in the bdump directory
-- probably not what you wanted.
-- these could be specified with explicit locations
-- if you need the files.
-- 
-- probably best to run this as SYSTEM, and that is
-- indeed that way this script is setup to work

col v_alert_log new_value v_alert_log noprint
col v_bdump new_value v_bdump noprint
col v_udump new_value v_udump noprint

select value v_bdump
from v$parameter
where name = 'background_dump_dest'
/

select value v_udump
from v$parameter
where name = 'user_dump_dest'
/

select 'alert_' || value || '.log' v_alert_log
from v$parameter
where name = 'instance_name'
/

create or replace directory bdump as '&&v_bdump';
create or replace directory udump as '&&v_udump';

drop table alert_log;

create table alert_log ( text varchar2(400) )
organization external (
	type oracle_loader
	default directory BDUMP
	access parameters (
		records delimited by newline
		nobadfile
		nodiscardfile
		nologfile
	)
	location('&&v_alert_log')
)
reject limit unlimited
/

drop public synonym alert_log;
create public synonym alert_log for system.alert_log;
grant select on alert_log to DBA;
grant read on directory bdump to dba;
grant read on directory udump to dba;