>ORACLE Trace and Dump file Alert sql

>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;
/
———————————————————————
begin
external_alert_log;
end;
/
———————————————————————
select * from alert_log_external;

http://www.adp-gmbh.ch/ora/admin/read_alert/index.html

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s