Home > DB_LEVEL > HOW TO ENABLE THE TRACE FOR A USER USING DBMS_MINOTOR.

HOW TO ENABLE THE TRACE FOR A USER USING DBMS_MINOTOR.

February 7, 2014

I have been working as a DBA for last 10 years, but some i how i could not explore much in PT side. Since i have limited exposure on PT side, PT issues are very big for me, but how long i will continue this situation, i vexed with my situation, finally gone through couple of blogs and found many interesting things, only i have to do is just learn one by one gradullay. Offcourse i have started to learn, Now i have been learning new things in oracle PT from different blogs, very interesting i should have done thing little earlier itself but any way something is better. Today i have decided to learn atleast one new topic from differnt blogs and reblog the same. As a part of that i have started from tracing.

My lesson is NOTHING IS TOUGH IF YOU START irrespective of the subject like either it could be maths/computers/science…etc…Just you have to spend some time to understand the problem and react accordingly.

In day to day DBA role, we may need to trace a particular user. This can be acheived in many ways. one of the way is as below using dbms_monitor package.

Steps are as follows.

1) Create a logon database trigger.

create or replace trigger scott_logon_trigger after logon on database
begin
if ora_login_user=’SCOTT’ then
dbms_session.set_identifier(‘scott’);
end if;
end;
/

2) Enable sql*trace for given client identifier

exec dbms_monitor.client_id_trace_enable(client_id=>’scott’,waits=>true, binds=>true);

3) Logon as UKJA user and execute some queries.

4) Disable sql*trace for given client identifier.

exec dbms_monitor.client_id_trace_disable(client_id=>’scott’);

5) Now merge all trace files and generate tkprof.

col value new_value dump_dir
select value from v$parameter where name=’user_dump_dest’;
host trcsess output= clientid=scott &dump_dir/*.trc
host tkprof

6) Review.

you can use the tkprof output

For your easiness, i am here with giving the complete log.

==========================================================================================================

1) Create logon trigger.

SQL> create or replace trigger scott_logon_trigger after logon on database
begin
if ora_login_user=’SCOTT’ then
dbms_session.set_identifier(‘scott’);
end if;
end;
/ 2 3 4 5 6 7

Trigger created.

SQL>

2) Enable the trace,

SQL> exec dbms_monitor.client_id_trace_enable(client_id=>’scott’,waits=>true, binds=>true);

PL/SQL procedure successfully completed.

SQL>

3) Logon as scott and execute some queries.

session1:-

SQL> conn scott/tiger
Connected.
SQL> select count(1) from emp;

COUNT(1)
———-
14

SQL> select count(1) from dept;

COUNT(1)
———-
4

SQL>

session2:-

SQL> conn scott/tiger
Connected.
SQL> select count(1) from dept;

COUNT(1)
———-
4

SQL> select count(1) from emp;

COUNT(1)
———-
14

SQL> select count(1) from tab;

COUNT(1)
———-
5

SQL>

session3:-

SQL> conn scott/tiger
Connected.
SQL> select count(1) from tab;

COUNT(1)
———-
5

SQL> select count(1) from emp;

COUNT(1)
———-
14

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BONUS TABLE
DEPT TABLE
EMP TABLE
MYSAMPLE TABLE
SALGRADE TABLE

SQL> select count(1) from dept;

COUNT(1)
———-
4

SQL> select count(1) from bonus;

COUNT(1)
———-
0

SQL>

4) disable the trace for scott user.

exec dbms_monitor.client_id_trace_disable(client_id=>’scott’);

SQL> exec dbms_monitor.client_id_trace_disable(client_id=>’scott’);

PL/SQL procedure successfully completed.

SQL>

5) Now merge all the trace files into single trace file and generate the tkprof output.

SQL> col value new_value dump_dir
SQL>
SQL> select value from v$parameter where name=’user_dump_dest’;

VALUE
——————————————————————————–
/u01/app/oracle/diag/rdbms/orcl/orcl1/trace

SQL>
SQL>
SQL> host trcsess output=myfinaltrace.trc clientid=scott &dump_dir\*.trc

SQL> host tkprof myfinaltrace.trc mytkprof.out

TKPROF: Release 11.2.0.1.0 – Development on Thu Feb 6 21:09:19 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

SQL>

==========================================================================================================

Advertisements
Categories: DB_LEVEL
%d bloggers like this: