show alert log contents with external table

show alert log contents with external table

1. Run the procedure with system user or (any privilige user)


DECLARE
BDumpDir VARCHAR2(200);
SID VARCHAR2(16);
ObjectExists EXCEPTION;
PRAGMA EXCEPTION_INIT(ObjectExists,-955);
BEGIN
------ get the bdump dir
SELECT value
INTO BDumpDir
FROM v$parameter
WHERE name='background_dump_dest';

----- create the directory for the bdump dir
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||
BDumpDir||'''';
----- grant the necessary privileges
EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';
------ get the SID
SELECT instance_name INTO SID FROM v$instance;
----- create the external table
EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXTERNAL
(TEXT VARCHAR2(255)
) ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY BDUMP_DIR
ACCESS PARAMETERS
(records delimited by newline
nobadfile
nologfile
)
LOCATION (''alert_'||SID||'.log'')
)
REJECT LIMIT UNLIMITED'
;
------ ignore ORA-955 errors (object already exists)
EXCEPTION WHEN ObjectExists THEN NULL;
END;
/


2. Now we can query the external table to read the alert log contents.


a. show from last but in ascending order (this is nice and real)
----------------------------------------------------------------

select text from(
select rownum s,text from (
SELECT rownum,text FROM system.alert_log_external
order by rownum desc
)
where rownum<200
)
order by s desc


b. show from last
-------------------

select rownum,text from (
SELECT * FROM system.alert_log_external
order by rownum desc
)


c. show with like condition
---------------------------

select rownum,text from (
SELECT * FROM system.alert_log_external
order by rownum desc
)
where upper(text) like upper('%ERROR%')

---------------end--------

Trace the object's/ query's execution Path

For sql / procedure / function trace (more effective way to tuning the execution path)

1. for seeing the trace file path , execute the following query

SELECT s.sid, s.serial#, pa.value '\' LOWER(SYS_CONTEXT('userenv','instance_name')) '_ora_' p.spid '.trc' AS trace_fileFROM v$session s, v$process p, v$parameter paWHERE pa.name = 'user_dump_dest'AND s.paddr = p.addrAND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

2. for trace file identifier(optional)

alter session set tracefile_identifier = 'LONG_RUNNING_QUERY';

3. for anable the session lebel trace file generate

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

4. execute your tuning able objects or query

5.execute the following command for better readable format

$ tkprof trc_file_name Output_file_name.txt

example:-
$ tkprof F:\ORACLE\product\10.2.0 \admin\DBA\udump\dba_ora_3920.trc c:\transte.txt

6. Turn off the Trace

ALTER SESSION SET EVENTS '10046 trace name context off';

----------end-------------------------------------