--一、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 全表selectSELECT * 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;--三、查出被锁的表，和锁住这个表的会话IDSELECT 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--CPUSELECT 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;--IOSELECT 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;
