[오라클] SEQUENCE 시퀀스 생성, 권한, SYNONYM 까지

오라클에서는 자동 증가 컬럼을 사용할 수가 없다.

다른 DB에서는 컬럼 자체에 옵션이 있으나,

오라클에서는 컬럼의 값을 증가시키기 위해서는 MAX(컬럼) + 1 , 채번테이블 또는 시퀀스를 사용하여 일련번호를 부여해야 한다.

오라클 시퀀스 생성 쿼리

• INCREMENT BY : 시퀀스 실행 시 증가시킬 값
• START WITH : 시퀀스의 시작값이다. (MINVALUE과 같거나 커야 한다)
• MINVALUE : 시퀀스가 시작되는 최솟값이다.
• MAXVALUE : 시퀀스가 끝나는 최댓값이다.
• NOCYCLE | CYCLE : NOCYCLE (반복안함), CYCLE(시퀀스의 최댓값에 도달 시 최솟값 1부터 다시시작)
  • 보통 프로젝트에서는 NOCYCLE 을 쓴다.
• NOCACHE | CACHE : NOCACHE(사용안함), CACHE(캐시를 사용하여 미리 값을 할당해 놓아서 속도가 빠르며, 동시 사용자가 많을 경우 유리)
  • CACHE DEFAULT 는 20 인데, 배치작업시 20인 경우 시스템에 부하가 걸려 CACHE 값을 수정해야한다. 따라서 시퀀스 생성 요청을 받았을 때 시퀀스를 사용해서 대량 작업 실행 예정인지 확인해야한다.
• NOORDER | ORDER : NOORDER(사용안함), ORDER(요청 순서로 값을 생성하여 발생 순서를 보장하지만 조금의 시스템 부하가 있음
  • RAC 환경에서 ORDER 을 사용한다면 각 인스턴스에 번호를 물어봐야하므로 NOORDER 사용해야 성능에 문제가 없다. 

1. 증가 시퀀스 예시

CREATE SEQUENCE emp_seq
       INCREMENT BY 1
       START WITH 1
       MINVALUE 1
       MAXVALUE 9999
       NOCYCLE
       NOCACHE
       NOORDER;

위의 시퀀스는 1부터 시작하여 9999까지 1씩 증가하는 시퀀스. 

CACHE
또는
CACHE 100

CACHE를 사용할 경우 CACHE 또는 CACHE "값"으로 지정할 수가 있다. 지정한 값만큼 시퀀스를 미리 생성해 놓는다.

 

2. 감소 시퀀스

CREATE SEQUENCE emp_seq
       INCREMENT BY -1
       START WITH 1000
       MINVALUE 1
       MAXVALUE 1000
       NOCYCLE
       NOCACHE
       NOORDER;

위의 시퀀스는 1000부터 시작하여 1까지 1씩 감소

 

3. 시퀀스 사용, 생성 확인

--생성확인 DBA
SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_NAME = 'OWNER.EMP_SEQ'

SELECT emp_seq.NEXTVAL -- 새로운 채번, 다시 내릴수 없다
     , emp_seq.CURRVAL -- 현재 채번된 숫자. 지금까지 채번된 수를 보여주는 것
  FROM dual

 

보통 INSERT 구문에서 순서를 매길 때 많이 사용한다.

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
     VALUES (emp_seq.NEXTVAL
           , 'TIGER'
           , 'ANALYST'
           , 7566
           , TRUNC(SYSDATE)
           , 3000
           , NULL
           , 20)

 

 

4. 다른 사용자가 사용할 수 있게 시퀀스 권한 주기 

- 시퀀스의 권한에는 CREATE, SELECT 가 있다. 

- CREATE 권한의 경우 SEQUENCE 를 수정할 수 있기 때문에 보통 개발자들에게 이 권한을 주지 않는다. 

- SELECT 권한만 줘보자

 

시퀀스 소유자 혹은 DBA 권한으로 접속 후 

GRANT SELECT [접속사용자.시퀀스명] to [권한을 받을 사용자명] ;
GRANT SELECT OWN.SEQ_1 to BBB ;


-- 만약 변경 권한까지 주고싶다면
GRANT SELECT,ALTER [접속사용자.시퀀스명] to [권한을 받을 사용자명] ;
GRANT SELECT, ALTER OWN.SEQ_1 to BBB ;

 

5. SYNONYM 부여

BBB 계정에서 AAA 계정의 시노님을 쓸것이기 때문에 SYNONUM 을 만들어줘야 한다. 

BBB 계정, 혹은 DBA권한으로 로그인 한 후

CREATE SYNONYM [시퀀스명]  for [권한을 준 사용자.시퀀스명] ;
CREATE SYNONYM SEQ_1  for AAA.SEQ_1 ;
--DBA 권한이라면 PUBLIC 으로도 생성가능
CREATE PUBLIC SYNONYM SEQ_1 FOR AAA.SEQ_1;

 

6. 참고

SYNONYM 은 OBJECT 이다. 

DBA_TABLES 에서는 조회되지 않지만DBA_TAB_PRIVS 에서는 조회가 된다.

 

왜 테이블이 아닌데 DBA_TAB_PRIVS에서는 조회가 되는지 의문이라 사수님에게 여쭤봤더니...

오라클엔 원래 이런게 많단다.. ^^ ㅋㅋㅋㅋ