Shulou(Shulou.com)06/01 Report--

Note: this SQL can query multi-session, non-select DML operations and manipulate locks caused by table A. The relationship between conversations. Detailed notes on the views / fields used by the SQL are below. .... If you want to show other fields, you can increase them according to your own needs.

SQL: SELECT DISTINCT S.SID, / * unique identification of a session. Usually, before analyzing a session, you need to obtain the SID of that session. * /-- S.SERIAL# / * session serial number * /, S.STATE / * WAIT STATE~*/, S.BLOCKING_SESSION,-- SESSION IDENTIFIER OF THE BLOCKING SESSION. THIS COLUMN IS VALID ONLY IF BLOCKING_SESSION_STATUS HAS THE VALUE VALID. S.BLOCKING_SESSION_STATUS STATUS, / * THIS COLUMN PROVIDES DETAILS ON WHETHER THERE IS A BLOCKING SESSION: * / (CASE WHEN SQL_TEXT IS NULL / * LO.REQUEST = 0 * / THEN'(SID:' | | S.SID | |') session SQL has finished running 'ELSE' (SID:' | | S.SID | |') session is executing SQL:' | | SQL_.SQL_TEXT END) SQL_TEXT / * finished SQL'SQL_TEXT flag SQL has finished running Otherwise, mark SQL'*/,-- SQL_.SQL_FULLTEXT SQL full text, S.USERNAME / * create the user name of the session * /, O.OWNER | |'. | | O.OBJECT_NAME locked object,-- the value of this field of V$SESSION.ROW_WAIT_OBJ# after operation =-1 So the associated V$LOCKED_OBJECT fetches the lock table LO.REQUEST, the mode S.EVENT of the lock requested by the Lock mode in which the process requests the lock session, and the machine name of the S.MACHINE / * client. * /, S.LOGON_TIME / * login time * /, 'ALTER SYSTEM KILL SESSION''| | S.SID | |','| | S.SERIAL# | |' 'KILL-- if there is a lock, KILL lock release will be used ~ FROM V$SESSION S LEFT JOIN V$SQL SQL_ ON SQL_.SQL_ID = S.SQL_ID JOIN V$LOCKED_OBJECT L ON L.SESSION_ID = S.SID JOIN ALL_OBJECTS O ON L.OBJECT_ID = O.OBJECT_ID JOIN V$LOCK LO ON (LO.BLOCK! = 0 OR LO.REQUEST! = 0)-V$LOCK.block = > A value of either 0 or 1 Depending on whether or not the lock in question is the blocker-- V$LOCK.REQUEST = > Lock mode in which the process requests the lock: the meaning of the following value ~ ['0-none'] WHERE LO.SID = L.SESSION_ID AND LO.SID = S.SID ORDER BY S.BLOCKING_SESSION DESC

Note:-- View = = official website comment-- v$session = = http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223-- V$SQL = = http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3043.htm#REFRN30246-- V$LOCK = = http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2027.htm#REFRN30121-- V$LOCKED_OBJECT = = http: / / docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2030.htm#REFRN30125-- ALL_OBJECTS = = http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1158.htm#REFRN20146

-- display field = = official website note: V$SESSION.STATE = Wait state:-- WAITING-Session is currently waiting-- WAITED UNKNOWN TIME-Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false-- WAITED SHORT TIME-Last wait was less than a hundredth of a second-- WAITED KNOWN TIME-Duration of the last wait is specified in the WAIT_TIME column S.BLOCKING_SESSION,-- Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.V$SESSION.BLOCKING_SESSION_STATUS = This column provides details on whether there is a blocking session:-- VALID-there is a blocking session And it is identified in the BLOCKING_INSTANCE and BLOCKING_SESSION columns-- NO HOLDER-there is no session blocking this session-- NOT IN WAIT-this session is not in a wait-- UNKNOWN-the blocking session is unknownV$LOCK.REQUEST = Lock mode in which the process requests the lock:-- 0-none-- 1-null (NULL)-- 2-row-S (SS)-3-row-X (SX)-- 4-share (S)-- 5-S/Row-X (SSX)-- 6-exclusive (X)

Have a good day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.