oracle 加查询锁,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;