OraFAQ Forum: Performance Tuning » How to get TRACE for a Sql script which is being called from a shell program

Home » RDBMS Server » Performance Tuning » How to get TRACE for a Sql script which is being called from a shell program

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get TRACE for a Sql script which is being called from a shell program [message #239130]

Mon, 21 May 2007 09:37

adroit.ramesh
Messages: 14
Registered: November 2005

Junior Member

Hi Friends,

Can anyone help me in getting TRACE FILE for a Sql script which is being called by a shell program.

I tried with Enable Trace option set.

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239135 is a reply to message #239130]

Mon, 21 May 2007 09:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239142 is a reply to message #239130]

Mon, 21 May 2007 09:56

maggy_ashee
Messages: 70
Registered: May 2007

Member

Hi,

Still its not working.
Even the Trace id is not getting generated.

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239145 is a reply to message #239130]

Mon, 21 May 2007 10:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Still its not working.
Please elaborate on how this conclusion was made.
>Even the Trace id is not getting generated.
What do you mean by "Trace id"?
What OS name & version?
What version of Oracle to 4 decimal places?

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239147 is a reply to message #239142]

Mon, 21 May 2007 10:07

maggy_ashee
Messages: 70
Registered: May 2007

Member

I ran the query in the back end and have run the program.

I mean the oracle_Process_id as Trace id.

OS Is UNIX,and i dont have any idea of how to get the version number.

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239149 is a reply to message #239130]

Mon, 21 May 2007 10:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Has the session/schema doing the ALTER been GRANTed ALTER SESSION priv?
In which folder are you expecting to find the trace file?
> i dont have any idea of how to get the version number.
Do a CUT & PASTE by invoking
sqlplus
-- & logging into Oracle

[Updated on: Mon, 21 May 2007 10:16] by Moderator

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239151 is a reply to message #239130]

Mon, 21 May 2007 10:24

maggy_ashee
Messages: 70
Registered: May 2007

Member

Yes it has the grant privilege.

The Orcale version is 8.1.7.4.0

This is the where I find the trace file.

/apps/oracle/admin/instance/udump/

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239176 is a reply to message #239151]

Mon, 21 May 2007 12:13

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

http://www.dba-oracle.com/t_10046_enable_trace.htm

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239478 is a reply to message #239130]

Tue, 22 May 2007 08:37

maggy_ashee
Messages: 70
Registered: May 2007

Member

No man not able to get anything out of it.

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239498 is a reply to message #239478]

Tue, 22 May 2007 09:09

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Just a test from sqlplus can you do this ?

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

omwb_user@UTV2> set autotrace traceonly

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239509 is a reply to message #239130]

Tue, 22 May 2007 09:37

maggy_ashee
Messages: 70
Registered: May 2007

Member

Yes am able to do this.
set autotrace traceonly

from sqlplus.

My program has been registered in the Front end,which is a shell script and htis shell script in turn calls a sql script.

I tried to set the Enable Trace option and
setting some commands in the 'System->Profiles'

both of them didnt work out.

Do you have any other option.

Report message to a moderator

Re: How to get TRACE for a Sql script which is being called from a shell program [message #239516 is a reply to message #239509]

Tue, 22 May 2007 10:04

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Did a small test, works like a charm for me, hmm

create shell script

[/SSW/oracle/script]vi test.sh
export ORACLE_USER=xxx
export ORACLE_PASSWORD=yyy
export DB_NAME=dw

export CONNSTRING=${ORACLE_USER}/${ORACLE_PASSWORD}@${DB_NAME}
$ORACLE_HOME/bin/sqlplus $CONNSTRING @/SSW/oracle/script/myscript.sql

create sql script

[/SSW/oracle/script]vi myscript.sql
set serveroutput on
alter session set sql_trace=true;
select 'x' from dual;
/
exit

Run script
[/SSW/oracle/script]. test.sh

It produces a trace file.
Try something in this size and see if it's working

Report message to a moderator

Previous Topic:	Need Help
Next Topic:	Query Tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 08:35:46 CST 2024