Home » SQL & PL/SQL » SQL & PL/SQL » Seeing active locks & sessions (translating from Informix) (merged 3) (ORACLE 10.2.0.3.0 on HP-UX B.11.11)
Seeing active locks & sessions (translating from Informix) (merged 3) [message #428192] Tue, 27 October 2009 08:06 Go to next message
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) [message #428203 is a reply to message #428192] Tue, 27 October 2009 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what does your query return and maybe we will be able to give you the Oracle equivalent instead of trying to find each piece equivalent which may not exist.

Regards
Michel
Re: Seeing active locks & sessions (translating from Informix) (merged 3) [message #428252 is a reply to message #428192] Tue, 27 October 2009 09:55 Go to previous messageGo to next message
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 #428255 is a reply to message #428192] Tue, 27 October 2009 10:19 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since the locking mechanisms in oracle and Informix are wildly different I suspect that there's is no straight translation of this code.
I think you need to tell us what you're trying to achieve with this code for us to be able to give you an alternative.
Re: Seeing active locks & sessions (translating from Informix) (merged 3) [message #428461 is a reply to message #428192] Wed, 28 October 2009 07:29 Go to previous messageGo to next message
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!!!
Re: Seeing active locks & sessions (translating from Informix) (merged 3) [message #428465 is a reply to message #428461] Wed, 28 October 2009 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Join v$open_cursor and v$session or just just query v$session with status='ACTIVE'

Regards
Michel
Re: Seeing active locks & sessions (translating from Informix) (merged 3) [message #428467 is a reply to message #428192] Wed, 28 October 2009 08:07 Go to previous messageGo to next message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Thanks a lot!! I think it can be valid.

What's the difference between views called 'V_$XXXX' and views called 'GV_$XXXX', for excample, 'V_$SESSION' and 'GV_$SESSION'?

Best regards,
Donato.
Re: Seeing active locks & sessions (translating from Informix) (merged 3) [message #428485 is a reply to message #428467] Wed, 28 October 2009 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One is for the current instance, the other one (G=Global) for all instances of the database, only useful in RAC (take care of it as it implies inter-instances communication).

Regards
Michel
Re: Seeing active locks & sessions (translating from Informix) (merged 3) [message #428499 is a reply to message #428192] Wed, 28 October 2009 09:21 Go to previous message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Mmmmm... interesting!

Thanks a lot!!
Previous Topic: query related Pivot function of oracle
Next Topic: connect by prior question
Goto Forum:
  


Current Time: Fri Sep 30 02:12:50 CDT 2016

Total time taken to generate the page: 0.10806 seconds