(Select jumin from B where substr(email, 1, instr(email, '@', 1) -1) = A.chg_id);
순위매기기
1.RANK
RANK() OVER (
[PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...] )
*동순위일때 건너뜀(1,1,1,3,3,5)
2.DENSE_RANK
DENSE_RANK() OVER (
[PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...] )
*동순위일때 연속됨(1,1,1,2,2,3)
DATA : A,A,A,B,B,C
테이블 LOCK 확인 및 해제
SELECT A.SID
, A.SERIAL#
, A.USERNAME
, A.PROCESS
, B.OBJECT_NAME
, DECODE (C.LMODE, 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 8, 'X', 'NO') TABLE_LOCK
, DECODE (A.COMMAND, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 12, 'DROP', 6, 'LOCK', 'UNKNOWN') SQL
, DECODE (A.LOCKWAIT, NULL, 'NO WAIT', 'WAIT') STATUS
FROM V$SESSION A, DBA_OBJECTS B, V$LOCK C
WHERE A.SID = C.SID
AND B.OBJECT_ID = C.ID1
AND C.TYPE = 'TM'
AND A.USERNAME = 'USER_NAME';
SELECT SUBSTR(C.OBJECT_NAME,1,20),A.SID,A.SERIAL#
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID = B.SID
AND B.ID1 = C.OBJECT_ID
AND B.TYPE='TM';
ALTER SYSTEM KILL SESSION 'SID, SERIAL';