plsql developer 편의기능집합

sql 툴을 사용할 때

편하게 사용할 수 있는 세팅을

미리 작업해놓거나

편의기능들을 알아두어

개발할 땐 집중해서 일할 수 있는

환경을 만들어놓고자 정리해놓습니다.

#plsql developer 세미콜론 단위로 실행

plsql 실행 시 블럭지정 후 실행해야 하는 불편함이 존재한다.

세미콜론 단위로 실행할 수 있도록 아래 옵션을 체크한다.

Tool > Preferences > preferences > Window Types > SQL Window > AuthSelect statement

#라인번호 표시

Tool > Preferences > preferences > Window Types > SQL Window > Show gutter ( line numbers) 체크

#null 값 색깔 표시

Tool > Preferences > preferences > Window Types > SQL Window > Null value cell color 에서 색 선택

#결과값 색상 번갈아가며 표시하지 않기

Tool > Preferences > preferences > Window Types > SQL Window > Alternate row color 의 Enabled 체크 해제

#sql 결과갯수제한

3가지 옵션이 있는데 주로 Fixed 로 100 정도 설정해놓습니다.

전체 데이터를 확인할 일은 생각보다 많이 없고 필요하면 추가조회하면 됩니다.

Tool > Preferences > preferences > Window Types > SQL Window > Records per Page

#연결 유지

Tool > Preferences > preferences > Oracle > Connection > Check connection 에 체크

#접속정보 저장

Tool > Preferences > preferences > Oracle > Logon History > Definition > Store with password 체크

#sql 실행내역 / sql history / sql 로그 확인

Edit > Recall Statement ( 단축키 Ctrl + E )

오라클 최근 생성된 테이블, 뷰 확인

시스템운영 또는 프로젝트 진행중

테이블이나 뷰 생성 시

공유하지 않는다면 알 수 없습니다.

테이블 생성시간

테이블 컬럼변경 확인

테이블 변경여부 확인

위의 내용을 ALL_OBJECTS 테이블에서 조회하면

테이블이나 뷰의 신규/변경된 내역을 확인할 수 있습니다.

( DBA_OBJECTS = ALL_OBJECTS )

응용

최근 생성된 테이블 확인

SELECT OWNER
     , OBJECT_NAME
     , SUBOBJECT_NAME
     , OBJECT_TYPE
     , CREATED
     , LAST_DDL_TIME
     , TIMESTAMP
     , STATUS
     , TEMPORARY
  FROM ALL_OBJECTS 
 ORDER BY CREATED DESC ;

[결과]

최근 변경된 테이블 확인

SELECT OWNER
     , OBJECT_NAME
     , SUBOBJECT_NAME
     , OBJECT_TYPE
     , CREATED
     , LAST_DDL_TIME
     , TIMESTAMP
     , STATUS
     , TEMPORARY
  FROM ALL_OBJECTS 
 ORDER BY LAST_DDL_TIME DESC ;

[결과]

oracle 11g 기준 ALL_OBJECT column comments

ALL_OBJECTS vs USER_OBJECT 차이

USER_OBJECT : 로그인유저의 객체조회

ALL_OBJECT  : 전체 owner 의 객체조회 ( owner 컬럼을 사용하여 구분가능 )

ALL_OBJECTS view 에 권한이 없다면

USER_OBJECT 로 확인 가능합니다.

오라클 자주발생하는 에러 및 해결

오라클 자주 발생하는 에러모음이며

추가적인 오류 발생 및 해결방안/예측가능한 방법이

생길 시 주기적으로 정리하여 업데이트 예정입니다.

오류 순서 ( 오류코드를 ctrl + F 로 찾아서 원하는 내용 확인하시면 편합니다. )

ORA-00001: 무결성 제약 조건(USERS.EMP)에 위배됩니다.

ORA-00904: "EMPNOA": 부적합한 식별자

ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

ORA-01400: NULL을 ("SCOTT"."EMP"."EMPNO") 안에 삽입할 수 없습니다.

ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다

ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 11수에 "_SYSSMU11_1602219420$" 이름으로 된 것이 너무 작습니다

ORA-14016: LOCAL 분할영역 인덱스로 강조된 테이블은 분할영역이 되어 있어야 합니다

ORA-12899: "SCOTT"."EMP"."ENAME" 열에 대한 값이 너무 큼(실제: 13, 최대값: 10)

ORA-30926: 원본 테이블의 고정 행 집합을 가져올 수 없습니다

오류코드 ( ORA-00001 )

원인

1. EMPNO 가 Primary Key 로 설정되어 있을 때 같은 값을 입력할 경우 key 중복오류가 발생합니다.

2. 다수의 건을 적재 시 Primary Key 값이 동일한 row 가 여러건이 아닌지 확인이 필요합니다.

오류코드 ( ORA-00904 )

ORA-00904: "EMPNOA": 부적합한 식별자

SQL 실행

SELECT EMPNOA FROM EMP ;

원인

1. 테이블에 없는 컬럼을 조회했을 경우 발생합니다.

2. 오탈자가 원인인 경우가 많으니 잘 살펴봅니다.

3. 정상적으로 사용중인 테이블에서 발생하는 경우 컬럼이 DROP 되었을 수 있으니 테이블을 다시 확인해봅니다.

해결

1. 오탈자 확인 후 수정합니다.

2. 컬럼이 DROP 된 경우 ADD , 또는 SELECT 문에서 같이 제외합니다.

오류코드 ( ORA-00942 )

( table or view does not exist )

원인

1. FROM 절에 테이블명이 잘못 입력된 경우

2. 테이블은 존재하지만 해당 스키마에 권한이 없는 경우

   ( GRANT 로 권한을 부여하여 해결합니다. ( GRANT 권한 부여 방법 ) )

해결

대부분의 오류는 이 두개에서 자주 발생하게 되니 오탈자가 없는지 먼저 확인해보시길 바랍니다.

오류코드 ( ORA-01400 )

ORA-01400: NULL을 ("SCOTT"."EMP"."EMPNO") 안에 삽입할 수 없습니다.

SQL 실행

INSERT INTO EMP (ENAME) VALUES ('James Cameron') ;

원인

Primary Key 가 존재하는 테이블인데 PK 에 null 이 입력되어 발생하는 오류입니다.

해결

테이블의 PK 가 EMPNO 로 구성되어 있으므로, 해당컬럼에 값을 입력하여 정상적인 ROW 로 생성합니다.

오류코드 ( ORA-01438 )

ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

SQL 실행

INSERT INTO EMP (EMPNO) VALUES (12345) ;

원인

입력하려는 컬럼의 자릿수가 NUMBER(4,0) 인데 4 자리를 초과하여 입력하여 오류가 발생하였습니다.

해결

예상치 못한 값이 입력되었을 수 있으니 입력하려는 값의 확인이 필요합니다.

오류코드 ( ORA-01555 )

ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 11수에 "_SYSSMU11_1602219420$" 이름으로 된 것이 너무 작습니다

SQL 실행

INSERT INTO EMP
SELECT EMPNO FROM CUSTOMER

원인

대용량 테이블에서 발생할 가능성이 매우 높음.

위의 SQL 실행을 예로 들면

CUSTOMER 테이블을 조회 하여 EMP 테이블에 데이터를 INSERT 하는 SQL 입니다.

SQL 실행시간이 약 10분 걸리는데

그 사이 CUSTOMER 테이블의 데이터가 변경된 경우

오류가 발생할 수 있습니다.

해결

1. INSERT 도중 SELECT 하는 데이터에 변화를 일으키지 않도록 합니다.

2. SQL 튜닝을 통해 실행시간을 줄여 데이터를 입력하도록 합니다.

오류코드 ( ORA-14016 )

ORA-14016: LOCAL 분할영역 인덱스로 강조된 테이블은 분할영역이 되어 있어야 합니다.

( underlying table of a LOCAL partitioned idnex must be partitioned )

SQL 실행

CREATE UNIQUE INDEX PK_TB_USER_INFO ON TB_USER_INFO ( USER_NO )
TABLESPACE USERS LOCAL ;

원인

파티션 테이블이 아닌 일반 테이블에서 INDEX 생성 시 LOCAL 영역으로 지정하려고 하니 오류가 발생하였습니다.

해결

CREATE UNIQUE INDEX PK_TB_USER_INFO ON TB_USER_INFO ( USER_NO )
TABLESPACE USERS ;

오류코드 ( ORA-12899 )

ORA-12899: "SCOTT"."EMP"."ENAME" 열에 대한 값이 너무 큼(실제: 13, 최대값: 10)

SQL 실행

INSERT INTO EMP (EMPNO, ENAME ) VALUES (1111, 'James Cameron') ;

원인

오류메세지를 보면 알 수 있듯 ENAME 의 지정된 컬럼 길이는 VARCHAR2(10 BYTE) 로 설정되어 있으나,

실제입력한 컬럼의 값은 13 BYTE 로 입력되어 오류가 발생하였습니다.

해결

1. 컬럼사이즈를 증가시켜줍니다.

2. 입력되는 값을 줄여서 입력합니다.

오류코드 ( ORA-30926 )

ORA-30926: 원본 테이블의 고정 행 집합을 가져올 수 없습니다

unable to get a stable set of rows in the source tables

SQL 실행

MERGE INTO SCOTT.EMP T1
USING
(
    SELECT DEPTNO
      FROM SCOTT.DEPT
     WHERE DNAME LIKE '%SALES%'
) T2
ON
(T1.DEPTNO = T2.DEPTNO)
WHEN MATCHED THEN
UPDATE
SET T1.SAL = '3000'

원인

일단 말도 안되는 문장이지만 예를 들면 부서가 SALES 라는 명칭이 들어간 부서는 급여를 3000 으로 변경하는 쿼리입니다.

USING 안의 구분에서 실행한 결과가 

위처럼 출력되었다면 데이터가 2건이상 발생하였고

ON 절에서 JOIN 으로 사용하는 DEPTNO 가 2건이상이 발생한 경우 오류가 발생할 수 있습니다.

해결

1. USING 절에서 사용되는 문장이 KEY 당 1건만 조회되도록 SQL 을 작성합니다. ( EX : GROUP or DISTINCT 이용 )

2. ON 절에서 KEY 값으로 JOIN 이 잘 되었는지 확인합니다.

ORACLE RANK

RANK 함수는 일반적으로 매출, 영업, 성적 등의 순위를 표현할 때 사용됩니다.

어렵지 않지만 자주 사용되는 함수이니 사용법을 잘 숙지해두시면

평소 업무도 뚝딱 해결할 수 있으니 같이 공유해요^^

ORACLE 에서 제공하는 RANK 문법은 아래와 같습니다.

RANK 문법

RANK 함수 사용하는 방법은

아래의 EMP 테이블을 예제로 설명드리겠습니다.

잠깐!

간단한 질문과 결과를 올려놓았으니 

잠시 생각하시고 SQL 문을 확인하시면 조금 더 도움이 되실꺼에요!

자 그럼 기본적인 사용법부터 확인해볼까요?

RANK()

1. 직원의 급여가 높은 순서대로 순위를 아래와 같이 조회해보세요.

  ( 항목 추출 : EMPNO, ENAME, SAL, 순위 )

< 결과 >

SELECT EMPNO
     , ENAME
     , SAL
     , RANK() OVER(ORDER BY SAL DESC) AS RK 
  FROM EMP

DENSE_RANK()

2,3번째 row를 보면 FORD 와 SCOTT 은 같은급여를 받고 있습니다.

다음 순위의 급여를 받는 JONES의 순위가 3위가 아니라 4위가 되었습니다.

3위로 출력하고 싶은 경우는 어떻게 할까요?

2. 순위의 공백이 없도록 DENSE_RANK 를 사용하여 조회해보세요.

< 결과 >

SELECT EMPNO
     , ENAME
     , SAL
     , DENSE_RANK() OVER(ORDER BY SAL DESC) AS RK 
  FROM EMP

ROW_NUMBER()

동일한 급여에도 순위가 나뉘도록 조회해보겠습니다.

3. 동일한 순위를 인정하지 않도록 조회해보세요.

< 결과 >

SELECT EMPNO
     , ENAME
     , SAL
     , ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RK 
  FROM EMP

자, 위와 같이 조회하면 어떤 기준으로 2등과 3등이 나뉜지 알 수 없습니다.

"2등과 3등의 차이가 뭐야?" 라고 물어본다면 대답할 수 없으니

우리는 명시적으로 그 차이를 지정하는 기준을 만들어볼께요!

4. 급여는 같지만 입사일이 빠른경우를 상위순위로 배치하도록 조회해보세요.

순위를 산정하는 방식이 2가지 인것을 생각해보면 쉬워요~ ( 급여, 입사일 )

< 결과 >

SELECT EMPNO
     , ENAME
     , SAL
     , HIREDATE
     , DENSE_RANK() OVER(ORDER BY SAL DESC, HIREDATE) AS RK 
  FROM EMP

여기까지 보셨다면 기본적인 RANK 의 사용법을 알게 되었습니다.

아래부터는 RANK 함수를 응용한 내용을 공유해보겠습니다.

RANK 응용1

 - 급여가 가장 높은 사람이 누구인지 1명만 조회하세요.

< 결과 >

SELECT ENAME
  FROM (
        SELECT ENAME
             , RANK() OVER(ORDER BY SAL DESC) RN
          FROM EMP
  ) 
 WHERE RN = 1

위와 같은 결과지만 좀 더 쉽게 조회 할 수 있는 방법도 있습니다.

바로 오라클의 KEEP / FIRST / LAST 문법을 사용하는것인데 MAX 값을 조회 후

다른 컬럼의 값을 바로 조회할 수 있습니다!

SELECT MAX(ENAME) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC) AS ENAME
  FROM EMP

조금 더 자세한 내용을 알고 싶다면 FIRST, LAST, KEEP 포스트를 확인해주세요~ ( 바로확인하기 )

RANK 응용2

 - 총급여가 많은 부서별 순위를 조회하세요.

부서별로 급여의 순위는 단순하게 처리가 가능합니다.

부서로 GROUP BY 후 ORDER BY 에서 지정한 정렬컬럼을 SUM 처리하면 됩니다.

< 결과 >

SELECT T1.DEPTNO
     , T2.DNAME
     , SUM(SAL) AS SAL
     , RANK() OVER(ORDER BY SUM(SAL) DESC) AS DEPT_RANK
  FROM EMP T1
 INNER JOIN DEPT T2
    ON T1.DEPTNO = T2.DEPTNO
 GROUP BY T1.DEPTNO
        , T2.DNAME

RANK 응용3

 - 부서별로 급여를 가장 많이 받는 사람을 조회하세요.

RANK 함수를 사용할 때 PARTITION BY 를 사용하면 해당 컬럼을 파티션으로 묶어 순위를 부여할 수 있습니다.

< 결과 >

SELECT T1.DEPTNO
     , T2.DNAME
     , T1.EMPNO
     , T1.ENAME
     , T1.SAL
     , RANK() OVER(PARTITION BY T1.DEPTNO ORDER BY T1.SAL DESC) AS DEPT_RANK
  FROM EMP T1
 INNER JOIN DEPT T2
    ON T1.DEPTNO = T2.DEPTNO

이상으로 오라클 RANK 함수에 대해 알아보았습니다.

도움이 되셨거나 추가질문이 있다면 댓글 남겨주세요~^^

오라클 KEEP

KEEP 단어 자체의 의미를 보면

"유지하다" 라는 의미를 가지고 있습니다.

오라클에서 사용하는 KEEP 은 단독으로 사용하기 보다는

FIRST , LAST 와 함께 사용하게 되는데

정렬을 통해 첫번째나 마지막에 나온 row 를 KEEP 해놓고 ( 유지 / 보관해놓고 )

해당 row 의 다른 컬럼을 사용한다고 생각하면 쉽게 사용할 수 있습니다.

다만 어떤 기준으로 추출하는지 명시적으로 사용할 수 있는 장점이 있습니다.

잠깐 예를 들어볼까요?

어느 회사에서 "부서별로 가장 높은 급여를 받는 사람이 얼마지?" 라고 하면

단순히 부서별 GROUP BY 후 MAX 값으로 추출하면 되겠죠?

그런데 "부서별로 가장 오래 근무한 사람의 급여가 얼마지?" 라고 하면

부서별 입사일이 가장 빠른 사람을 추출 후 급여를 찾아야 해서 서브쿼리나 다른 방법으로 쿼리를 해야겠죠?

하지만 KEEP 문법을 사용하면 한번에 찾을수 있더라고요!

그럼 샘플 데이터를 통해서 확인해볼께요!

샘플데이터

FIRST 와 LAST 는 비슷하게 사용할 수 있는데

집계함수나 분석함수로 조회된 ROW 에 대해 동작할 수 있습니다.

FIRST나 LAST 를 사용하면

HIREDATE 라는 특정 컬럼을 기준으로 정렬해야 하지만 필요한 값이 HIREDATE 가 아닌 경우

FIRST 나 LAST 함수를 사용하면 SELF JOIN 이나 VIEW 가 필요 없이 더 좋은 성능으로 사용할 수 있습니다.

SELECT DEPTNO
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) AS JOIN_FIRST_SAL
     , MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) AS JOIN_LAST_SAL
  FROM EMP
 GROUP BY DEPTNO

결과

위처럼 입사일(HIREDATE)을 기준으로

입사일이 오래된 순으로 정렬(FIRST)할 것인지

최근 입사한 순으로 정렬(LAST)할 것인지 정렬 후

집계함수(MAX, MIN)를 사용하면 집계함수에서 조회 된

첫번째나 마지막에 출력된 row 에 존재하는 급여(SAL)컬럼을 사용 할 수 있습니다.

CAUTION

하지만, FIRST 나 LAST 를 사용 시 주의 할 점이 있는데

바로, 정렬 시 null 이 존재하는 경우입니다.

정렬을 FIRST 로 입력 시 null 값은 기본적으로 맨 처음에 조회되기 때문에

의도된 바와 다른 결과를 조회할 수 있는데 아래 예를 확인해보겠습니다.

SELECT DEPTNO
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY MGR NULLS FIRST ) AS MGR_NULLS_FST
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY MGR NULLS LAST  ) AS MGR_NULLS_LST
  FROM EMP
 GROUP BY DEPTNO

결과

같은 문법이지만 NULLS FIRST 로 사용했느냐 NULLS LAST 로 사용했느냐에 따라

처리결과가 다르게 조회된 것을 확인할 수 있습니다.

NULLS FIRST 와 NULLS LAST 는 null 값이 존재할 때

null 을 앞에 조회되게 할 지, 뒤에 조회되게 할 지를 명시하는 문장이라고 보시면 됩니다.

같은 사용방법으로 맨 하단 ORDER BY 문장에도 사용할 수 있어요

SELECT *
   FROM EMP
  ORDER BY MGR NULLS LAST>

결과

SELECT *
   FROM EMP
  ORDER BY MGR NULLS FIRST

결과

결과와 같이 MGR 을 기준으로 오름차순으로 정렬되는건 동일하지만

null 의 위치가 어디에 위치하라고 명시해놓는 문법이라고 보시면 됩니다

또한, OVER 절을 사용하면 분석함수로도 사용할 수 있습니다.

부서에서 제일먼저 입사한 직원과 나중에 입사한 직원의 급여가 본인의 급여와 어느정도 차이나는지 비교해보세요.

SELECT DEPTNO, ENAME, SAL, HIREDATE
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO )       AS JOIN_FIRST_SAL   /* 부서내 가장 먼저 입사한 직원의 급여 */
     , MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO )       AS JOIN_LAST_SAL    /* 부서내 최근 입사한 직원의 급여 */
     , SAL - MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO ) AS JOIN_FIRST_DIFF  /* 부서내 가장 먼저 입사한 직원과 본인의 급여 비교 */
     , SAL - MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO ) AS JOIN_LAST_DIFF   /* 부서내 최근 입사한 직원과 본인의 급여 비교 */
  FROM EMP
 ORDER BY DEPTNO, HIREDATE

위의 쿼리는 부서별로 파티션을 나눈 후 최초입사, 최근입사로 정렬 후 급여를 추출했습니다.

입사일로 MAX 와 MIN 을 조회했지만 실질적으로 급여를 추출할 수 있었습니다.

이상 KEEP 문법을 사용하여 쿼리를 간단하게 작성하는법을 확인해보았습니다.

오라클 파티션 테이블 샘플(생성 수정 삭제) 및 주의사항

대용량 데이터를 조회 시 조금이라도 더 빠른 데이터의 접근을 위해서는 

테이블을 파티션으로 분할하여 테이블을 생성할 수가 있습니다.

파티션 테이블 생성

CREATE TABLE TB_SALE  ( 
      SALE_DATE          VARCHAR2(8 BYTE)  NOT NULL ,
      SALE_TIME          VARCHAR2(6 BYTE)  NOT NULL ,
      CUST_NO            VARCHAR2(10 BYTE) NOT NULL ,
      SALE_AMT           NUMBER                     ,
      INPUT_DATE         DATE
)
  TABLESPACE TS_DATA
  PARTITION BY RANGE (SALE_DATE) 
 (
 PARTITION PR_TB_SALE_201801  VALUES LESS THAN ('20180201') , 
 PARTITION PR_TB_SALE_201802  VALUES LESS THAN ('20180301') , 
 PARTITION PR_TB_SALE_201803  VALUES LESS THAN ('20180401') , 
 PARTITION PR_TB_SALE_201804  VALUES LESS THAN ('20180501') , 
 PARTITION PR_TB_SALE_201805  VALUES LESS THAN ('20180601') , 
 PARTITION PR_TB_SALE_201806  VALUES LESS THAN ('20180701') , 
 PARTITION PR_TB_SALE_201807  VALUES LESS THAN ('20180801') , 
 PARTITION PR_TB_SALE_201808  VALUES LESS THAN ('20180901') , 
 PARTITION PR_TB_SALE_201809  VALUES LESS THAN ('20181001') , 
 PARTITION PR_TB_SALE_201810  VALUES LESS THAN ('20181101') , 
 PARTITION PR_TB_SALE_201811  VALUES LESS THAN ('20181201') , 
 PARTITION PR_TB_SALE_201812  VALUES LESS THAN ('20190101') , 
 PARTITION PR_TB_SALE_201901  VALUES LESS THAN ('20190201') , 
 PARTITION PR_TB_SALE_201902  VALUES LESS THAN ('20190301') , 
 PARTITION PR_TB_SALE_201903  VALUES LESS THAN ('20190401') , 
 PARTITION PR_TB_SALE_201904  VALUES LESS THAN ('20190501') , 
 PARTITION PR_TB_SALE_201905  VALUES LESS THAN ('20190601') , 
 PARTITION PR_TB_SALE_201906  VALUES LESS THAN ('20190701') , 
 PARTITION PR_TB_SALE_201907  VALUES LESS THAN ('20190801') , 
 PARTITION PR_TB_SALE_201908  VALUES LESS THAN ('20190901') , 
 PARTITION PR_TB_SALE_201909  VALUES LESS THAN ('20191001') , 
 PARTITION PR_TB_SALE_201910  VALUES LESS THAN ('20191101') , 
 PARTITION PR_TB_SALE_201911  VALUES LESS THAN ('20191201') , 
 PARTITION PR_TB_SALE_201912  VALUES LESS THAN ('20200101') 
-- PARTITION PR_TB_SALE_MAX  VALUES LESS THAN (MAXVALUE) 
 ) 
 ;


CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ;  


ALTER TABLE TB_SALE
  ADD CONSTRAINT PK_TB_SALE PRIMARY KEY (SALE_DATE, SALE_TIME, CUST_NO);


COMMENT ON COLUMN TB_SALE.SALE_DATE IS '매출일자';
COMMENT ON COLUMN TB_SALE.SALE_TIME IS '매출시간';
COMMENT ON COLUMN TB_SALE.CUST_NO   IS '고객번호';
COMMENT ON COLUMN TB_SALE.SALE_AMT  IS '매출 금액';
COMMENT ON TABLE  TB_SALE           IS '고객 매출내역';

파티션 구성 시 마지막에 MAXVALUE 를 주석으로 막은 이유는 

분할해 둔 파티션의 마지막이 임박해오는 경우 SPLIT 보다 파티션 추가가 간편하기 때문입니다!

물론 예기치 않은 데이터가 들어오는 경우 MAXVALUE 를 지정해두는것이 안정적이긴 합니다.

파티션 추가

 ALTER TABLE TB_SALE
 ADD PARTITION PR_TB_SALE_202001 VALUES LESS THAN ('20200201' )
 TABLESPACE TS_INDEX ;

파티션 삭제

ALTER TABLE TB_SALE DROP PARTITION PR_TB_SALE_202001 ;

파티션 분할 (MAXVALUE 파티션 분할)

ALTER TABLE TB_SALE 
SPLIT PARTITION PR_TB_SALE_MAX AT ( '20200201' )
INTO ( PARTITION PR_TB_SALE_202001
     , PARTITION PR_TB_SALE_MAX
     ) ;

MAXVALUE 로 잡혀있던 PR_TB_SALE_MAX 파티션을 2020년 02월 01일 이전 데이터가 입력될 파티션으로 분리하였다.

파티션 TRUNCATE

ALTER TABLE TB_SALE TRUNCATE PARTITION PR_TB_SALE_201801 ;

파티션 테이블 인덱스 생성

CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ; 

파티션 테이블을 생성할 때 INDEX 설정 시 주의할 사항이 있습니다.

예를 들어 위와 같이 RANGE 파티션을 가진 테이블을 월별로 파티션을 분리한다고 했을 때 

테이블 전체에 INDEX 를 구성하는 것 보다 각각의 파티션 별로 INDEX 를 가지고 있어야 

대용량의 테이블을 조회할 때 더 효과적인 속도를 기대할 수 있습니다.

여기서 테이블스페이스의 INDEX 지정 시 LOCAL INDEX 가 아닌 default 로 설정하거나 GLOBAL INDEX 로 설정할 경우

파티션기준이 아닌 전체 테이블을 기준으로 INDEX 가 만들어지니 주의해야 합니다.

파티션 테이블이 GLOBAL INDEX 로 생성될 경우 테이블의 중간중간 데이터가 삭제되거나 변경된다면 

전체 테이블 기준으로 최적화된 INDEX 가 뒤죽박죽 될 수 있으나

LOCAL 로 생성되는 경우에는 다른 파티션의 INDEX 에는 영향이 가지 않으니 

INDEX 를 사용하는 SQL 인 경우 더 빠른 실행계획을 세울 수 있게 됩니다.

궁금한점이나 잘못된 부분이 있으면 댓글 남겨주세요^^

감사합니다.

오라클 MERGE 문 간단한 예제로 한번에 INSERT, UPDATE 처리

UPDATE INSERT 를 동시에 처리하고 싶은 경우가 간혹 존재하는데

이럴 경우 과거 CURSOR 를 사용하거나 UPDATE 를 먼저 처리하고 INSERT 를 처리한 경우가 있습니다.

이제 MERGE 문을 사용하면 SELECT ~ INSERT 시 원하는 조건에 맞는 행은 UPDATE 를, 조건에 맞지 않는 행은 INSERT 를 할 수 있게 되었습니다.

즉,

       이미 존재하는 값은 UPDATE

       신규로 입력된 값은 INSERT

이 처리가 한번에 가능합니다.

그것도 다수의 행을 UPDATE, INSERT 가 가능합니다.

일명 UPSERT 라고도 부르기도 하죠.

오라클에서 제공하는 문법은 아래와 같습니다.

MERGE 문법

이렇게 보시면 복잡하지만 아래예제와 설명을 보면 간단하게 알 수 있습니다!

MERGE INTO 예제

MERGE INTO CUSTOMER C
USING
(
      SELECT USERNO
           , USERNAME
           , ADDRESS
           , PHONE
       FROM NEW_JOIN
      WHERE INPUT_DATE = '20170724'
) N
ON ( C.USERNO = N.USERNO)
WHEN MATCHED THEN
UPDATE
SET C.USERNAME = N.USERNAME
  , C.ADDRESS  = N.ADDRESS
  , C.PHONE    = N.PHONE
WHEN NOT MATCHED THEN
INSERT ( USERNO
       , USERNAME
       , ADDRESS
       , PHONE
       )
 VALUES (
         N.USERNO
       , N.USERNAME
       , N.ADDRESS
       , N.PHONE
 )

위의 간단한 예제는 신규고객이 들어왔을 때 기존 고객테이블에 데이터를 입력하는 업무입니다.

하나씩 문법을 살펴보겠습니다.

MERGE INTO CUSTOMER C : INSERT 또는 UPDATE 할 테이블과 테이블의 ALIAS 를 지정해줍니다.

USING : 원하는 결과를 추출하기 위한 SELECT 문입니다.  이 SELECT 문에서 나온 결과를 INSERT 또는 UPDATE 할 예정입니다.

ON : SELECT 한 결과와 입력하고 싶은 테이블의 UNIQUE 한 값을 매칭하는 연결고리입니다. ( 주로 KEY 값을 사용 )

WHEN MATCHED THEN : SELECT 의 결과가 INSERT 할 테이블에 값이 이미 존재하는 경우 UPDATE 를 실행합니다.

WHEN NOT MATCHED THEN : SELECT 의 결과가 INSERT 할 테이블에 값이 없는 경우 INSERT 를 실행합니다.

업데이트만 하고 싶은 경우에는 아래 WHEN NOT MATCHED THEN 이하 문장을 제거하면 됩니다.

MERGE UPDATE 예제

MERGE INTO CUSTOMER C
USING
(
      SELECT USERNO
           , USERNAME
           , ADDRESS
           , PHONE
       FROM NEW_JOIN
      WHERE INPUT_DATE = '20170724'
) N
ON ( C.USERNO = N.USERNO)
WHEN MATCHED THEN
UPDATE
SET C.USERNAME = N.USERNAME
  , C.ADDRESS  = N.ADDRESS
  , C.PHONE    = N.PHONE

또한 오라클 10g 이후로는 SELECT 한 결과를 UPDATE 뿐 아니라 DELETE 도 처리할 수 있습니다.

MERGE UPDATE DELETE 예제

MERGE INTO CUSTOMER C
USING
(
      SELECT USERNO
           , USERNAME
           , ADDRESS
           , PHONE
       FROM NEW_JOIN
      WHERE INPUT_DATE = '20170724'
) N
ON ( C.USERNO = N.USERNO)
WHEN MATCHED THEN
UPDATE
SET C.USERNAME = N.USERNAME
  , C.ADDRESS  = N.ADDRESS
  , C.PHONE    = N.PHONE
DELETE WHERE SAL >= 3000

이 MERGE 문은 DML 의 문장으로 트랜잭션(TRANSACTION) 단위로 실행되기 때문에 COMMIT, ROLLBACK 을 처리해야 완전한 반영이 됩니다.

이 외에 궁금하신점은 댓글로 문의주시면 답변 달아드리겠습니다.

감사합니다.

오라클 SUBSTR 함수 문법과 예제로 마스터하기

문법

SUBSTR(기준문자, 시작위치, 종료위치);

SUBSTR(기준문자, 시작위치);

예제

SELECT SUBSTR('HELLOSQL', 1, 5 );
------------------------------------------
결과 : HELLO

SUBSTR 함수는 결과의 원하는 문자열 자르기를 하기 위해 시작위치와 종료위치를 입력하여 결과의 일부분을 추출하게됩니다.

일반적으로 SUBSTR 은 문자를 자를 때 사용하게 됩니다.

SUBSTR 사용 규칙

 - 시작위치가 0이면 1로 처리됩니다. 

SELECT SUBSTR('HELLOSQL',0,1) FROM DUAL
SELECT SUBSTR('HELLOSQL',1,1) FROM DUAL
 ------------------------------------------
 결과 : H

 - 시작위치에 양수를 입력하면 문자의 시작부분부터 카운트하여 첫번째 문자를 찾습니다. 

SELECT SUBSTR('HELLOSQL',3,2) FROM DUAL
------------------------------------------
결과 : LL     

 - 시작위치에 음수를 입력하면 문자의 끝부분부터 뒤로 계산하여 첫번째 문자를 찾습니다.

SELECT SUBSTR('HELLOSQL',-3,2) FROM DUAL
------------------------------------------
결과 : SQ         

 - 종료위치가 생략되면, 시작위치 이후의 모든 문자를 리턴합니다.

SELECT SUBSTR('HELLOSQL',3) FROM DUAL
------------------------------------------
결과 : LLOSQL
 

  - substring_length가 1보다 작으면 null을 리턴합니다. 

SELECT SUBSTR('HELLOSQL',3,0) FROM DUAL
------------------------------------------
결과 : NULL

  - 시작위치와 종료위치에 소수점을 입력한 경우 자동으로 정수로 변환됩니다. ( 3.8 -> 3 , 3.1 -> 3 )

SELECT SUBSTR('HELLOSQL',3,3.9) FROM DUAL
SELECT SUBSTR('HELLOSQL',3,3.1) FROM DUAL
------------------------------------------
결과 : LLO

 - SUBSTR 의 첫번째 파라메터에 들어갈 수 있는 데이터타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB 중 1개 타입을 사용할 수 있습니다.

    또한, 결과값은 파라메터에 입력한 타입과 동일한 데이터타입을 리턴합니다.

 - 시작위치와 종료위치에 입력되어야 할 값은 데이터타입이 NUMBER 타입이거나 NUMBER 타입으로 변환될 수 있는 데이터타입으로 입력해야 합니다. ( 즉, 정수값이 입력되어야 합니다. )

SUBSTR 과 비슷하지만 다른 문자형태를 잘라서 사용하고 싶은 경우 아래의 함수를 이용할 수 있습니다. ( 문법동일 )

SUBSTRB : 문자 대신 바이트를 사용

SUBSTRC : 유니코드 완성형 문자를 사용

SUBSTR2 : UCS2 코드포인트를 사용

SUBSTR4 : UCS4 코드포인트를 사용

위의 함수 중 SUBSTRB 는 1~2년에 한두번 사용해보았으나 그 밑의 함수들은 아직 사용해본적이 한번도 없습니다.

♡공감과 댓글은 저에게 더 큰 힘이 됩니다!

( 로그인하지 않아도 공감버튼은 클릭됩니다. )

오라클 다양한 INSERT INTO 방법

INSERT문을 사용하면 테이블에 데이터를 입력할 수가 있습니다.

물론 해당테이블에 INSERT 권한이 있어야 입력이 가능합니다.

이 포스팅에서는 다양한 방법으로 INSERT 처리하는 법을 알아보겠습니다.

INSERT 문법

INSERT INTO dml_table_expression_clause [ t_alias ]
[ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
          [, { expr | DEFAULT } ]...
       )

INSERT 예제

INSERT INTO EMP e ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( '7499','ALLEN','SALESMAN','7698',SYSDATE,'1600','300','30');
INSERT INTO EMP e ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( '7521','WARD' ,'SALESMAN','7698',SYSDATE,'1250','500','30');


INSERT INTO EMP e VALUES ( '7499','ALLEN','SALESMAN','7698',SYSDATE,'1600','300','30');
INSERT INTO EMP e VALUES ( '7521','WARD' ,'SALESMAN','7698',SYSDATE,'1250','500','30');


INSERT INTO EMP e VALUES ( '7369','SMITH','CLERK','7902',SYSDATE,'800',NULL,'20');
INSERT INTO EMP e ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO ) VALUES ( '7369','SMITH','CLERK','7902',SYSDATE,'800','20');

일반적인 INSERT 방법은 위의 문법으로 입력을 하게 됩니다.

아래 두줄의 차이점은 전체 컬럼의 값을 넣느냐 넣지 않느냐로 비교할 수 있는데

5번 라인은 전체 컬럼 기준으로 COMM 컬럼에 NULL 을 명시하여 입력한 CASE 이며

6번 라인은 INSERT 할 컬럼에서 COMM 컬럼을 제외한 컬럼에 직접 값을 입력한 CASE 입니다.

( 동일한 결과를 확인 할 수 있습니다. )

테이블 생성과 데이터 입력을 한번에!

CREATE TABLE SCOTT.EMP_BACKUP AS 
SELECT * 
  FROM SCOTT.EMP
;

또는 테이블 백업 시 데이터도 그대로 넣어야 할 경우도 있습니다.

위와 같이 생성 시 테이블 생성 + 데이터 입력이 동시에 이루어지게 됩니다.

CREATE TABLE SCOTT.EMP_BACKUP AS 
SELECT EMPNO
     , ENAME
     , JOB
     , MGR
     , 2500 AS SAL
     , COMM
     , DEPTNO 
  FROM SCOTT.EMP
 ;

INSERT 와 마찬가지로 데이터는 CREATE TABLE [table_name] AS SELECT 문에서도 원하는 컬럼만 생성하여 컬럼을 생성할 수 있습니다.

이 때, 컬럼의 ALIAS 는 필수입니다!

일반적으로 테이블 백업을 할 때는 위의 방법을 사용할것으로 예상됩니다.

다수의 INSERT 처리

INSERT INTO EMP_BAK
SELECT * FROM EMP
;

INSERT 할 테이블을 지정 후 SELECT 결과를 INSERT 합니다.

INSERT INTO SCOTT.EMP_BAK ( EMPNO, ENAME, JOB, SAL )
 SELECT EMPNO, ENAME, JOB, SUM(SAL)
   FROM SCOTT.EMP
 GROUP BY EMPNO, ENAME, JOB
;

위와 같이 가공한 데이터를 INSERT 할 때 주로 사용합니다.

다수 테이블의 INSERT 처리

INSERT ALL
  INTO EMP_BAK  ( EMPNO, ENAME, JOB )
  INTO EMP_BAK2 ( EMPNO, ENAME, JOB )
  INTO EMP_BAK3 ( EMPNO, ENAME, JOB )
  INTO EMP_BAK4 ( EMPNO, ENAME, JOB )
  INTO EMP_BAK5 ( EMPNO, ENAME, JOB )
SELECT EMPNO, ENAME, JOB 
  FROM EMP
;

사실 다수 테이블에 한번에 INSERT 할 일이 없어서 실제로 써본적은 없지만 이런 방법이 있다는 것만 알아도 될 것 같습니다.

엑셀을 이용한 다수의 INSERT 처리

이건 INSERT 뿐만 아니라 여러방면으로 활용될 수 있는 방법인데

엑셀의 각 열에 데이터를 입력 후 수식을 이용해 문장을 만든 후 INSERT 처리하는 방법입니다.

선행작업 : A B C 열에 데이터를 입력합니다.

-- 수식
="INSERT INTO EMP VALUES('"&A1&"',"&"'"&B1&"',"&"'"&C1&"');"
="INSERT INTO EMP VALUES('"&A2&"',"&"'"&B2&"',"&"'"&C2&"');"
="INSERT INTO EMP VALUES('"&A3&"',"&"'"&B3&"',"&"'"&C3&"');"

-- 값 복사
INSERT INTO EMP VALUES('7369','SMITH','CLERK');
INSERT INTO EMP VALUES('7499','ALLEN','SALESMAN');
INSERT INTO EMP VALUES('7521','WARD','SALESMAN');

값 복사를 통해 위의 결과를 한번에 실행하여 INSERT 가능합니다.

이상으로 테이블 데이터 INSERT 하는 방법에 대해 알아보았습니다.

오라클 LOCK 걸린 개체 확인 및 LOCK 해제

오라클이나 다른 DBMS 제품을 사용하는 중에 LOCK 이 걸려본 경험은 다들 한두번씩 있을거라고 봅니다.

일반적으로 TOAD 나 ORANGE 같은 상용제품을 사용할 때는 모니터링툴이 있으니 바로 열어서 확인해볼수 있지만

SQL Developer 같은 무료제품을 사용할 때에는 어떻게 해야 하는지 알 수 없습니다.

사실 모니터링툴도 다 DBMS 에서 관리하고 있는 시스템인데 SQL 로 조회해보면 됩니다.

자주 사용하는 sql 정도는 몇개 저장해두고 사용하면 편하게 쓸 수 있습니다.

일단 LOCK 걸린 객체를 확인해보겠습니다.

1. lock 걸린 개체 확인

SELECT OBJECT_ID
     , SESSION_ID       -- SID
     , ORACLE_USERNAME
     , OS_USER_NAME
  FROM V$LOCKED_OBJECT
;


OBJECT_ID	SESSION_ID	ORACLE_USERNAME		OS_USER_NAME
-----------------------------------------------------------------
163990		401		SCOTT			js
163966		401		SCOTT			js

이걸로는 자세한 내용을 아직 알기 어렵습니다.

그러면 다음으로 해당 sid 와 serial 번호로 락걸린 object name 을 확인해보겠습니다.

2. 해당 sid 와 serial 번호로 락걸린 object name 을 확인

SELECT A.SID
     , A.SERIAL#
     , object_name
     , A.SID || ', ' || A.SERIAL# AS KILL_TASK
  FROM V$SESSION A
 INNER JOIN V$LOCK B
    ON A.SID = B.SID
 INNER JOIN DBA_OBJECTS C
    ON B.ID1 = C.OBJECT_ID
 WHERE B.TYPE  = 'TM'
 ;
   

SID	SERIAL#	OBJECT_NAME	KILL_TASK
------------------------------------------------------------
401	12761	EMP		401, 12761
401	12761	EMP		401, 12761

자 이제 뭔가 OBJECT_NAME 이 보이면서 이 TABLE이 LOCK 이 걸렸구나 라는것을 확인할 수 있습니다.

그럼 LOCK 이 걸린것은 확인했고 LOCK 을 해제하려면 어떻게 해야 할까요?

정답은 마지막에 SID 와 SERIAL# 로 조회된 NUMBER 로 해당되는 SESSION 을 KILL 시키면 됩니다.

3. sid 와 시리얼 번호로 세션 해제

 ALTER SYSTEM KILL SESSION '401, 12761'
 ;

뒤에 입력된 숫자인 401, 12761 는 위에서 말씀드린 SID와 SERIAL# 에 대한 값입니다.

편하게 KILL_TASK 라는 값을 복사해서 사용할 수 있게 미리 만들어주었습니다.

 ( ALTER 명령은 별도의 COMMIT 이 필요없는거 다들 아시리라 생각합니다. )

이 단계까지 왔으면 LOCK 걸린 테이블의 SESSION 은 해제되었을 것이라고 봅니다.

그런데 위에서 조회된 세션이 단순히 LOCK 걸린것이 아니라 아직 작업중인 SQL 일수도 있다는 생각이 들수도 있습니다.

그럴때는 LOCK 을 발생시킨 SQL 이 뭔지 확인해볼수 있습니다.

4. 락 발생 사용자 및 OBJECT 조회 + 어떤 sql 를 실행중하여 lock 을 걸고 있는지 확인

SELECT DISTINCT T1.SESSION_ID
     , T2.SERIAL#
     , T4.OBJECT_NAME
     , T2.MACHINE
     , T2.TERMINAL
     , T2.PROGRAM
     , T3.ADDRESS
     , T3.PIECE
     , T3.SQL_TEXT
  FROM V$LOCKED_OBJECT T1
     , V$SESSION T2
     , V$SQLTEXT T3
     , DBA_OBJECTS T4
 WHERE 1=1
   AND T1.SESSION_ID = T2.SID
   AND T1.OBJECT_ID = T4.OBJECT_ID
   AND T2.SQL_ADDRESS = T3.ADDRESS
   ORDER BY T3.ADDRESS, T3.PIECE
   ;
   

SESSION_ID	SERIAL#	OBJECT_NAME	MACHINE	    	TERMINAL	PROGRAM		    	ADDRESS		PIECE	SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	0    	INSERT /*+ APPEND */ INTO EMP
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	1    	, EMPNO
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	2    	, EMPNM
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	3    	, DEPTNO

위와 같이 조회되면 맨 우측에 SQL_TEXT 를 조회해보면 어떤 SQL 이 실행되었는지 알 수 있습니다.

이상으로 오라클 TABLE LOCK 조회 및 해제방법에 대해 알아보았습니다.