Home » RDBMS Server » Server Utilities » How to distinguish b/w normal trace file and different Sessions enabled SQL trace files?
How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184051] Tue, 25 July 2006 02:17 Go to next message
ghalib
Messages: 8
Registered: July 2006
Location: INDIA
Junior Member

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.

Thanks in Advance.


ASAD

Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184063 is a reply to message #184051] Tue, 25 July 2006 03:06 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try to grep for "alter session set sql_trace = true";

grep -il 'alter session set sql_trace' *.trc


Please don't tell me you are running on Windows. There must be similar utilities available for each and every operating system.
Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184165 is a reply to message #184051] Tue, 25 July 2006 10:24 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
alter session set tracefile_identifier = '__TAGGED__';
Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184289 is a reply to message #184165] Wed, 26 July 2006 01:08 Go to previous messageGo to next message
ghalib
Messages: 8
Registered: July 2006
Location: INDIA
Junior Member
dear I am using Windows Professional Server not having any experience of using unix.

Could you please discuss this related to windows O/S.?

As well as what does these commands actually do?

grep -il 'alter session set sql_trace' *.trc

alter session set tracefile_identifier = '__TAGGED__';

ASad
Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184373 is a reply to message #184289] Wed, 26 July 2006 06:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are looking for someone elses trace file, then you could find the sid & serial# of that particular user from SQL*Plus and use the following:

For example, if a session has sid of 14 and serial# of 39:

findstr /i /c:"SESSION ID:(14.39)" d:\oracle\admin\dbname\udump\*.trc

You could also issue the following select and look for the trace file that has this number in the filename.

select spid
	from v$process p, v$session s
	where s.paddr = p.addr
	and s.username = '<username_of_other_session>';

SPID
----
2422

For example, you would look for file ora9i_ora_2422.trc.

[Updated on: Wed, 26 July 2006 07:06]

Report message to a moderator

Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184791 is a reply to message #184289] Fri, 28 July 2006 01:25 Go to previous messageGo to next message
ghalib
Messages: 8
Registered: July 2006
Location: INDIA
Junior Member
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

Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184793 is a reply to message #184791] Fri, 28 July 2006 01:32 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Use "alter session set tracefile_identifier" as suggested by ebrian in this very same thread.
Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184804 is a reply to message #184791] Fri, 28 July 2006 02:13 Go to previous messageGo to next message
ghalib
Messages: 8
Registered: July 2006
Location: INDIA
Junior Member
You did'nt understand what actually I am trying to say.

Asad
Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #184899 is a reply to message #184804] Fri, 28 July 2006 09:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
ghalib wrote:
>> You did'nt understand what actually I am trying to say
Seems that you are not understanding or even try what others are saying here.
>>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
Nothing by default is available.
Either you search for the session/name manually
or
manually TAG the trace before the users are generating the trace (using alter session set trace file identifier).

Re: How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? [message #185061 is a reply to message #184899] Sun, 30 July 2006 16:47 Go to previous message
ghalib
Messages: 8
Registered: July 2006
Location: INDIA
Junior Member
Someone give his opinion to me that you create a loggon trigger for achieving what u wanted.
If the logon trigger will do what i wanted could some one plz write a logon trigger for me regarding what i am wanting.

Thanks

ASAD
Previous Topic: wwsec_persons$ problem when restoring full dmp of 817 portal
Next Topic: Loading control file in SQL loader
Goto Forum:
  


Current Time: Thu Dec 08 06:03:30 CST 2016

Total time taken to generate the page: 0.15972 seconds