The DBMS_SYSTEM Package (ksdwrt,set_sql_trace_in_session)

I have found a very useful package. DBMS_SYSTEM which has several functions, but I have choose two of them ksdwrt and SET_SQL_TRACE_IN_SESSION.


I had the following problem: I have configured EM alert on ORA- errors. But EM was not catching error ORA-20018. As you see error code is between [-20000]-[-20999], so it is user-defined error.
EM catches errors that are generated in alert.log. Not all errors are going to alert.log especially user defined errors.

So let’s introduce ksdwrt that helped me to solve this problem.


Used to write messages to the alertlog and/or trace files.

EXEC DBMS_System.ksdwrt(n, message);
EXEC DBMS_System.ksdwrt(2, ‘My Test Alertlog Message’);

Where the value of “n” indicates the destination.

1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.

Anther usefull method:


Used to set trace on or off in another users session.

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, true );
EXEC DBMS_SYSTEM.set_sql_trace_in_session(31, 97, true );

The values for SID and SERIAL# can be found using the V$SESSION view.

End of the story:

I have run:

dbms_system.ksdwrt(2,’ORA-20118: myerror’);
raise_application_error(-20118, ‘myerror’);

And error was placed in alert.log. So EM was able to see it.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: