Locks - identify user creating Query 2
From: oraapps-l@cpa.qc.ca [mailto:oraapps-l@cpa.qc.ca]On Behalf Of Mehul
Zaveri
Sent: Sunday, September 12, 1999 8:59 AM
To: Multiple recipients of list
Subject: Which oracle financials user is locking which tables ?
Hi friends,
Can we find out, which apps users are holding locks on which tables.
(i.e. a link between fnd_% and v$transaction, v$process, v$session etc.)
I am able to find table name from dba_dml_locks, but which apps user is
locking?
Any help/pointer would be greatly appreciated.
Kind regards,
Mehul Zaveri,
Oracle DBA
EPPCO-DUBAI
From: Dinesh Makked dmakked@deltadentalva.com
To: Multiple recipients of list oraapps-l@cpa.qc.ca
Sent: Monday, September 13, 1999 3:56 PM
Subject: RE: Which oracle financials user is locking which tables ?
Try this....
SELECT
OWNER||'.'||OBJECT_NAME "Object",
OS_USER_NAME "Terminal",
ORACLE_USERNAME "Locker",
PROGRAM "Program",
NVL(lockwait,'ACTIVE') "Wait",
DECODE(LOCKED_MODE,
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') "Lockmode",
OBJECT_TYPE "Object Type",
SESSION_ID "Session ID",
SERIAL# "Serial",
c.SID
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
Date: Mon, 13 Sep 1999 18:00:25 +0400
From: "Mehul Zaveri" mehulapps@hotmail.com
To: oraapps-l@cpa.qc.ca
Cc: dmakked@deltadentalva.com
Subject: Re: Which oracle financials user is locking which tables ?
Hai dinesh,
I think there is some misunderstanding.
What I want to know is which apps user (Users created under Oracle
financials) and not 'apps' itself'.
i.e. Which users (created under Oracle financials) are locking which table?
As I understand, this information is stored under foundation tables.
Foundation tables means FND_%.
I want some (or one) of these (FND_%) tables to join with actual
DBA_DML_LOCKS and get the actual oracle financials userid.
Hope now query is more precise.
Mehul Zaveri,
Oracle DBA
EPPCO-DUBAI
Date: Mon, 13 Sep 1999 09:57:30 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
Mehul,
In the same query given below (above), we can add one more line like
"where object_name like 'FND%' "..
We do like this and add this line.
If you want to check the users, check the code behind
"fnd_signon_audit_view"
table and join the ORACLE process is with v$process and from there to
v$session.
You can check the archives. I mentioned this earlier.
Thanks.
-chakrapani
Date: Tue, 14 Sep 1999 12:15:11 -0500
From: "Logan, Ernie" Ernie_Logan@bmc.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
Yes.
Date: Mon, 13 Sep 1999 10:12:14 -0400
From: Mohan Thanikanti mthanika@camail2.harvard.edu
To: oraapps-l@cpa.qc.ca
Subject: Re: Which oracle financials user is locking which tables ?
Hi,
You can use a tool called " TOAD(Tool for Application Developers" for
this info. It is a free-ware available at
www.toadsoft.com.
Once you down load it, you can open the application and look for the Menu
Item ---Tuning.
It gives info for DBAs.
One of them is about the locks.
Thanks,
MOHAN
Date: Mon, 13 Sep 1999 10:06:46 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
You might be mentioning about the code behind the object.
I use TOAD but it does not give locks info..
-chakrapani
Date: Mon, 13 Sep 1999 10:23:15 -0400
From: Mohan Thanikanti mthanika@camail2.harvard.edu
To: oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
Select Tuning
Serverstats
Click on Locks.............You can see the info
MOHAN
Date: Mon, 13 Sep 1999 09:18:34 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
Mohan, TOAD is not giving the FND APPLICATIONS user name. I am using it.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Mon, 13 Sep 1999 09:43:47 -0500
From: "Logan, Ernie" Ernie_Logan@bmc.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
To get to the application user name, you must join to application tables
based on the SID (SESSION_ID) reported in V_$LOCKED_OBJECT.
Try something like this:
set lines 120
SELECT
OWNER||'.'||OBJECT_NAME "Object",
OS_USER_NAME "OS User",
user_name "Apps User" ,
c.PROGRAM "Program",
NVL(lockwait,'ACTIVE') "Wait",
DECODE(LOCKED_MODE,
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') "Lockmode",
OBJECT_TYPE "Object Type",
c.SERIAL# "Serial",
c.SID
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
, v$process p
, fnd_signon_audit_view f
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
and p.addr = c.paddr
and p.pid = f.pid(+)
ORDER BY 1 ASC, 5 Desc
/
Date: Mon, 13 Sep 1999 10:03:10 -0500
From: "Logan, Ernie" Ernie_Logan@bmc.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
OBTW: This does depend on apps auditing being active at some level higher
than "None".
Date: Mon, 13 Sep 1999 19:20:26 +0400
From: "Mehul Zaveri" mehulapps@hotmail.com
To: oraapps-l@cpa.qc.ca
Cc: Ernie_Logan@bmc.com
Subject: Re: Which oracle financials user is locking which tables ?
Thanks to all, who took pains for resolving this issue.
Respected logan,
But still one mystry remains, where to get the same info without being
auditing on?
Or is it not stored till that time anywhere at all? difficult to accept w/o.
discussion.
Once again thanks to all.
Mehul Zaveri,
Oracle DBA
EPPCO-DUBAI
Date: Mon, 13 Sep 1999 11:14:45 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
Mehul,
This is what I mentioned in my earlier mail.
You can get the code related to fnd_signon_audit_view from :
set long 2000
select text from user_views where view_name ='FND_SIGNON_AUDIT_VIEW';
Copy this CODE and add the logic Logan mentioned.
You can run this at any time as a script.
-chakrapani
Date: Mon, 13 Sep 1999 11:29:47 -0500
From: "Logan, Ernie" Ernie_Logan@bmc.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
If you don't tell Oracle Apps to audit logons (at least to the User level),
you cannot determine the "Application User Name" which is holding any
specific lock.
Quoted from an earlier posting by Candace (CFowler@littonapd.com):
"Under SysAdm go to Profile:System. The two system profile options are:
AuditTrail:Activate Values are "No" or "Yes"
Sign-On:Audit Level Values are "None", "User",
"Responsibility" or "Form"
"AuditTrail:Activate" determines if you want to activate the audit trail
facility or not. "Sign-On:Audit Level" determines to what level of detail
you want to audit. "None" has the least (none at all) and "Form" has the
most."
Date: Mon, 13 Sep 1999 09:44:42 -0700
From: Arvind Gidwani agidwani@cisco.com
To: oraapps-l@cpa.qc.ca, Multiple recipients of list oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
All :
There are many ways that you can find out who is locking your object
a) There is a view that you can create by running the script
$ORACLE_HOME/rdbms/admin/utllock.sql
This std oracle provided script creates some good views namely
dba_dml_locks - This view gives all the objects that locked and in
what mode
dba_ddl_locks - Shows locks on procedures/packages and triggers
Another way to find out is
select * from v$lock where request 0
this will exactly pin point who is blocking your transaction
if this query returns any rows and if your session is included in the
list with the request 0 then you are being blocked
Another way is to run $ORACLE_HOME/rdbms/admin/utldtree.sql
This will print a graphical rep of the blockers and waiters
If this does not resolve your issue there is another query
You can query your v$session on the row_file_wait, row_wait_object and
query the v$sessio_wait tables .
If you want a specific query I can send you one
I bet ya , with the above mentioned queries you can nail down who is
locking the object
FYI - Arvind
Date: Mon, 13 Sep 1999 12:42:49 -0500
From: "Logan, Ernie" Ernie_Logan@bmc.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Which oracle financials user is locking which tables ?
None of which will provide the "Oracle Applications" user name. Oracle
Applications logs into the database as the user "APPS" on all sessions. This
makes any pure RDBMS solution to this question essentially useless. Unless
you are using character mode with distinct OS user names, all you will find
out is session ID of who is locking an object.
If your intent is to contact the person locking an object to ask that they
complete their transaction or anything like that, then you have to have to
application user ID, not the RDBMS ID.
Date: Mon, 13 Sep 1999 21:54:39 -0400
From: fushan@gamma.aei.ca
To: oraapps-l@cpa.qc.ca
Subject: Re: Which oracle financials user is locking which tables - Your
real solution for Application
Hi Mehul :
I totally understand what are you asking.. I am working on
Oracle Financial 10.7 in unix. We used to have problem to identify
who locks a table and blocks the rest of the world. From
V$session, we are only able to see a username called "APPS"....
That does not help at all.
I will attached a file with a simple ER diagram.(Not attatched) Base on this
diagram, you or your DBA should be able to find out the User
Name.
Good luck, if you want more explaination, send me email
Fu-shan Louisa Leong
Oracle DBA, Oracle Financial Application
Andersen Consulting
Date: Tue, 14 Sep 1999 05:00:18 -0700 (PDT)
From: Kevin Gillins kgillins@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: Re: Which oracle financials user is locking which tables - Your
real solution for Application
Here is a script that we use to relate an APPS session with the user in
FND_USER table.
+++
set pause off
set verify off
set pagesize 20
column process heading 'PROCESS' format a10
column pid heading 'U-PID' format a6
column os_user heading 'OS ID' format a8
column sid heading 'SID' format 99999
column db_user heading 'DB ID' format a8
column user_name heading 'APPS USER' format a20
column name heading 'NAME' format a20
column phone heading 'PHONE' format a15
select a.username process,
a.spid pid,
b.osuser os_user,
b.sid sid,
b.username db_user,
c.user_name,
c.description name,
c.fax phone
from v$process a,
v$session b,
applsys.fnd_user c
where a.addr = b.paddr and
b.username = c.user_name(+)
;
+++
Use it as you wish.
Date: Tue, 14 Sep 1999 17:32:39 +0400
From: "Mehul Zaveri" mehulapps@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: Re: Which oracle financials user is locking which tables - (looks
closed now)
Dear Kevin,
Your query is working because it seems, you have same oracle userids.
But normally(?) you don't have that mapping. After a nice flowchart
including various other replies, I've created the following query for my
daily use.
I am also planning to configure this query execution once per 30mins. This
will help us in finding locking related problems, which user may not report
at all.
May be our friends can give still better idea, and/or optimize the following
query :-
COLUMN blocking_others FORMAT a3 tru
COLUMN object_name FORMAT a30
COLUMN waiter FORMAT a10
COLUMN locker FORMAT a10
COLUMN owner FORMAT a7
COLUMN user_name FORMAT a15
COLUMN waiterunx FORMAT a9
COLUMN lockerunx FORMAT a9
COLUMN Osid FORMAT 9999
select nmwtng.user_name waiter, nmlkng.user_name locker,
tbl.owner, tbl.name,
tbl.blocking_others, tbl.session_id osid,
nmlkng.spid lockerunx,nmwtng.spid waiterunx
from dba_dml_locks tbl, dba_waiters wtrs,
(select ses.*, usr.user_name, logn.*
from v$session ses, fnd_logins logn, fnd_user usr
where ses.process = logn.spid
and logn.user_id = usr.user_id
) nmwtng,
(select ses.*, usr.user_name, logn.*
from v$session ses, fnd_logins logn, fnd_user usr
where ses.process = logn.spid
and logn.user_id = usr.user_id
) nmlkng
where tbl.session_id = wtrs.holding_session(+)
and wtrs.waiting_session = nmwtng.sid(+)
and tbl.session_id = nmlkng.sid(+)
order by owner, locker;
Date: Tue, 14 Sep 1999 15:59:58 -0700 (PDT)
From: Kevin Gillins kgillins@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: Re: Which oracle financials user is locking which tables - (looks
closed now)
You are correct, I sent in a bad sql query. It will work only if the
oracle id is the same as the apps id. Sorry for the confussion. I
thought this was another script I had. I will look for the one I had
in mind.
Kevin
Date: Tue, 14 Sep 1999 09:38:30 -0700
From: Andy Rivenes arivenes@llnl.gov
To: oraapps-l@cpa.qc.ca
Subject: Re: Which oracle financials user is locking which tables ?
I believe the original question was "Can we find out, which apps users are
holding locks on which tables?" The answer is yes, but in order to do so
you have to use FND_LOGINS, as has been pointed out. The problem with doing
so is that many times the END_TIME field is not updated when a user
disconnects. Having said that, the script fndlock.sql at
www.appsdba.com/apps_sql.htm will do what was asked.
Andy Rivenes
arivenes@llnl.gov
Table Index alter - Sort key too long
Date: Wed, 6 Oct 1999 10:04:21 +0700
From: Subramaniam Venkat venkat@irs.co.id
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: HELP _ Urgent
SQL alter index INV.MTL_TRANSACTION_ACCOUNTS_N1 rebuild
tablespace erp_idx2;
alter index INV.MTL_TRANSACTION_ACCOUNTS_N1 rebuild
tablespace erp_idx2
*
ERROR at line 1:
ORA-01467: sort key too long
Thanks in advance
Regards,
Venkat - DBA
PT INDO-RAMA SYNTHETICS Tbk
Purwakarta-41101 Indonesia
Phone 62-264-202311 EXT 2526
Rollback Tablespace not released
Date: Wed, 06 Oct 1999 14:08:24 GMT
From: "bharathi Yarlagadda" bharathi_d72@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: "Fill Employee Hierarchies" not releasing Rollback Tablespace
We are having problems in running "Fill Employee Hierarchy" conc. req. which
works on 2000 employees and always it errors out with ORA-1650 on ROLLBACK
tablespace.
The table PO_EMPLOYEE_HIERARCHIES_ALL IS HAVING 400000 RECORDS in it.
Hence running a request on this is taking long time and if we run the same
request after completing, it is not releasing the previous
ROLLBACK space.
Our DBAs are also working on this. But any faster reponse would be highly
appreciated.
Can somebody suggest the solution as to how to solve this problem ?
Do I need to increase the no. of rollback segs ?
Do I need to reduce next extent size ?
I dropped and recreated the rollback segments. This also did not solve the
problem.
And we are planning to run this on a daily basis after going LIVE.
Any idea on how to solve this, will be highly appreciated.
I also set autoextend ON for the RBS datafile. But I think this would not
help.
Here is the report on ROLLBACK usage while running the request:
Please click here for the said report
Lock on FND_CONCURRENT_CONFLICT_SETS
Date: Thu, 7 Oct 1999 17:25:14 -0400
From: "Avrami, Louis" louis_avrami@es.adp.com
To: "'oraapps-dba@cpa.qc.ca'" oraapps-dba@cpa.qc.ca,
Subject: AOL: Lock on FND_CONCURRENT_CONFLICT_SETS
Hello all,
We're running Apps 10.7, SmartClient 16.1 environment, RDBMS 7.3.4.4
on an IBM AIX 4.3.2 S70 server. The modules in production are GL, AP, PA,
PO, INV, OE and the self-service web requisitioning
Each month-end there are a lot of report requests, especially FSGs.
During peak periods, when there are a lot of jobs running, we are
experiencing a problem with the FND_CONCURRENT_CONFLICT_SETS table. For
some unknown reason, the table has an exclusive lock that will not release.
Here's what the sql_text looks like, after identifying the database SID that
has the lock:
select b.serial#, a.sql_text
from v$sqltext a, v$session b, v$process c
where b.paddr = c.addr
and a.hash_value = b.sql_hash_value
and b.sid = '&SID';
SID SERIAL# SQL_TEXT
----------------------------------------------------------------
8 4 lock table FND_CONCURRENT_CONFLICT_SETS in exclusive mode
When querying for the username for the SID, all that comes up is
'OraUser'. As SmartClient has been deployed here over the years it's been
rolled out to Citrix servers and individual workstations, and hasn't been
set up properly to identify client/server users.
Has anyone ever experienced a similar problem, or have any insights
to what could be locking the table in exclusive mode? If anyone can over
any information, it would be greatly appreciated.
Lou Avrami