How to show currently active events

Found a helpful PL/SQL block in this presentation:

http://www.sagelogix.com/idc/groups/public/documents/sagelogix-presentation/sage016048.pdf

This shows you which events are set – i.e. by an alter system set events command.  I modified this to have a larger range of event values – to 29999.

declare
event_level number;
begin
for i in 10000..29999 loop
sys.dbms_system.read_ev(i,event_level);
-- note: requires exec permission for DBMS_SYSTEM
if (event_level > 0) then
dbms_output.put_line
('Event '||i||' set at level '|| event_level);
end if;
end loop;
end;
/

This shows the events that were set dynamically as well as any set in the init parameter event.

Run:

alter system set events
'10503 trace name context forever, level 4000';

Then run the PL/SQL block and you get this:

Event 10503 set at level 4000

Also, Oracle note 160178.1 has a good explanation of setting events dynamically and in an spfile.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply