오라클 DB 용어, 명령문 정리07- 시퀀스, 인덱스, 동의어, 권한, DCL

2019. 05. 27 필기

 

 

 

< 시퀀스 SEQUENCE >

: 정수값을 저장하여 자동 증가되는 값을 제공하는 객체.
  호출될 때마다 중복되지 않은 고유한 숫자를 리턴하는 객체

: 중복되지 않는 기본기 컬럼에 사용할 값을 발생시키는데 주로 사용한다.

 

>> [형식] CREATE SEQUENCE 시퀀스명 [START WITH 초기값] [INCREMENT BY 증가값]

     [MAXVALUE 최대값] [MINVALUE 최소값] [CYCLE] [CACHE 개수];

- START WITH 초기값 : 시퀀스 객체에 저장되는 초기값 설정 -- 생략 시 null
- INCREMENT BY 증가값 :  자동 증가되는 정수값 설정 -- 생략 시 1씩 증가
- MAXVALUE 최대값 : 시퀀스 객체에 저장되는 최대값 설정 -- 생략 시 숫자형 상수 최대표현값
- MINVALUE 최소값 : 시퀀스 객체에 저장되는 최소값 설정 -- 생략 시 1
- CYCLE : 시퀀스 객체에 저장되는 정수값이 최대값을 초과할 경우 최소값부터 다시 제공되도록 반복
- CACHE 개수 : 임시 저장공간에 자동 증가값을 미리 생성하여 제공할 수 있는 개수 설정 -- 생략 시 20개

 

■ 시퀀스 목록 확인

- USER_SEQUENECES  : 현재 접속 사용자의 시퀀스 정보를 제공하는 딕셔너리
- sequence.CURRVAL : 현재 시퀀스값 리턴
- sequence.NEXTVAL  : 시퀀스 객체에 저장된 정수값에 증가값을 더하여 리턴

-- 시퀀스 객체의 저장된 정수값은 증가값이 더해진 값으로 자동 변경
-- 시퀀스 객체에 저장된 정수값이 존재하지 않을 경우 시퀀스 최소값 제공하여 변경

 

■ 시퀀스 변경
>>[형식] ALTER SEQUENCE 시퀀스명 {MAXVALUE | MINVAL | INCREMENT BY} 변경값;

 

■ 시퀀스 삭제
>>[형식] DROP SEQUENCE 시퀀스명;
-- 휴지통으로 가지 않으니 주의!(오라클 휴지통으로 가는 건 테이블 뿐)

 

 

< 인덱스 INDEX >

: 테이블에 저장된 행을 보다 빠르게 검색하기 위한 기능을 제공하는 객체

-- 컬럼에 인덱스를 설정하면 인덱스 영역이 생성되어 컬럼에 대한 행검색 속도 증가
-- 조건식에서 많이 사용하는 컬럼에 설정하며, 행이 많을 경우 인덱스를 사용하면 효율적임
-- 유니크 인덱스와 비유니크 인덱스로 구분

 

■ 유니크 인덱스(UNIQUE INDEX)
: 컬럼에 자동 생성되는 인덱스 (PK 제약조건 또는 UNIQUE 제약조건에 의해 생성)

 

■ 비유니크 인덱스(NON-UNIQUE INDEX)
: 컬럼에 사용자가 수동으로 설정하는 인덱스

 

■ 인덱스 확인 딕셔너리
- USER_INDEXES  : 현재 접속 사용자의 인덱스 정보를 제공
- USER_IND_COLUMNS : 현재 접속 사용자의 컬럼 인덱스 정보를 제공

 

■ 비유니크 인덱스 생성
>>[형식] CREATE INDEX 인덱스명 ON 테이블명(컬럼명);
-- 테이블의 저장행이 많을 경우 조건식에서 많이 사용되는 컬럼에 설정
-- 검색결과가 저장행의 2~4%인 경우 / 조인에서 많이 사용되는 컬럼 / null이 많이 저장된 컬럼에 설정하면 효율적

 

■ 인덱스 삭제
>>[형식] DROP INDEX 인덱스명;
-- 비유니크 인덱스에서만 사용(유니크 인덱스는 제약조건을 제거하면 자동 소멸)

 

< 동의어(SYNONYM) >

 : 오라클 객체에 별명을 부여하여 사용하는 객체
** 대표적인 동의어 : USER_TABLES = TABS /  DUAL  등 외 각종 딕셔너리

 

■ 종류
전용 동의어 : 특정 사용자만 사용할 수 있는 동의어 - 일반 사용자가 생성
공용 동의어 : 모든 사용자가 사용할 수 있는 동의어 - 관리자가 생성

 

■ 동의어 생성 
>>[형식] CREATE [PUBLIC] SYNONYM 동의어 FROM 객체명;
- PUBLIC : 공용 동의어를 생성하기 위해 사용하는 키워드(시스템 권한 부여 필요)

