oracle 锁查询 select加锁方法

--一、oracle对select加锁方法

create table t_test(a number,b number);

INSERT INTO t_test VALUES (1, 2);
INSERT INTO t_test VALUES (3, 4);
INSERT INTO t_test VALUES (5, 6);
COMMIT;

---session 1 模拟选中一个号码
 
 SELECT * FROM t_test WHERE A = 1 FOR UPDATE SKIP LOCKED;

         A          B
---------- ----------
         1          2


---session 2 对a=1再进行select
 
 SELECT * FROM t_test WHERE A = 1 FOR UPDATE SKIP LOCKED;

--未选定行

-- session 3 全表select
SELECT * FROM t_test FOR UPDATE SKIP LOCKED;

         A          B
---------- ----------
         3          4
         8          9


--二、查询那些用户，操纵了那些表造成了锁机
SELECT S.USERNAME,
       DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
       O.OWNER,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       S.SID,
       S.SERIAL#,
       S.TERMINAL,
       S.MACHINE,
       S.PROGRAM,
       S.OSUSER
  FROM V$SESSION S, V$LOCK L, ALL_OBJECTS O
 WHERE L.SID = S.SID
   AND L.ID1 = O.OBJECT_ID(+)
   AND S.USERNAME IS NOT NULL;

--三、查出被锁的表，和锁住这个表的会话ID
SELECT A.SESSION_ID, B.*
  FROM V$LOCKED_OBJECT A, ALL_OBJECTS B
 WHERE A.OBJECT_ID = B.OBJECT_ID;

--四、 查出对应的SQL语句
SELECT VS.SQL_TEXT,
       VSESS.SID,
       VSESS.SERIAL#,
       VSESS.MACHINE,
       VSESS.OSUSER,
       VSESS.TERMINAL,
       VSESS.PROGRAM,
       VS.CPU_TIME,
       VS.DISK_READS
  FROM V$SQL VS, V$SESSION VSESS
 WHERE VS.ADDRESS = VSESS.SQL_ADDRESS
   AND VSESS.SID = (上面查出来的会话ID);

--五、

--１.查哪个过程被锁

--查V$DB_OBJECT_CACHE视图:

SELECT *
  FROM V$DB_OBJECT_CACHE
 WHERE OWNER = '过程的所属用户'
   AND LOCKS != '0';

--2. 查是哪一个SID,通过SID可知道是哪个SESSION.

--查V$ACCESS视图:

SELECT *
  FROM V$ACCESS
 WHERE OWNER = '过程的所属用户'
   AND NAME = '刚才查到的过程名';

--3. 查出SID和SERIAL#

--查V$SESSION视图:

SELECT SID, SERIAL#, PADDR FROM V$SESSION WHERE SID = '刚才查到的SID';

--查V$PROCESS视图:

SELECT SPID FROM V$PROCESS WHERE ADDR = '刚才查到的PADDR';

--4. 杀进程

--(1).先杀ORACLE进程:

ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';

--(2).再杀操作系统进程:

KILL - 9 '刚才查出的SPID';

--或

--ORAKILL 刚才查出的SID 刚才查出的SPID

--六、查找最耗费系统资源的SQL
--CPU
SELECT B.SQL_TEXT,
       A.BUFFER_GETS,
       A.EXECUTIONS,
       A.BUFFER_GETS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS),
       C.USERNAME
  FROM V$SQLAREA A, V$SQLTEXT_WITH_NEWLINES B, DBA_USERS C
 WHERE A.PARSING_USER_ID = C.USER_ID
   AND A.ADDRESS = B.ADDRESS
 ORDER BY A.BUFFER_GETS DESC, B.PIECE;

--IO
SELECT B.SQL_TEXT,
       A.DISK_READS,
       A.EXECUTIONS,
       A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS),
       C.USERNAME
  FROM V$SQLAREA A, V$SQLTEXT_WITH_NEWLINES B, DBA_USERS C
 WHERE A.PARSING_USER_ID = C.USER_ID
   AND A.ADDRESS = B.ADDRESS
 ORDER BY A.DISK_READS DESC, B.PIECE;

SELECT S.SID, S.VALUE "CPU Used"
  FROM V$SESSTAT S, V$STATNAME N
 WHERE S.STATISTIC# = N.STATISTIC#
   AND N.NAME = 'CPU used by this session'
   AND S.VALUE > 0
 ORDER BY 2 DESC;