Translate

2022년 3월 18일 금요일

[Oracle] 오라클 시스템 객체 쿼리 모음

  오전 11:03    

Laptop
운영체제Windows 10 Pro 64bit
개발환경Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


1. 테이블 DML 쿼리 Lock 조회

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT DISTINCT
       T1.SESSION_ID
     , T2.SERIAL#
     , T4.OBJECT_NAME
     , T2.MACHINE
     , T2.TERMINAL
     , T2.PROGRAM
     , T3.ADDRESS
     , T3.PIECE
     , T3.SQL_TEXT
     , T2.PREV_EXEC_START
     , 'ALTER SYSTEM KILL SESSION ' || '''' || T1.SESSION_ID || ', ' || T2.SERIAL# || ''';' KILL_SQL
     , T5.SPID PID
  FROM V$LOCKED_OBJECT T1
     , V$SESSION T2
     , V$SQLTEXT T3
     , DBA_OBJECTS T4
     , V$PROCESS T5
 WHERE T1.SESSION_ID = T2.SID
   AND T1.OBJECT_ID = T4.OBJECT_ID
   AND T2.SQL_ADDRESS = T3.ADDRESS
   AND T2.PADDR = T5.ADDR
--   AND MACHINE = 'PC명'
--   AND OBJECT_NAME = '테이블명'
   ORDER BY T3.ADDRESS, T3.PIECE;


2.  Package DDL Lock 조회

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT S.SID, S.SERIAL#,
       L.LOCK_TYPE,
       L.MODE_HELD,
       L.MODE_REQUESTED,
       L.LOCK_ID1,
       'ALTER SYSTEM KILL SESSION ' || '''' || S.SID || ', ' || S.SERIAL#, || ''';' KILL_SQL
    FROM   DBA_LOCK_INTERNAL L,
       V$SESSION S
    WHERE S.SID = L.SESSION_ID
    AND L.LOCK_TYPE = 'Body Definition Lock'
    AND UPPER(L.LOCK_ID1) = UPPER('패키지명');


3. JOB 목록 조회

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT
  JOB,
  WHAT,
  INTERVAL,
  TO_CHAR(NEXT_DATE, 'YYYY-MM-DD HH24:MI:SS') NEXT_DATE,
  TO_CHAR(THIS_DATE, 'YYYY-MM-DD HH24:MI:SS') THIS_DATE
FROM
  USER_JOBS
WHERE
  BROKEN = 'N'
  AND what LIKE '%' || 'JOB명' || '%'
ORDER BY
  NEXT_DATE;


4. 실행 중인 JOB 조회

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT D.JOB,
       S.SID,
       S.SERIAL#,
       STATUS,
       LOG_USER USERNAME,
       WHAT,
       DECODE (TRUNC (SYSDATE - LOGON_TIME), 0, NULL, 
            TRUNC (SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR (TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'), 'HH24:MI:SS')
         RUNNING,
       D.FAILURES,
       'ALTER SYSTEM KILL SESSION ' || '''' || S.SID || ', ' || S.SERIAL# || ''';' KILL_SQL
  FROM DBA_JOBS_RUNNING D, V$SESSION S, DBA_JOBS J
 WHERE S.SID = D.SID AND D.JOB = J.JOB;


5. 객체(Package, Procedure, Function, Trigger...) 스크립트 검색

1
2
3
4
5
SELECT * FROM
  USER_SOURCE
WHERE
  UPPER('%' || '검색어' || '%')
  AND NAME LIKE '%' || '객체명' || '%';

단, 위 쿼리에서 VIEW는 USER_VIEWS 라는 별도의 테이블을 쓰기 때문에 조회되지 않는데, 스크립트가 나오는 TEXT 컬럼의 타입이 LONG으로 되어있어서 LIKE문을 쓸 수 없기 때문에 만약 함께 포함된 결과를 보고싶다면 CLOB 타입으로 변환할 임시 테이블을 쓰는 약간 번거로운 과정을 거쳐야 한다.
또한 LINE의 경우 VIEW 스크립트에서는 CREATE 부분을 없앤 상태로 주 쿼리만을 보여주기 때문에 developer 등의 기능으로 조회해서 볼 경우 맞지 않아보일 수 있다.

편하게 쓰기 위해 프로시저로 정의했다.
(임시 테이블이 남는게 싫다면 프로시저 내에서 EXECUTE로 CREATE/DROP을 할 수도 있을 것 같은데, DDL이 너무 자주 수행되는 것도 낭비같아서 일단은 남겨두기로...)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
CREATE GLOBAL TEMPORARY TABLE USER_VIEWS_CLOB
(
  NAME  VARCHAR2(30 BYTE),
  TEXT  CLOB
);

CREATE OR REPLACE PROCEDURE SP_SEARCH_OBJ(
    p_text IN VARCHAR2,
    p_type IN VARCHAR2,
    p_name IN VARCHAR2,
    v_result OUT SYS_REFCURSOR
)
IS 
BEGIN
    INSERT INTO USER_VIEWS_CLOB
    SELECT VIEW_NAME, TO_LOB(TEXT) FROM USER_VIEWS;
    
    OPEN v_result FOR
        SELECT * FROM (
            SELECT
              NAME, TYPE, LINE, TEXT
            FROM
              USER_SOURCE
            UNION
            SELECT
              NAME, 'VIEW' TYPE, LINE, TO_CHAR(TEXT)
            FROM
              (
                SELECT
                  NAME, LEVEL LINE, REGEXP_SUBSTR (TEXT, '.+', 1, LEVEL) TEXT
                FROM (
                    SELECT
                      NAME, TEXT
                    FROM
                      USER_VIEWS_CLOB
                    WHERE
                      UPPER(text) LIKE UPPER('%' || p_text || '%')
                  ) CONNECT BY LEVEL <= REGEXP_COUNT (TEXT, '.+')
              )
        )
        WHERE 
            UPPER(text) LIKE UPPER('%' || p_text || '%')
            AND TYPE = NVL(UPPER(p_type), TYPE)
            AND NAME LIKE '%' || UPPER(p_name) || '%';
    
    DELETE FROM  USER_VIEWS_CLOB;
    COMMIT;
END;

-- EXEC SP_SEARCH_OBJ('검색어', '타입', '객체명', :v_result);


6. 임의 날짜로부터 특정 일수동안의 목록 조회

ex) 2022년 1월 1일부터 1년(365일)
1
2
3
SELECT TO_DATE('20220101','YYYYMMDD') + (LEVEL-1) AS v_date
FROM DUAL 
CONNECT BY LEVEL <= (365)

~ 



- 참고 사이트




  

0 개의 댓글:

댓글 쓰기

피드 구독하기: 댓글 (Atom)