Friends, many a times we will be setting events traces in our
databases for various reasons. The below script will give advantage to
know what are all the events turned on in our database
Listing All Events
Most events are numbered in the range 10000 to 10999. To dump all event messages in this range use
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE ‘%Message ‘||err_num||’ not found%’ THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
On Unix systems event messages are in the formatted text file
$ORACLE_HOME/rdbms/mesg/oraus.msg
To print detailed event messages (Unix only) use the following script
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
Listing Enabled Events
To check which events are enabled in the current session
SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line (‘Event ‘||TO_CHAR (l_event)||
‘ is set at level ‘||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/
Listing All Events
Most events are numbered in the range 10000 to 10999. To dump all event messages in this range use
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE ‘%Message ‘||err_num||’ not found%’ THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
On Unix systems event messages are in the formatted text file
$ORACLE_HOME/rdbms/mesg/oraus.msg
To print detailed event messages (Unix only) use the following script
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
Listing Enabled Events
To check which events are enabled in the current session
SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line (‘Event ‘||TO_CHAR (l_event)||
‘ is set at level ‘||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/
No comments:
Post a Comment