whither user trace file?

whither user trace file?

Post by EdSteven » Thu, 04 Aug 2005 23:50:02


latform: Oracle 9.2 on Solaris

Playing around with Tom Kyte's techniques for setting traceing on
sessions. Everything went as advertised except that I can't find the
trace file. Yes, I know it should be in the udump directory. In
digging around for an answer, I found a comment in a msg in the ng
archives that if connection is by shared server (which it is in this
case) the trace will go to bdump destination. But my trace is not
there either. Here is a session log:

<begin log>
oracle@ncens181: echo $ORACLE_SID
VITXD01
oracle@ncens181: sqlplus system

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 3 08:41:42 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create or replace trigger logon_trigger
2 after logon on database
3 begin
4 if (user = 'orcluser' ) then
5 execute immediate
6 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''ORCLUSER''';
7 execute immediate
8 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER,
LEVEL 12''';
9 end if;
10 end;
11 /

Trigger created.

SQL> show parameter user_dump

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
user_dump_dest string
/u01/app/oracle/admin/VITXD01/
udump
SQL> connect orcluser
Enter password:
Connected.
SQL> select count(*) from all_tables;

COUNT(*)
----------
1090

SQL> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
03-aug-2005 08:44:45

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

oracle@ncens181: pwd
/u01/app/oracle/admin/VITXD01

oracle@ncens181: ls -l
total 76
drwxr-x--- 2 oracle dba 2560 Aug 2 14:59 bdump
drwxr-x--- 3 oracle dba 512 May 3 22:28 cdump
drwxr-x--- 2 oracle dba 512 Dec 28 2004 create
drwxr-x--- 2 oracle dba 11264 Aug 2 21:50 logs
drwxr-x--- 2 oracle dba 512 May 13 15:51 pfile
drwxr-x--- 2 oracle dba 512 Jan 7 2005 scripts
drwxr-x--- 2 oracle dba 512 Nov 6 2004 trace
drwxr-x--- 2 oracle dba 18944 Aug 3 07:09 udump

oracle@ncens181: ls -l bdump
total 2254
-rw-r----- 1 oracle dba 69829 Aug 2 23:03 alert_VITXD01.log
-r--r----- 1 oracle dba 62917 Jul 21 03:42
alert_VITXD01.log.1
-r--r----- 1 oracle dba 119289 Jul 7 00:11
alert_VITXD01.log.2
-r--r----- 1 oracle dba 83447 Jun 20 23:02
alert_VITXD01.log.3
-r--r----- 1 oracle dba 157802 Jun 6 23:03
alert_VITXD01.log.4
-r--r----- 1 oracle dba 103040 May 20 23:02
alert_VITXD01.log.5
-r--r----- 1 oracle dba 87441 May 6 23:03
alert_VITXD01.log.6
-r--r----- 1 oracle dba 98308 Apr 21 02:36
alert_VITXD01.log.7
-r--r----- 1 oracle dba 205076 Apr 6 23:03
alert_VITXD01.log.8
-r--r----- 1 oracle dba 58058 Mar 20 23:02
alert_VITXD01.log.9
-rw-r----- 1 oracle dba
 
 
 

whither user trace file?

Post by Maxim Deme » Thu, 04 Aug 2005 23:56:15

dStevens schrieb:

I suppose , your trigger doesn't fire, try to change your code from
4 if (user = 'orcluser' ) then
to
4 if (user='ORCLUSER') then

Best regards

Maxim

 
 
 

whither user trace file?

Post by EdSteven » Thu, 04 Aug 2005 23:59:37

DOH!

Username in trigger definition was lower case, so of course the
condition was never met. Made it upper case, and condition was met,
but then I got

oracle@ncens181: sqlplus orcluser/orcluser

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 3 08:54:50 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 5


even after graning ORCLUSER 'alter session' and 'execute any
procedure'.

So, what privilege is he still lacking?
 
 
 

whither user trace file?

Post by EdSteven » Fri, 05 Aug 2005 00:07:43

Yep, I was catching that even as you were replying!
 
 
 

whither user trace file?

Post by Maxim Deme » Fri, 05 Aug 2005 00:10:30

EdStevens schrieb:
I'm not the fastest at typing ;-)
For your next error,
try to grant ( as sys , for example or any user with dba role ) 'ALTER
SESSION' to user SYSTEM

Best regards

Maxim
 
 
 

whither user trace file?

Post by EdSteven » Fri, 05 Aug 2005 00:53:18

Hmm. Well, SYSTEM alerady had the DBA roll, which has ALTER SESSION,
but when I granted ALTER SESSION directory to SYSTEM, everything
worked.

More reading on my plate ... ;-)
 
 
 

whither user trace file?

Post by Thomas Kyt » Fri, 05 Aug 2005 23:22:29

In article < XXXX@XXXXX.COM >, EdStevens
says...


see
http://www.yqcomputer.com/ ~tkyte/Misc/RolesAndProcedures.html

for why the owner of the trigger needed the privilege directly.


--
Thomas Kyte
Oracle Public Sector
http://www.yqcomputer.com/
opinions are my own and may not reflect those of Oracle Corporation