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--------
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Thursday, March 5, 2009
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-------------------------------------
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-------------------------------------
Subscribe to:
Posts (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-