We help IT Professionals succeed at work.
Get Started

Can not start trace on oracle 10g

Geert G
Geert G asked
on
814 Views
Last Modified: 2013-12-18
Hi,

I have a problem starting a trace using a database login trigger on a oracle 10.2.0.3 database

The trigger is this:

CREATE OR REPLACE TRIGGER secu.secutrg_trclogon
AFTER LOGON ON DATABASE
DECLARE
  prg            varchar2(200);
BEGIN
  execute immediate ('alter session set session_cached_cursors = 0');
  BEGIN
    SELECT upper(substr(program, 1, 8))
      INTO prg
      FROM v$session
      WHERE  audsid  = userenv('SESSIONID')
       and rownum < 2;
  EXCEPTION
    when NO_DATA_FOUND then
      null;
  END;
  IF prg = 'BTSNTSVC' THEN
    execute immediate ('alter session set tracefile_identifier = ''ssbizztalk''');
    execute immediate ('alter session set events = ''10046 trace name context forever, level 12''');
  END IF;
END;
/

The trigger throws this error:
Windows thread id: 6736, image: ORACLE.EXE (SHAD)


*** SERVICE NAME:(W06EUPG) 2010-12-03 11:19:09.567
*** SESSION ID:(528.4604) 2010-12-03 11:19:09.567
Skipped error 604 during the execution of SECU.SECUTRG_TRCLOGON
*** 2010-12-03 11:19:09.583
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 19


the error gets thrown on this line :
 execute immediate ('alter session set events = ''10046 trace name context forever, level 12''');

i have now granted dba to this user and still i get the error
logging in with "/ as sysdba" does produce the wanted trace file ...
however i don't want users to login with "/ as sysdba"

anybody any ideas of what grant is missing to allow traces ?
or notes showing bugs with this behaviour from metalink ?
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE