Dev Note/DB2010. 4. 7. 15:57
테이블 사용 중 'ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다.'
의 오류 메세지가 있는 경우.. 대부분이 테이블 락으로 인한 것이다.
테이블 락이 걸린 세션을 찾아서 죽이는 방법은 아래와 같다.

SELECT
    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'
    AND C.OBJECT_NAME='테이블 이름'

결과는 해당 테이블에 락이 걸려있는 세션에 대한 정보 조회가 가능하다.
SID SERIAL#
--- -------
25     12345 

alter system kill session '25, 12345';
로 해당 세션을 죽이면 된다.
Posted by as.wind.914
Dev Note/DB2008. 3. 23. 02:31
Oracle 내장함수 중 유용한 몇몇에 대해 알아본다.

-- 숫자 함수 (Number Function)
ABS(n) : 절대값을 계산하는 함수
   SQL> SELECT ABS(-10) FROM DUAL;      // 결과는 10
CEIL(n) : 주어진 값보다 큰 최소 정수값을 구하는 함수
   SQL> SELECT CEIL(5.1) FROM DUAL;      // 결과는 6
   SQL> SELECT CEIL(-5.1) FROM DUAL;      // 결과는 -5
FLOOR(n) : 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수
   SQL> SELECT FLOOR(5.1) FROM DUAL;      // 결과는 5
   SQL> SELECT FLOOR(-5.1) FROM DUAL;     // 결과는 -6
EXP(n) : 주어진 값의 e의 승수를 구하는 함수
LN(n) : 주어진 값의 자연로그 값을 구하는 함수
MOD(m, n) : m을 n으로 나우어 남은 값을 반환한다.
   SQL> SELECT MOD(5, 3) FROM DUAL;      // 결과는 2
   SQL> SELECT MOD(5, 0) FROM DUAL;      // 결과는 5
POWER(m, n) : m의 n승 값을 구하는 함수
   SQL> SELECT POWER(2, 3) FROM DUAL;      // 결과는 8
ROUND(m, n) : m 값의 반올림을 구하는 함수. n은 소숫점 자릿수를 명시
   SQL> SELECT ROUND(111.126, 1) FROM DUAL;      // 결과는 111.1
   SQL> SELECT ROUND(111.126, -1) FROM DUAL;      // 결과는 110
SIGN(n) : n 값의 부호를 구하는 함수. n > 0일때는 1, n = 0일때는 0, n < 0 일때는 -1
SQRT(n) : n 값의 루트값을 구하는 함수. n은 양수이어야 한다.
TRUNC(n, m) : n 값을 m 소숫점 자리로 반내림한 값을 구하는 함수
   SQL> SELECT TRUNC(10.678, 2) FROM DUAL;      // 결과는 10.67
   SQL> SELECT TRUNC(567.345, -2) FROM DUAL;      // 결과는 500

-- 문자 함수 (String Function)
CONCAT(str1, str2) : 두 문자를 합치는 함수. "||" 연산자와 같은 역할을 합니다.
   SQL> SELECT CONCAT('Oracle', ' Korea') NAME FROM DUAL;      // 결과는 Oracle Korea
INITCAP(str) : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 주는 함수
   SQL> SELECT INITCAP('junducki') FROM DUAL;      // 결과는 Junducki
LOWER(str) : 문자열을 소문자로 변환시켜주는 함수
   SQL> SELECT LOWER('JUNDUCKI') FROM DUAL;      // 결과는 junducki
UPPER(str) : 문자열을 대문자로 변환시켜주는 함수
   SQL> SELECT UPPER('junducki') FROM DUAL;      // 결과는 JUNDUCKI
LPAD(str1 , n, str2) : str1 문자열의 왼쪽에 str2 문자열을 str1의 문자열 길이가 n이 되게 채워주는 함수. str1의 문자열이 n보다 클 경우 str1을 n개 문자열 만큼 반환합니다.
   SQL> SELECT LPAD('jin', 5, '-') FROM DUAL;      // 결과는 --jin
RPAD(str , n, char2) : LPAD와 반대로 오른쪽을 채워주는 함수
   SQL> SELECT RPAD('jin', 5, '-') FROM DUAL;      // 결과는 jin--
SUBSTR(str, m, n) : str 문자열의 m 번째 자리부터 n개의 문자열을 구하는 함수
   SQL> SELECT SUBSTR('junducki', 3, 3) FROM DUAL;      // 결과는 ndu
   SQL> SELECT SUBSTR('junducki', -3, 3) FROM DUAL;      // 결과는 cki
LENGTH(str) : str 문자열의 길이를 구하는 함수
   SQL> SELECT LENGTH('junducki') FROM DUAL;      // 결과는 8
REPLACE(str1, str2, str3) : str1 문자열에서 str2과 매칭되는 부분을 str3으로 변환하는 함수. 대소문자를 구분함
   SQL> SELECT REPLACE('Ukzang ukzang', 'U', 'j') FROM DUAL;      // 결과는 jkzang ukzang
INSTR(str1, str2, m, n) : str1 문자열에 str2가 매칭되는 위치를 구하는 함수. m은 str1 문자열의 m 위치에서 부터 검색. n은 매칭되는 횟수를 지정. 매칭되는 것이 없을 때는 0
   SQL> SELECT INSTR('junducki junducki', 'u') FROM DUAL;      // 결과는 2
   SQL> SELECT INSTR('junducki junducki', 'u', 3) FROM DUAL;      // 결과는 5
   SQL> SELECT INSTR('junducki junducki', 'u', 3, 2) FROM DUAL;      // 결과는 11
TRIM(str1, str2) : str1 문자열의 양끝의 str2 문자열을 제거하는 함수, str2를 주지 않으면 [공백]을 제거한다.
LTRIM(str1, str2) : TRIM을 왼쪽 끝만 적용
RTRIM(str1, str2) : TRIM을 오른쪽 끝만 적용
VSIZE(str) : str 문자열의 Byte 수를 구하는 함수. NULL이면 NULL이 반환
   SQL> SELECT VSIZe('junducki') FROM DUAL;      // 결과는 8

-- 날짜 함수 (Date Function)
LAST_DAY(d) : 달의 마지막 날을 구하는 함수
   SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;      // 결과 31-03-2008
ADD_MONTH(m, n) : m의 날짜에 n 달을 더해주는 함수
   SQL> SELECT ADD_MONTH(SYSDATE, 2) FROM DUAL;      // 결과 24-05-2008
MONTH_BETWEEN(m, n) : m 날짜와 n 날짜 사이의 달수를 구하는 함수
   SQL> SELECT MONTHS_BETWEEN(TO_DATE('2008/06/05') , TO_DATE('2008/09/23')) FROM DUAL;     
         // 결과는 -3.880635
ROUND(d, [f]) : d 날짜를 f로 지정한 단위로 반올림을 구하는 함수
   SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'YEAR') FROM DUAL;      // 결과는 2009-01-01
   SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'MONTH') FROM DUAL;      // 결과는 2008-08-01
   SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'DAY') FROM DUAL;      // 결과는 2008-08-11
날짜에 대한 산술 연산
   날짜 + 숫자 : 결과는 날짜. 날짜부터 숫자만큼의 날수가 지난 날짜
   날짜 - 숫자 : 결과는 날짜. 날짜부터 숫자만큼의 날수가 전인 날짜
   날짜 - 날짜 : 결과는 숫자. 두 날짜의 차이

-- 변환 함수 (Convert Function)
TO_CHAR : DATE형, NUMBER형을 VARCHAR2형으로 변환해주는 함수
   SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;      // 결과는 2008/03/28
TO_DATE : CHAR형, VARCHAR2형을 DATE형으로 변환해주는 함수
   SQL> SELECT TO_DATE('2008/03/28', 'YYYY/MM/DD') FROM DUAL;      // 결과는 2008/03/28
TO_NUMBER : CHAR형, VARCHAR2형을 숫자형으로 변환해주는 함수
   SQL> SELECT TO_NUMBER('12327') FROM DUAL;      // 결과는 12327

-- 기타 함수 (Etc Function)
NVL : NULL 값을 다른 값으로 변환해주는 함수. 모든 데이터 타입에 사용가능.
   SQL> SELECT empno, NVL(comm, 0) FROM emp;
   EMPNO             NVL(COMM, 0)
   ----------------------------------------------
   7499                  300
   7521                  0
DECODE(value, if1, then1, if2, then2, ...) : 데이터들을 다른 값으로 변환해주는 함수. value 값이 if1일 경우 then1으로, if2일 경우 then2로 ...
   SQL> SELECT deptno, DECODE(deptno, 10, 'AAA', 20, 'BBB', 30, 'CCC') FROM emp;
   DEPTNO           DECODE(DEPT
   ------------------------------------------------
   10                       AAA
   30                      CCC
   20                      BBB
GREATEST(n1, n2, ...) : 값 중 최대값을 구하는 함수
   SQL> SELECT GREATEST(10, -5, 16, 20, -11) FROM DUAL;      // 결과는 20
LEAST(n1, n2, ...) : 값 중 최소값을 구하는 함수
   SQL> SELECT LEAST(10, -5, 16, 20, -11) FROM DUAL;      // 결과는 -11
Posted by as.wind.914
Dev Note/DB2008. 3. 23. 01:43
Oracle에서 Data를 계층형 구조로 가져오는 유용한 쿼리에 대해 알아본다.
DB를 설계하다 보면 상위계층과 하위계층이 연관관계를 가지는 테이블 구조가 많이 나온게 된다. 예를 들어 상품의 카테고리, 게시물과 답변글의 관계 등.
이런 계층형 구조를 가지는 테이블의 Data를 계층형으로 구조로 조회할 수 있는 쿼리를 Oracle에서 지원한다. 그것이 바로 START WITH ~ CONNECT BY ~ 이다.

START WITH : 계층질의의 최상위(부모)로 사용할 기준 Data를 정의한다. 서브쿼리도 사용할 수 있다.
CONNECT BY : 계층구조의 상위와 하위의 관계를 정의한다. 서브쿼리는 사용할 수 없다.

그럼 간단한 사용예를 알아본다.

   CREATE TABLE TB_PRGM (
      PRGM_ID VARCHAR2(10) NOT NULL,
      PRTN_PRGM_ID VARCHAR2(10),
      PRGM_NM VARCHAR2(50) NOT NULL,
      DISP_ORDER NUMBER(3) NOT NULL,
      USE_YN CHAR(1) NOT NULL
   )

위와 같이 프로그램에 대해 간단히 정리한 테이블이 있다. PRGM_ID는 프로그램 아이디이며, PRNT_PRGM_ID는 해당 프로그램이 속하는 상위 프로그램의 아이디를 명시한다. 루트 프로그램은 NULL 값을 가진다. DISP_ORDER는 동일 PRNT_PRGM_ID로 묶여 있는 프로그램들의 display ordering 값이다.

   PRGM100         NULL         프로그램100            1          Y
      PRGM110         NULL         프로그램110            1          Y
         PRGM111         NULL         프로그램111            1          Y
         PRGM112         NULL         프로그램112            2          N
      PRGM120         NULL         프로그램120            2          Y
         PRGM121         NULL         프로그램121            1          N
   PRGM200         NULL         프로그램200            1          Y

위와 같은 Data가 TB_PRGM 테이블에 저장이 되어 있고, 위의 프로그램 정보를 계층구조로 조회하는 쿼리는 다음과 같다.

   SELECT PRGM_ID, PRGM_NM, LEVEL
   FROM TB_PRGM
   START WITH PRTN_PRGM_ID IS NULL
   CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID

여기서 LEVEL은 계층구조의 depth를 표현해주는 예약어이다. 위 쿼리는 부모에서 자식으로 계층구조를 만든것이고, 반대로 자식에서 부모로 계층구조를 만들수도 있는데 PRIOR의 위치를 변경하면 된다.

   CONNECT BY PRIOR 자식컬럼 = 부모컬럼               // 부모에서 자식으로 계층구조
   CONNECT BY 자식컬럼 = PRIOR 부모컬럼               // 자식에서 부모로 계층구조

그럼 여기서 트리(계층구조)로 형성 된 Data를 동일 계층내에서 정렬을 할려면 어떻게 하면 될까? ORDER BY를 사용하게 되면 계층구조로 만들어진 Data들이 ORDER BY 절에 명시 된 컬럼에 의해 다시 정렬이 되어 계층구조는 모두 사라지게 된다. 이때는 ORDER SIBLINGS BY를 사용해야 한다.

   SELECT PRGM_ID, PRGM_NM, LEVEL
   FROM TB_PRGM
   START WITH PRTN_PRGM_ID IS NULL
   CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID
   ORDER SIBLINGS BY DISP_ORDER

한가지 더 WHERE 절을 어떻게 사용하면 되느냐?

   SELECT PRGM_ID, PRGM_NM, LEVEL
   FROM TB_PRGM
   START WITH PRTN_PRGM_ID IS NULL
   CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID
   WHERE USE_YN = 'Y'

위와 같이 WHERE 절을 CONNECT BY 밑에 사용하면 된다.

-- START WITH ~ CONNECT BY ~ 의 실행 순서
1. START WITH 절이 실행되어 계층구조의 루트 기준을 잡는다.
2. CONNECT BY 절에 의해 계층구조를 만든다.
3. WHERE 절에 의해 조건이 실행된다.
Posted by as.wind.914