공용동의어 생성 >>[형식] CREATE [PUBLIC] SYNONYM 동의어 FROM 객체명;

시스템 관리자로 접속하여 사용자에게 CREATE SYNONYM 시스템 권한 부여 

SHELL> SQLPLUS /NOLOG 
SQL> CONN SYS/SYS AS SYSDBA 
SQL> GRANT CREATE SYNONYM TO SCOTT;

 

■ 동의어 목록 확인
- USER_SYNONYMS : 현재 접속 사용자에게 동의어 정보를 제공하는 딕셔너리

스키마 (SCHEMA)
: 테이블, 뷰, 시퀀스 등과 같은 객체들의 모음 
-- 오라클에서는 객체를 사용자 스키마(SCHEMA)를 이용하여 저장
-- 다른 사용자의 객체를 표현하고자 할 경우 [스키마명.객체명] 형태로 표현
-- 사용자스키마를 표현하지 않으면 기본적으로 현재 접속 사용자의 스키마의 객체만 접근 가능
-- 스키마에 접근할 수 있는 사용자 권한이 없는 경우 에러 발생 - 관리자는 모든 사용자 스키마 접근 가능

※ 스키마에 대해 이해하는 데 도움이 되었던 페이지
>> https://ykcb.tistory.com/89

■ 동의어 삭제
>>[형식] DROP [PUBLIC] SYNONYM 동의어;

< 권한 부여 >

- 계정(USER) : DBMS 시스템을 사용할 수 있는 허락받은 사용자(객체)
- 계정관리 : 계정 생성 또는 삭제 기능 - 관리자(시스템 권한)

일반 사용자는 계정과 관련된 어 떠 한 명령도 할 수 없다. 오직 관리자만 가능
관리자 명령은 cmd 창에서 합시다

 

■ 계정 생성 
>> SQL> CREATE USER username IDENTIFIED BY password;

■ 계정 목록 확인
>> SQL> SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, CREATED FROM DBA_USERS;
-- DBA_USERS : 관리자에게 사용자 정보를 제공하는 딕셔너리

■ 계정의 암호 변경
>> SQL> ALTER USER username IDENTIFIED BY password;

■ 계정 상태 변경
>> SQL> ALTER USER username ACCOUNT {LOCK|UNLOCK};
: OPEN(접속 가능) 또는 LOCK(접속 불가) 상태로 변경 -- ex) 5회 이상 비밀번호 오류시, 장기휴면멤버 등
-- 오라클 접속시 계정의 암호를 5번 틀리면 자동으로 계정상태가 LOCK 상태로 자동 변경 >> 접속 불가능

■ 계정 삭제
>> SQL> DROP USER username;

 

 

< 데이터 제어어 DCL >

: Data Control Language; 계정에 권한을 부여하거나 회수하는 명령

- 권한(PRIVILEGE) : 시스템 권한(관리자용) 또는 객체 권한(일반사용자용)
- 롤(ROLE) : 시스템 권한들을 그룹화하여 사용하는 기능

 

■ 시스템 권한 부여
>>SQL> GRANT {PRIVILEGE | ROLE} TO username [WITH ADMIN OPTION] [IDENTIFIED BY password];
-- 계정명 대신 PUBLIC 키워드를 사용할 경우 모든 사용자에게 일괄적인 시스템 권한 부여
-- 권한을 부여 받을 계정이 존재하지 않을 경우 자동으로 계정 생성
-- WITH ADMIN OPTION : 부여받은 시스템 권한을 다른 사용자에게 부여, 회수할 수 있는 기능 제공
-- IDENTIFIED BY : 암호 설정. 계정이 생성될 경우 반드시 필요

 

객체권한
: 계정의 객체관련명령 사용권한
-- INSERT, UPDATE, DELETE, SELECT 등의 명령과 관련된 권한
>>SQL> GRANT {ALL | privilege, ...} ON 객체명 TO 계정명 [WITH GRANT OPTION];
-- ALL : 모든 객체 관련 명령 사용 권한
-- WITH GRANT OPTION : 부여받은 객체권한을 다른 사용자에게 부여, 회수할 있는 기능 제공

시스템 권한 부여
>>SQL> GRANT CREATE SESSION TO username;

시스템 권한 회수
>>SQL> REVOKE {privilege | role}, ... FROM username;

객체 권한 회수
>>SQL> GRANT {ALL | privilege, ...} ON 객체명 FROM 계정명;

- TABLESPACE : 사용자 스키마 사용 가능 용량
- USER_TAB_PRIVS_MADE : 현재 접속 사용자의 객체권한을 부여한 정보를 제공
- USER_TAB_PRIVS_RECD : 현재 접속 사용자의 객체권한을 부여 받은 정보를 제공