Home » RDBMS Server » Performance Tuning » How to get TRACE for a Sql script which is being called from a shell program
How to get TRACE for a Sql script which is being called from a shell program [message #239130] Mon, 21 May 2007 09:37 Go to next message
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
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 Go to previous messageGo to next message
maggy_ashee
Messages: 70
Registered: May 2007
Member
Hi,

Still its not working.
Even the Trace id is not getting generated.
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 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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?
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 Go to previous messageGo to next message
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.

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 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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 Go to previous messageGo to next message
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/
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 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
http://www.dba-oracle.com/t_10046_enable_trace.htm
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 Go to previous messageGo to next message
maggy_ashee
Messages: 70
Registered: May 2007
Member
No man not able to get anything out of it.
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
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.
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 Go to previous message
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


Previous Topic: Need Help
Next Topic: Query Tuning
Goto Forum:
  


Current Time: Fri Dec 02 18:24:56 CST 2016

Total time taken to generate the page: 0.41749 seconds