[轉貼]Oracle如何找出某一Table被哪個User或程式Lock??

[tim]

http://delphi.ktop.com.tw/topic.asp?TOPIC_ID=32560


1.

代碼: 選擇全部

select * from sys.dba_dml_locks; -- 取得其中 session_id
select * from sys.gv_$session where sid=[session_id] -- 可取得 user / machine name ... 等資訊

2.
Here are a few things to try:
1. Run this script to determine who is holding what locks:
/* Detecting Locking rows */

代碼: 選擇全部

SELECT owner || '.' || object_name || ' [' || object_type || ']' obj_info,
     dbms_rowid.rowid_create(1, row_wait_obj#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "Rowid",
     a.username, a.sid,
     a.row_wait_file#, a.row_wait_block#, a.row_wait_row#
FROM DBA_OBJECTS,
     (SELECT a.username, a.sid, a.row_wait_obj#, a.ROW_WAIT_FILE#,
a.ROW_WAIT_BLOCK#, a.ROW_WAIT_ROW#
        FROM v$session a, v$lock b
       WHERE a.username IS NOT NULL
         AND a.row_wait_obj# > 0
         AND a.sid = b.sid
         AND b.TYPE = 'TX') a
WHERE object_id = a.row_wait_obj#
ORDER BY 1, 2
[/coee]