Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    4

    Unanswered: How to distinguish b/w normal trace file and different Sessions enabled SQL trace fil

    Is there any way to differentiate between normal trace files and
    different Session's SQL enabled trace files which generate in udump folder on Operating System?
    I want to have a script which distinguishes between different
    Session's SQL enable trace files and normal trace files.
    So we can know that these files are generated due to enabling sql trace utility. Suppose more than one users enable sql tracing thier sessions, how we find out thier tracefiles.
    Windows Professional is the server.

    Thanks in Advance.


    ASAD

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Check this page; as the name of the trace file is based on the current value of the user_dump_dest, the session's process id and the instance name, I'm not sure you'll be completely satisfied with the solution, but script the author suggested enables you to retrieve created trace file name at the SQL*Plus prompt (using identify_trace_file.sql).

  3. #3
    Join Date
    Jul 2006
    Posts
    4
    Suppose more than two users generate their tracefiles through SQL Trace utility on The Operating system (windows) at the same time, than How would we tag their trace files through a method as you said. How would we find their trace files among others?

    Thanks in Advance

    Kq

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    If you code this in the application, and you're running in DEDICATED server mode, you could use

    Code:
    alter session set tracefile_identifier = 'somthing distinct';
    I don't know what's possible when you're using DBMS_SESSION or DBMS_MONITOR, though.

    -Chuck

  5. #5
    Join Date
    Jul 2006
    Posts
    4
    As I discussed, Suppose two persons perform sql tracing for their sessions.
    Is there a automize way to getting know that these two trace files are generated due to invoke SQL Tracing and then tag their tracefiles.
    Obviously these two persons issue the Alter Session command for enabling SQL Trace theirselves.

    Is thier a routine or something like that available that instead of these two users who perform their session trace, anautomize routine will tag their trace files to a particular name.So whenever I look at the userdump folder I can distinguish these files with each other.

    Asad

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I dunno, try something like:

    Code:
    CREATE OR REPLACE PROCEDURE START_TRACE AS
      USRNM VARCHAR2(30) := USER;
      CURTIME VARCHAR2(30) := TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS');
      ALTER_STMT VARCHAR2(100);
    BEGIN
      ALTER_STMT := 'alter session set tracefile_identifier = ''' || USRNM || CURTIME ||'''';
      EXECUTE IMMEDIATE ALTER_STMT;
      ...start the trace, etc...
    END;
    -cf

  7. #7
    Join Date
    Jul 2006
    Posts
    4
    Hi,

    Could you please complete this logon trigger code to tag the name to tracefiles?

    Thanks

    Asad

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Here's an example with the AFTER LOGON trigger.
    Code:
    /usr/users/oracle> sqlplus '/ as sysdba'
    
    SQL*Plus: Release 9.2.0.1.0 - Production on Mon Aug 14 10:42:40 2006
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production
    
    SQL>
    SQL>
    SQL> create or replace trigger trace_identifier
      2     after logon on database
      3  begin
      4     execute immediate 'alter session set tracefile_identifier = ' || user;
      5  end;
      6  /
    
    Trigger created.
    
    SQL> conn a/a
    Connected.
    SQL> show parameter tracefile_identifier
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    tracefile_identifier                 string      A
    SQL> show parameter user_dump
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    user_dump_dest                       string      /oracle/app_ora/oracle/admin/a
                                                     tm1/udump
    SQL> alter session set sql_trace = true;
    
    Session altered.
    
    SQL> !ls -lt /oracle/app_ora/oracle/admin/atm1/udump | head -2
    total 716
    -rw-r-----   1 oracle   dba          922 Aug 14 10:44 atm1_ora_486209_A.trc
    
    SQL>
    This will work for users with non-dbas priviledges.

  9. #9
    Join Date
    May 2006
    Posts
    132
    This can come in handy...thanks JMartinez !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •