Home > Oracle > using dbms_monitor for tracing

using dbms_monitor for tracing

  • Trace the entire database

EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE;

  • To disable trace for the database

EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE;

  • To enable trace for instance RAC1 use:

EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE (INSTANCE_NAME => 'RAC1');

  • To disable trace for instance RAC1 use

EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE (INSTANCE_NAME => 'RAC1');

Trace at session level


Enable tracing 

dbms_monitor.session_trace_enable(
session_id => 127,
serial_num => 29,
waits => TRUE,
binds => FALSE);

Disable Tracing 

dbms_monitor.session_trace_disable(session_id => 127,serial_num => 29);

  • Trace using client identifiers , useful when many sessions connect using the same user

-- set client identifier using DBMS_SESSION.SET_IDENTIFIER

BEGIN
  DBMS_SESSION.SET_IDENTIFIER ('CLIENTJ10');
END;

-- OR use a database logon trigger

CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_user_identifier varchar2(64);
BEGIN
SELECT SYS_CONTEXT('USERENV', 'OS_USER')  
       ||':'||
       SYS_CONTEXT('USERENV', 'IP_ADDRESS')
  INTO v_user_identifier
  FROM dual;
DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;
/
  • Trace CLIENT_IDENTIFIER or  MODULE_NAME

-- to find out connected sessions

SELECT sid, serial#,
client_identifier, service_name, action, module
FROM V$SESSION;

-- To trace CLIENT_IDENTIFIER, MODULE_NAME, or ACTION_NAME

-- Enable tracing for CLIENT_IDENTIFIER mdesouza:10.210.1.170 with wait information but no bind information

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(mdesouza:10.210.1.170',TRUE,FALSE);

-- Enable tracing for service_name TESTORAC but only for the TOAD 9.6.1.1 module and include wait and bind information.

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('TESTORAC','TOAD 9.6.1.1', DBMS_MONITOR.ALL_ACTIONS,TRUE,TRUE,NULL);

-- Enable tracing for session id 61 and include wait and bind information

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(61,8012, TRUE, TRUE);

-- Trace my current session

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL,NULL, TRUE, TRUE);

-- to view your enabled traces

SQL> select * from DBA_ENABLED_TRACES ;

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: