개발자를 위한 인덱스 생성과 SQL 작성 노하우 : 22-24

 

- 오라클 에러 메시지

 

1. "ORA-00001: 유일성 제약조건에 위배됩니다"

테이블에 PK가 있거나 UNIQUE INDEX가 있을 때, 중복되게 INSERT 하면 발생한다. 

 

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

개발자의 오타에 의한 경우가 가장 흔하지만, 실제로 테이블이 생성됐는지 확인이 필요하다. 또한 권한이 없을 때 발생하는 경우도 존재한다. 권한이 없는 경우에는 권한을 요청하거나 직접 권한을 부여할 수 있다.

GRANT SELECT, INSERT, UPDATE, DELETE ON [테이블명] TO [유저명]

 

마지막으로 대소문자 사용에 의한 문제일 수 있다. 오라클은 대소문자 구분이 없으며 자동으로 테이블이 대문자로 생성되지만, 따옴표로 감싸서 소문자로 테이블을 생성할 수 있다.

CREATE TABLE AAA      -- 대문자 테이블 생성 AAA
CREATE TABLE aaa       -- 대문자 테이블 생성 AAA
CREATE TABLE 'AAA'    -- 대문자 테이블 생성 AAA
CREATE TABLE 'aaa'     -- 소문자 테이블 생성 aaa

 

3. "ORA-00904: 열명이 부적합합니다"

존재하지 않는 컬럼명을 쿼리 구문에 사용할 경우에 발생하는 에러 메시지다.

 

4. "ORA-01017: 유효하지 않는 사용자/패스워드에 의한 접근을 제한합니다"

오라클 접속 시 사용자 ID나 패스워드가 일칯하지 않아서 발생한다. 간혹 TNS(Transparent Network Substrate) 정보가 틀린 경우에도 발생한다.

 

5. "ORA-01722: 수차기 부적합합니다."

INSERT 혹은 UPDATE 시에 컬럼의 타입에 맞지 않는 값을 입력할 때 발생한다. 컬럼의 타입 전환 시에 문제가 발생한다.

 

6. "ORA-01555: 스냅샷이 너무 오래 됐습니다(롤백 세그먼트가 작습니다)"

사용자가 필요로 하는 롤백 세그먼트의 정보가 다른 트랜잭션에 의해 오버라이트돼 존재하지 않을 때 발생한다. 롤백 세그먼트가 너무 작다는 의미로, 롤백 세그먼트를 크게 하는 것으로 쉽게 해결할 수 있다. 또한 대용량 데이터 처리시에 빈번한 COMMIT 사용을 자제하거나, 처리를 한가한 시간대로 돌리는 것으로 효과를 낼 수 있다. 무거운 배치 쿼리의 실행에서도 발생하는데, 이때는 크기 조정보다는 튜닝하는 것이 더 우선책이 될 수 있다.

 

7. "ORA-00911: 문자가 부적합합니다"

대부분 쿼리 구문의 끝에 세미콜론(;)을 사용해서 발생하는 경우가 많다.

 

8. "ORA-12541: 리스너가 존재하지 않습니다"

오라클의 리스너는 네트워크를 통해 클라이언트에서 오라클 서버로 접근하는 것을 관리하는 기능을 수행한다. 원격 데이터베이스 서버에 접근하기 위해서는 원격 서버에 리스너가 구동돼 있어야 한다. 주로 리스너가 구동돼 있지 않았을 때에 발생한다. 리스너를 구동시키면 된다.

명령 프롬프트 > Lsnrctl
LSNRCTL > status
LSNRCTL > stop
LSNRCTL > start

 

리스너 로그 파일이 너무 커져서 문제가 발생하는 경우도 있는데, 리스너 로그 파일을 옮기고 새로 생성하거나 아예 만들지 않게 설정하면 된다.

 

9. "ORA-03113: 통신 채널에 EOF가 있습니다"

서버의 고장이나 네트워크가 불안정할 때 주로 발생하는데, 다량의 데이터를 인서트하거나 업데이트할 때 발생하기도 한다. 이 에러의 원인은 명확하지 않고 포괄적으로 발생해 적절한 대처가 어려운 경우가 많다.

 

10. "ORA-01476: 제수가 0 입니다"

쿼리 구문의 나눗셈에서 분모가 0일 때 발생한다.

SELECT 0 / 1 FROM DUAL    -- 에러가 발생하지 않는다.
SELECT 1 / 0 FROM DUAL    -- 에러가 발생한다.

 

해결

SELECT CASE WHEN 분모가 = 0 THEN 0 ELSE 분자 / 분모 END FROM ...
SELECT DECODE(분모, 0, 0, 분자/분모) FROM ...
SELECT NVL(분자 / DECODE(분모, 0, NULL, 분모), 0) FROM ...

 

 

 

- 오라클에서 달력

 

1. 오라클에서의 날짜 끝이 존재한다. 아래 쿼리를 실행하면,

SELECT TO_DATE('99991231', 'YYYYMMDD') + 1 FROM DUAL

------------------------------

ORA-01841

 

날짜의 범위를 넘어섰기 때문에 에러가 발생한다.

 

2. 오라클에서 날짜의 처음을 테스트해보면

SELECT TO_DATE('00010101', 'YYYYMMDD) - 1 FROM DUAL

-------------------------------

0000/12/31 00:00:00

 

0001년 01월 01일에서 하루를 뺀 쿼리를 실행하면 0000년 12월 31일의 결과가 리턴된다. 0000년 12월 31일이 실제로 존재하는지 아래 쿼리를 실행하면,

SELECT TO_DATE('00001231', 'YYYYMMDD) FROM DUAL

-------------------------------

ORA-01841

 

오라클 에러가 발생하다. 

 

3. 1582.10.05 ~ 1582.10.14 기간은 제거된 기간인데, 오라클에서 어떻게 인식하는지 확인해보면

 

SELECT TO_DATE('15821004', 'YYYYMMDD') + 1 FROM DUAL

-------------------------------

1582/10/15 00:00:00

 

SELECT TO_DATE('15821011', 'YYYYMMDD') FROM DUAL

-------------------------------

1582/10/15 00:00:00

 

 사라진 기간에 대해 조회하면 1582년 10월 15일로 나타난다.

 

4. 윤년인지 평년인지 조회하는 쿼리

윤년은 4년으로 나누어지면 되는데, 단 1582년 이후에 100년으로 나누어지는 경우는 평년이고, 400년으로 나누어지면 윤년이다.

SELECT DECODE(CASE WHEN MOD(연도, 4) = 0 THEN 1 ELSE 0 END
                             + CASE WHEN MOD(연도, 100) = 0 AND 연도 > '1582' THEN -1 ELSE 0 END
                             + CASE WHEN MOD(연도, 400) = 0 AND 연도 > '1582' THEN 1 ELSE 0 END,
                                1, 'Y', 'N') AS 윤년
FROM ( SELECT '조회하려는 연도' AS 연도 FROM DUAL)

 

5. 요일 구하기

(연번호 + 월번호 + 일번호) / 7 = 요일번호

 

 

- 랜덤 함수와 사용자 정의 함수

 

오라클 랜덤 패키지 DBMS_RANDOM : 랜덤값을 제공하는 함수 기능을 포함

 

1. 1-10 사이의 값을 추출

SELECT DBMS_RANDOM.VALUE(1, 10) FROM DUAL
----------------------------
3.56757192841058651735493361019736900258

 

실행할 때마다 다른 값을 리턴하는데, 소수점 이하 값을 포함하는 1에서 10사이의 값을 리턴한다.

 

2. 6자리 번호 추출

SELECT ROUND(DBMS_RANDOM.VALUE(100000, 999999), 0) FROM DUAL
----------------------------
731132

 

100000-999999 사이의 값을 리턴하는데 ROUND함수를 사용해 소수점 이하 값을 제외한 6자리 숫자를 랜덤하게 리턴한다.

 

3. 랜덤 문자 추출

SELECT DBMS_RANDOM.STRING('U', 10) FROM DUAL   -- 대문자 10자리
SELECT DBMS_RANDOM.STRING('L', 10) FROM DUAL   -- 소문자 10자리
SELECT DBMS_RANDOM.STRING('A', 10) FROM DUAL   -- 대소문자 10자리
SELECT DBMS_RANDOM.STRING('X', 10) FROM DUAL   -- 대소문자 및 숫자 10자리
SELECT DBMS_RANDOM.STRING('P', 10) FROM DUAL   -- 대소문자 및 특수문자

 

사용자 정의 함수

SELECT REVERS('ABCDEFG') FROM DUAL
--------------------------------------
GFEDCBA

SELECT REVERS('12345') FROM DUAL
--------------------------------------
54321

SELECT REVERS(12345) FROM DUAL   -- 숫자형인 경우 오라클 에러 발생
SELECT REVERS('한글') FROM DUAL   -- 한글인 경우 오라클 에러 발생
SELECT REVERS('ㅎㄱ') FROM DUAL   -- 한글 자모인 경우 오라클 에러 발생

 

위 예시처럼 REVERSE함수가 한글에 대한 역순 문자열을 리턴하지 않는 경우, 사용자 정의 함수를 만들어서 사용할 수 있다. 사용자 정의 함수는 오라클이 기본적으로 제공하지 않는 함수를 필요에 따라 만들어서 사용한다. 사용자 정의 함수는 호출 시 별도의 실행 엔진에서 구동되기 때문에 부하가 존재한다.

 

숫자인 경우 1을 리턴하고 아닌 경우 0을 리턴하는 사용자 정의 함수

CREATE OR REPLACE FUNCTION ISNUMBERIC ( P_NUM IN VARCHAR2) RETURN NUMBER
AS
    V_NUM NUMBER;
BEGIN
    V_NUM := TO_NUMBER(P_NUM);
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END;