Avatar of Geert G
Geert GFlag for Belgium asked on

Can not start trace on oracle 10g

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 ?
Oracle Database

Avatar of undefined
Last Comment
Geert G

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
POracle

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Geert G

than you for your devine intervention and for pointing this out
you have saved my day !

these grants must have gotten revoked somewhere
now to find the place/what/who did this
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23