Thursday, March 5, 2009

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-------------------------------------