Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: pay_balance_values_v (ORACLE APPS HRMS) problem.

RE: pay_balance_values_v (ORACLE APPS HRMS) problem.

From: Nahata, Naveen (US - Hyderabad) <>
Date: Tue, 31 May 2005 08:35:16 -0500
Message-Id: <>


In Oracle Applications, it is very easy to set the trace, especially when the users are using forms.

There are 2 ways of setting the trace in such a case:

  1. If you have access to the user, sit with him and just before he queries navigate to Help -> diagnostics -> Trace and select "Trace with binds and waits". Provide the apps password when prompted.
  2. Get the DB session information from Help -> About Oracle applications. You will see fields named version_database_process (spid), Server SID (sid), Serial#, AUDSID etc. Once you have that information you can set the trace using ORADEBUG, DBMS_SYSTEM.SET_EV or by using DBMS_SUPPORT. I recommend using ORADEBUG because every form the user opens will have a different SID, and hence you will lose the trace.

Once you get the trace file, you can format it using tkprof and analyze the problem.

If the session is a self service session, things get a bit more tricky. You will have to enable the trace by setting the profile "Initialization SQL Statement%Custom" at the user level and ask him to launch a new session. This will trace the entire length of the session


-----Original Message-----
[] On Behalf Of Niall Litchfield Sent: Tuesday, May 31, 2005 1:20 PM
Cc: Oracle-L Freelists
Subject: Re: pay_balance_values_v (ORACLE APPS HRMS) problem.

We have Windows server with 2 cpu and 4 gb of RAM. However, when I run
> a simple SELECT * FROM PAY_BALANCE_VALUES_V it is taking end-less
> time. I can't even trace this as we can't change any sql which is
> provided by oracle.

I'm sceptical of this last point, Oracle provides a utility (trace analyzer)=20
that comes straight from the apps dev team for analysing trace files. if you=20
can't trace apps why would they have written the tool? My guess is that=20 there is an apps specific way to enable and disable database tracing and you=20
should look for that.=20

Niall Litchfield
Oracle DBA


This message (including any attachments) contains confidential =
information intended for a specific individual and purpose, and is =
protected by law.  If you are not the intended recipient, you should =
delete this message.  Any disclosure, copying, or distribution of this =
message, or the taking of any action based on it, is strictly =
Received on Tue May 31 2005 - 09:40:11 CDT

Original text of this message