Oracle Database System Triggers

System event list that could to be triggered:
AFTER STARTUP
BEFORE SHUTDOWN
AFTER LOGON
BEFORE LOGOFF
AFTER DB_ROLE_CHANGE -- for Data Guard failover and switchover
AFTER SUSPEND
AFTER SERVERERROR

AFTER SERVERERROR triggers fire after an Oracle error is raised, unless the error is one of
the following:
ORA-00600
Oracle internal error
ORA-01034
Oracle not available
ORA-01403
No data found
ORA-01422
Exact fetch returns more than requested number of rows
ORA-01423
Error encountered while checking for extra rows in an exact fetch
ORA-04030
Out-of-process memory when trying to allocate N bytes
In addition, the AFTER SERVERERROR trigger will not fire when an exception is raised
inside this trigger (to avoid an infinite recursive execution of the trigger).

Trace you session

Trace you session

1. Regular
Trace your own session
alter session set sql_trace=true;
or
alter session set events '10046 trace name context forever, level 4';
-- levels: 1(sql_trace=TRUE), 4, 8, 12

1.1. Regular 1
Trace your own session
exec dbms_session.set_sql_trace(TRUE);

2. Distance 1
dbms_system.set_bool_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
bval IN BOOLEAN);
example:
exec dbms_system.set_bool_param_in_session(10, 161, 'sql_trace', TRUE/FALSE);

3. Distance 2
dbms_system.set_sql_trace_in_session(
sid
NUMBER,
serial# NUMBER,
sql_trace BOOLEAN);
example
exec dbms_system.set_sql_trace_in_session(10, 1008, TRUE/FALSE);
or
EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);
EXEC DBMS_System.Set_Ev(31, 97, 10046, 4, '');

4. Distance 3 (new 11g)
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits
IN BOOLEAN
DEFAULT TRUE,
binds
IN BOOLEAN
DEFAULT FALSE,
plan_stat IN VARCHAR2
DEFAULT NULL);

Write your own message into alert log

Run under SYS
begin
  dbms_system.ksdwrt(2, 'Message from DB');
end;

Original
dbms_system.ksdwrt(n, message_string);
where n is:
1 - Write to trace file.
2 - Write to alertlog.
3 - Write to both.