내맘대로

-- 락 걸린 테이블 확인 select do.object_name,do.owner,do.object_type,do.owner, vo.xidusn, vo.session_id, vo.locked_mode from v$locked_object vo, dba_objects do where vo.object_id = do.object_id ​ -- 특정 테이블이 락 걸렸는지 확인 select A.SID, A.SERIAL#, B.TYPE,C.OBJECT_NAME 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' AND C.OBJECT_NAME IN ('테이블명') ​ -- 쿼리 확인 sel..

JOB 등록 방법 1. 생성 DECLARE v_job NUMBER; -> job 번호 들어감..자동으로 BEGIN SYS.DBMS_JOB.SUBMIT ( v_job ,'USER.PROCEDURE_NAME;' ,TO_DATE('2012/08/08 05:00:00', 'YYYY/MM/DD HH24:MI:SS'), -- 다음실행 시간 ,'TRUNC(SYSDATE+1)+ 1/24', -- 실행주기 , FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(v_job)); COMMIT; END; 2. 수정 EXEC DBMS_JOB.CHANGE(job, '프로시져명', 다음실행 시간, 'TRUNC(SYSDATE+1)+ 5/24'); 전체 수정할 경우는 CHA..

오라클 피벗 기능 WITH TEST_TEMP AS ( SELECT '2013' AS YEA, '01' AS MON, '10000' VAL FROM DUAL UNION ALL SELECT '2013' AS YEA, '02' AS MON, '20000' VAL FROM DUAL UNION ALL SELECT '2013' AS YEA, '03' AS MON, '30000' VAL FROM DUAL UNION ALL SELECT '2013' AS YEA, '04' AS MON, '40000' VAL FROM DUAL UNION ALL SELECT '2013' AS YEA, '05' AS MON, '50000' VAL FROM DUAL UNION ALL SELECT '2013' AS YEA, '06' AS MON..