Home » RDBMS Server » Server Administration » How to know who lock the Account ? (oracle 10.2.0.3 , sun solaries 10)
How to know who lock the Account ? [message #408824] Thu, 18 June 2009 00:05 Go to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dears

In a online banking system,a user want to debit a global Account but it saying that locked by some other user.
It's happen very regularly in some particular accounts.
I would like to know the session/userid who locked that account.

is it possible to know? please help me

Regards
Halim
Re: How to know who lock the Account ? [message #408833 is a reply to message #408824] Thu, 18 June 2009 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't unless you set audit or create a ddl trigger for this.

From my experience, it is most likely that this user has a profile with a limit on failed logins and this is why his account is locked not because someoneelse loked it.
What is the exact status of the account in dba_users?

Regards
Michel
Re: How to know who lock the Account ? [message #408868 is a reply to message #408833] Thu, 18 June 2009 02:14 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

thanks

sorry for my not cleared information.

Accouts means banking Account number(a row)

here, all application users(aprocximate 700 users) are connecting to
the database .

i want to know that,How can i know the session id
who lock the specific row of a table (my desire row) not table.

regards
Halim

Re: How to know who lock the Account ? [message #408872 is a reply to message #408868] Thu, 18 June 2009 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What do you mean "lock"? In Oracle meaning (like any kind of row) or in functional meaning (like banking account lock)?

Regards
Michel
Re: How to know who lock the Account ? [message #408879 is a reply to message #408872] Thu, 18 June 2009 02:43 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dear Michel

forget all .
I just want to know.

How to find a session/user who locked my specific table's row ?

regards
Halim

[Updated on: Thu, 18 June 2009 03:29] by Moderator

Report message to a moderator

Re: How to know who lock the Account ? [message #408892 is a reply to message #408879] Thu, 18 June 2009 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't.
This information is not centralized, it is recorded in each block.
You can only knows it when you are waiting for the row, then you can see on which row you are waiting in v$session.

Regards
Michel
Re: How to know who lock the Account ? [message #408893 is a reply to message #408892] Thu, 18 June 2009 03:47 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

thanks Michel

regards
Halim
Re: How to know who lock the Account ? [message #409368 is a reply to message #408893] Mon, 22 June 2009 05:58 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dear Michel

what's Error in second query ? please help me

I googled it , is it help me .

SQL> select    s.sid,b.object_name
  2  ,  row_wait_obj#
  3  ,  row_wait_file#
  4  ,  row_wait_block#
  5  ,  row_wait_row#
  6  ,  dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
  7                             ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) rowidd
  8  from       v$session s, dba_objects b
  9  where s.ROW_WAIT_OBJ# = b.OBJECT_ID
 10  /

       SID OBJECT_NAME    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# ROWIDD
---------- ----------------------- ------------------ ----- ------------- -------------
       120 I_IND1                     39    1     247    0 AAAAAnAABAAAAD3AAA
       162 SCHEDULER$_QINFO_PK      5156    1    11226   0 AAABQkAABAAACvaAAA
       161 WRI$_ALERT_OUTSTANDING   8850    3     2662   0 AAACKSAADAAAApmAAA
       127 LK_PARAMETER            59110   16     3912   0 AAAObmAAQAAAA9IAAA
       138 LK_ATM_LOCATION         59018   16       20   0 AAAOaKAAQAAAAAUAAA
       125 STBASEL2                60436    9    34297   0 AAAOwUAAJAAAIX5AAA
       118 STBASEL2                60436    9    34297   0 AAAOwUAAJAAAIX5AAA
       113 STBASEL2               60436              9           34297             0 AAAOwUAAJAAAIX5AAA

8 rows selected.

SQL> select * from (
  2   select    s.sid,b.object_name
  3  ,  row_wait_obj#
  4  ,  row_wait_file#
  5  ,  row_wait_block#
  6  ,  row_wait_row#
  7  ,  dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
  8                             ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) rowidd
  9  from       v$session s, dba_objects b
 10  where s.ROW_WAIT_OBJ# = b.OBJECT_ID )
 11  where rowidd='AAAOwUAAJAAAIX5AAA'
 12  /
,  dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
   *
ERROR at line 7:
ORA-01410: invalid ROWID
ORA-06512: at "SYS.DBMS_ROWID", line 38

SQL>



[Updated on: Mon, 22 June 2009 07:33] by Moderator

Report message to a moderator

Re: How to know who lock the Account ? [message #409389 is a reply to message #409368] Mon, 22 June 2009 07:33 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The columns are only valid within some circonstances that are defined in the documentation, please refer to Reference manual.

Regards
Michel
Previous Topic: DBA_OUTSTANDING_ALERT not genrating
Next Topic: Error while enabling database_tab_monitoring
Goto Forum:
  


Current Time: Fri Nov 29 13:48:03 CST 2024