| Seeing active locks & sessions (translating from Informix) (merged 3) [message #428192] |
Tue, 27 October 2009 08:06  |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Hi people,
I need to translate the next Informix query:
SELECT sid, username, uid, pid, hostname FROM sysmaster:syssessions
WHERE sid = (SELECT distinct(owner)
FROM sysmaster:syslocks
WHERE tabname = "TAB_NAME"
and rowidlk = (SELECT rowid from TAB_NAME WHERE KEY_FIELD = 1)
)
I found something similar to 'syssessions':
select SID, USERNAME, USER#, 0 PID, MACHINE
from sys.v_$session
But I don't know where find the locks info...
The sub-select "(SELECT rowid from TAB_NAME WHERE KEY_FIELD = 1)" always return only one row.
I don't know how to translate this 'rowid' use to Oracle.
Some kind of help?
Thanks in advance.
Best regards,
Donato.
[Updated on: Tue, 27 October 2009 08:20] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Seeing active locks & sessions (translating from Informix) (merged 3) [message #428252 is a reply to message #428192] |
Tue, 27 October 2009 09:55  |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Hi,
At first, a cursor read from "TAB_NAME" with the option WITH HOLD (Informix) <-> FOR UPDATE (Oracle), locking the rows that it are reading from.
Then, with the query I'm trying to translate, I want to check that the Locking is being done correctly. In other words, I'm trying to check that the cursor have worked fine.
But I'm not translating only a query, but a whole program. I can't change the program functionality, i need a similar query or, rather, the same results.
I hope having explained!
Thanks a lot!
|
|
|
|
|
|
| Re: Seeing active locks & sessions (translating from Informix) (merged 3) [message #428461 is a reply to message #428192] |
Wed, 28 October 2009 07:29 |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Well,
I'll use a cursor (reading from only one table). It's a simple 'SELECT' cursor, with the command FOR UPDATE. Then, just in the moment this cursor is OPENED (and locking the read rows), i'll want to know some information about who -and from where- this cursor has been opened: I'll need 'Session ID', 'User Name', 'User ID', 'Process ID' and 'Host Name'.
This information will be only for logging (well, something similar), so I don't need the same Informix values, I need Oracle distinctive values.
I hope explained correctly!
Thanks!!!
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
