Why and How to enable a DB system event traces?

  Why ?

In certain cases a further information will be needed in order to get know what it is the details of these issue occur, especially for those cases where Oracle consider it as an information ORA like 28 and 3136. Actually these steps can be used for any type of event traces where event is an ORA error expected to be. This is can be enabled for current connected session or sql it is self or for the whole system.

Note*: alter system will be reset and loosed after DB restart.

How ?

Below is steps to enable traces for ora-28 and ora-3436:

1- As sys user , login to sqlplus:

alter system set events '3136 trace name errorstack level 3';

ALTER SYSTEM SET EVENTS '28 trace name errorstack level 12';

2- To make sure trace is enabled you may use on of the below whether to send all details to trace file or get the details on screen as below:

oradebug setmypid

oradebug eventdump session

oradebug eventdump process

oradebug eventdump system

3- To send details to file :

oradebug setmypid

oradebug UNLIMIT

alter session set tracefile_identifier='myevents';

oradebug eventdump session

oradebug eventdump process

oradebug eventdump system

oradebug tracefile_name