OraFAQ Forum: Server Administration » How to know who lock the Account ?

Home » RDBMS Server » Server Administration » How to know who lock the Account ? (oracle 10.2.0.3 , sun solaries 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to know who lock the Account ? [message #408824]

Thu, 18 June 2009 00:05

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

Report message to a moderator

Re: How to know who lock the Account ? [message #408833 is a reply to message #408824]

Thu, 18 June 2009 00:30

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: How to know who lock the Account ? [message #408868 is a reply to message #408833]

Thu, 18 June 2009 02:14

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

Report message to a moderator

Re: How to know who lock the Account ? [message #408872 is a reply to message #408868]

Thu, 18 June 2009 02:28

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: How to know who lock the Account ? [message #408879 is a reply to message #408872]

Thu, 18 June 2009 02:43

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

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: How to know who lock the Account ? [message #408893 is a reply to message #408892]

Thu, 18 June 2009 03:47

halim
Messages: 100
Registered: September 2008

Senior Member

thanks Michel

regards
Halim

Report message to a moderator

Re: How to know who lock the Account ? [message #409368 is a reply to message #408893]

Mon, 22 June 2009 05:58

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

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Previous Topic:	DBA_OUTSTANDING_ALERT not genrating
Next Topic:	Error while enabling database_tab_monitoring

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 22:56:03 CDT 2024