ORACLE 8i HOW TO TRACE SESSION IN SQL PLUS

There are various ways of finding your trace file.

1. By finding the process id in the trace file name:

select
      p.spid
    from
      sys.v_$mystat m,
      sys.v_$session s,
      sys.v_$process p
    where
      m.statistic# = 1 and
      s.sid = m.sid and
      p.addr = s.paddr

look for the selected pid in the trace file name.

2. By adding your own identifier to the trace file name.
Use these steps:
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=TEST;

This will create a trace file in the user dump dest with name like

pid_TEST.trc

That's ok. But how do i Trace a Specific Session with a session ID ?

I use these two lines to generate the syntax for switching on/off tracing in another session (with level 12 it's including the values of bind variables):

select 'exec dbms_system.set_ev('||sid||', '||serial#||',10046,12,'''');' from v$session where lower(username) like lower('%&search4username%');

select 'exec dbms_system.set_ev('||sid||', '||serial#||',10046,0,'''');' from v$session where lower(username) like lower('%&search4username%');

 

the problem is that i have a package body wich has some dynamic SQL nad i can't seem to see the SQL it produces. How can i do it? you script runs well but am i choosing the right username? pelase help!...

To start tracing from in a session that you can't control you could use a logon trigger like this one:

create or replace trigger xxx_trace
  after logon on schema
  declare
lcommand varchar(200);
  begin
   execute immediate 'alter session set max_dump_file_size=100000000';
   execute immediate 'alter session set timed_statistics=TRUE';
   lcommand := 'alter session set events ''10046 trace name context forever, level 12''';
   execute immediate lcommand;
  end;
/

remember to grant the alter session privilege directly to the user that should run this trigger

hope this helps
/\/\icha

where an how do i call this trigger!? what's the usage using SQL + . I've created the trigger but how do i use it!? Tnx

The trace file produces this, i guess the trigger has some errors? what does it  do? does it trace every session? that's cool!!! but i think it has some errors:


Oracle process number: 16

Windows thread id: 1544, image: ORACLE.EXE


*** SESSION ID:(30.24220) 2006-09-12 11:11:35.004
Skipped error 604 during the execution of GIA2.XXX_TRACE
*** 2006-09-12 11:11:35.036
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 7

The trigger will fire whenever you as the owner of the trigger log onto your database - so after you created it in say user xxx and logon to xxx schema again, you should find a new trace-file in the user_dump_dest of your database (a directory that is on the db-server).

Please be aware that the overhead for 10046-traces can bring a productive system down by using excessive cpu- and space-resources!

... are you still there

when i try to execute the web page that invokes the proccedure ... i can't seem to see what sel is done by it.

the .trc files generated by the trigger are like this....i think it's an error of some sort:

Dump file D:\oracle\admin\gia\udump\ORA03384.TRC
Tue Sep 12 11:24:36 2006
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 2000 Version 5.2 , CPU type 586
Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
Windows 2000 Version 5.2 , CPU type 586
Instance name: gia

Redo thread mounted by this instance: 1

Oracle process number: 24

Windows thread id: 3384, image: ORACLE.EXE


*** SESSION ID:(15.34374) 2006-09-12 11:24:36.187
Skipped error 604 during the execution of GIA2.XXX_TRACE
*** 2006-09-12 11:24:36.203
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 7

@xjpmauricio
should work - could it be that you didn't grant the alter session privilege DIRECTLY to your user (via role won't work in PL/SQL)

How do i "grant the alter session privilege DIRECTLY to your user" !?!?!? sorry for so much ignorance...i'm new into Oracle....

SQL> grant alter session to GIA2;

You can do this only if you have a priviliged account (like sys or some other DBA) or got that privilege with the admin option.

...and your ignorance could be my points ;-)

lol

Forced accept.

Computer101
EE Admin