ORA |
您所在的位置：网站首页 › mds-00054 › ORA |
|
ORA-00054
Failed to acquire a lock (Type: "Type", Name: "Name", Description: "Description") because it is currently held by another session. The resource being locked can be identified by ID1_value ("ID1_description") and ID2_value ("ID2_description")
Type: The enqueue resource type.Name: The enqueue resource name.Description: The enqueue resource description.ID1_value: The value of first identifier for the enqueue resource.ID1_description: The description of first identifier for the enqueue resource.ID2_value: The value of second identifier for the enqueue resource.ID2_description: The description of second identifier for the enqueue resource.
Cause
The current session is failing to acquire the specified lock on the specified resource because another session holds the lock. ActionRetry the operation and see if the lock has been released. If not, identify which session holds the lock and on which resource using the information provided in the error message text and terminate the transaction of the session before retrying the operation. Additional InformationTo identify the session that is holding the lock in question, run the following query. Note that this query requires SELECT privileges on the GV$SESSION and GV$LOCK views. For example: SELECT l.inst_id, s.sid, s.serial#, s.username, s.sql_id, l.ctime AS "LOCK_HOLD_TIME_ELAPSED_SECONDS", s.client_identifier, s.module, s.action FROM GV$SESSION s, GV$LOCK l WHERE l.sid = s.sid AND l.inst_id = l.inst_id AND l.type = '' AND l.id1 = AND l.id2 = ;The column LOCK_HOLD_TIME_ELAPSED_SECONDS (ctime) is the time since the current lock was granted. In other words, it is the number of seconds for which the other session has held the lock. Consider this example of a failing DROP TABLE operation because a session still holds a lock on the table: DROP TABLE my_transactions; DROP TABLE my_transactions * ERROR at line 1: ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML", Description: "Synchronizes accesses to an object") because it is currently held by another session. The resource being locked can be identified by 74855 ("Table") and 0 ("operation")With the information provided by the error message, you can transform the previous query template to the following, gaining insight into which session holds the lock. For example: SELECT l.inst_id, s.sid, s.serial#, s.username, s.sql_id, l.ctime AS "LOCK_HOLD_TIME_ELAPSED_SECONDS", s.client_identifier, s.module, s.action FROM GV$SESSION s, GV$LOCK l WHERE l.sid = s.sid AND l.inst_id = l.inst_id AND l.type = 'TM' AND l.id1 = 74855 AND l.id2 = 0; INST_ID SID SERIAL# USERNAME SQL_ID ---------- -------- -------- -------- ------------- 1 147 41814 GERALD af0zhnff94c7v LOCK_HOLD_TIME_ELAPSED_SECONDS CLIENT_IDENTIFIER MODULE ------------------------------ ----------------- -------- 129 SQL*Plus ACTION ----------This output shows that the session on Oracle Database where INST_ID is 1, SID is 147, SERIAL# is 41814, and USERNAME is GERALD holds the lock. Furthermore, we can infer that the session holds the lock for 129 seconds and is likely to be a SQL*Plus prompt, because the client information column MODULE shows SQL*Plus. At this stage, you can decide on the following options: Contact the end user of that session and ask to terminate the operation on the resource. Disconnect or terminate the session using the ALTER SYSTEM DISCONNECT ... KILL SESSION statement. Refrain from executing the current operations because users still have active operations on the resource. |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |