[sql] 14. DDL 시퀀스(SEQUENCE) | 인덱스(INDEX) | 동의어(SYNONYM) | 사용자(USER)

1. 시퀀스(SEQUENCE)

• 숫자값(정수값)을 저장하여 자동증가되는 값을 제공하는 객체
• 행을 구분하기 위해 사용함

1) 시퀀스 생성

--생략 가능하므로 실제 CREATE SEQUENCE 시퀀스명까지만 사용 많이함
--START WITH 초기값 : 시퀀스에 저장되는 초기값 설정, 생략시 NULL이 저장
--INCREMENT BY 증가값 : 자동 증가되는 숫자값 설정, 생략시 1씩 증가
--MAXVALUE 최대값 : 시퀀스에 저장 가능한 최대값 설정, 생략시 숫자값으로 표현 가능한 최대값으로 설정
--MINVALUE 최소값 : 시퀀스에 저장 가능한 최소값 설정, 생략시 1이 설정
--CYCLE : 시퀀스에 저장값이 최대값을 초과할 경우, 최소값부터 다시 제공되도록 반복하는 기능 제공
--CACHE 갯수 : 임의의 저장공간에 자동 증가값을 미리 생성하여 제공할 수 있는 갯수를 설정하는 기능, 생략시 20이 설정

--🐨형식
CREATE SEQUENCE 시퀀스명
	[START WITH 초기값]
    [INCREMENT BY 증가값]
    [MAXVALUE 최대값] [MINVALUE 최소값]
    [CYCLE]
    [CACHE 갯수]

--테이블과 🐨시퀀스 생성

--1)
--📊 USER2 테이블 생성
-- 회원번호(숫자형 - PK), 회원이름(문자형), 생년월일(날짜형)
CREATE TABLE USER2(NO NUMBER(2) PRIMARY KEY, NAME VARCHAR2(20), BIRTHDAY DATE);
DESC USER2;
--🐨시퀀스 생성
--USER2테이블의 NO 컬럼값으로 저장하기 위한 자동 증가값을 제공하는 시퀀스 생성
CREATE SEQUENCE USER2_SEQ;
//Sequence USER2_SEQ이(가) 생성되었습니다.

--2)
--🖤시퀀스 확인
--📕USER_SEQUENCE : 시퀀스 정보를 제공하는 딕셔너리
SELECT SEQUENCE_NAME, MAX_VALUE, MIN_VALUE , INCREMENT_BY FROM USER_SEQUENCES;

--3)
--🖤시퀀스에 저장된 숫자값 확인
--🖤형식 : 시퀀스명.CURRVAL
--시퀀스에 NULL이 있는 경우 숫자값을 확인하면 에러 발생
SELECT USER2_SEQ.CURRVAL FROM DUAL; --ERROR : 시퀀스에 값이 없어서 에러

--4)
--🖤시퀀스에 저장된 숫자값으로 증가값 제공
-- 증가된 값 제공 후 시퀀스에는 증가된 값이 자동 저장됨
--🖤형식 : 시퀀스명.NEXTVAL
--시퀀스에 NULL이 있는 경우 시퀀스의 최소값을 제공한 후 최소값으로 시퀀스의 저장값 변경 처리
SELECT USER2_SEQ.NEXTVAL FROM DUAL; --검색결과 : 1 - 시퀀스의 저장값은 1로 변경
SELECT USER2_SEQ.CURRVAL FROM DUAL; --검색결과 : 1
SELECT USER2_SEQ.NEXTVAL FROM DUAL; --검색결과 : 2 - 시퀀스의 저장값은 2로 변경
SELECT USER2_SEQ.CURRVAL FROM DUAL; --검색결과 : 2

--5)
--📊 USER2 테이블에 행 삽입
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'홍길동','00/01/01');
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'임꺽정','00/12/31');
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'전우치',SYSDATE);
SELECT * FROM USER2;
COMMIT;

1)

2)

5)

 

 

2) 시퀀스 변경

ALTER SEQUENCE 시퀀스명 {MAXVALUE|MINVALUE|INCREMENT BY} 변경값

--1)
--🐨USER2_SEQ 시퀀스의 최대값을 99로 변경하고 증가값은 5로 변경
SELECT SEQUENCE_NAME, MAX_VALUE, MIN_VALUE, INCREMENT_BY FROM USER_SEQUENCES;
ALTER SEQUENCE USER2_SEQ MAXVALUE 99 INCREMENT BY 5;
//Sequence USER2_SEQ이(가) 변경되었습니다.
SELECT SEQUENCE_NAME, MAX_VALUE, MIN_VALUE, INCREMENT_BY FROM USER_SEQUENCES;



--2)
--🐨USER2 테이블에 행 삽입
SELECT * FROM USER2;
SELECT USER2_SEQ.CURRVAL FROM DUAL; --검색결과: 5
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL, '일지매', '03/09/09');
//1 행 이(가) 삽입되었습니다.
SELECT * FROM USER2;

1)

1)

2)

2)

3) 시퀀스 삭제

DROP SEQUENCE 시퀀스명

--🐨USER2_SEQ 시퀀스 삭제
SELECT SEQUENCE_NAME, MAX_VALUE, MIN_VALUE, INCREMENT_BY FROM USER_SEQUENCES;
DROP SEQUENCE USER2_SEQ;
//Sequence USER2_SEQ이(가) 삭제되었습니다.
SELECT SEQUENCE_NAME, MAX_VALUE, MIN_VALUE, INCREMENT_BY FROM USER_SEQUENCES;

 

 

 

 

 

 

 

 

 

2. 인덱스(INDEX)

• 테이블에 저장된 행을 보다 빠르게 검색하기 위한 기능을 제공하는 객체
• 컬럼에 인덱스를 설정하면 인덱스 영역을 생성하여 컬럼에 대한 행 검색 속도 증가
• 조건식에서 많이 사용하는 컬럼에 설정하며 행이 많을 때 설정하는 것이 효율적
• 모든 컬럼에 인덱스 주는 것은 비효율적..

1) 인덱스의 종류

유니크 인덱스

• (UNIQUE INDEX)
• PRIMARY KEY제약조건이나 UNIQUE제약조건에 의해 자동으로 생성되는 인덱스

비유니크 인덱스

• (NON-UNIQUE INDEX)
• 사용자가 컬럼을 이용하여 수동으로 생성하는 인덱스

2) 인덱스 생성

• 비유니크 인덱스(NON-UNIQUE INDEX) 생성

형식

CREATE INDEX 인덱스명 ON 테이블명(컬럼명)

 

유니크 인덱스 생성

--1)
--USER3 테이블 생성 (PK,UNIQUE 제약조건) - 유니크 인덱스 자동 생성
--회원번호(숫자형-PRIMARY KEY), 회원이름(문자형), 이메일(문자형-UNIQUE)
--PRIMARY KEY 제약조건이나 UNIQUE 제약조건에 의해 유니크 인덱스 자동 생성
CREATE TABLE USER3(NO NUMBER(4) CONSTRAINT USER3_NO_PK PRIMARY KEY, NAME VARCHAR2(20), EMAIL VARCHAR2(50) CONSTRAINT USER3_EMAIL_UK UNIQUE);
//Table USER3이(가) 생성되었습니다.

--2)
--USER3테이블 제약조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER3';

--3)
-- USER3테이블 인덱스 확인
-- 📕USER_INDEXES : 인덱스 정보를 제공하는 딕셔너리,
-- 📕USER_IND_COLUMNS : 컬럼인덱스 정보를 제공하는 딕셔너리
--유니크 인덱스의 이름은 제약조건 이름과 동일
SELECT C.INDEX_NAME, COLUMN_NAME, UNIQUENESS FROM USER_INDEXES I JOIN USER_IND_COLUMNS C ON I.INDEX_NAME = C.INDEX_NAME WHERE C.TABLE_NAME='USER3';

2)

3)

 

 

비유니크 인덱스 생성

--1)
--USER3테이블의 NAME 컬럼을 이용하여 인덱스 생성
CREATE INDEX USER3_NAME_INDEX ON USER3(NAME);
//Index USER3_NAME_INDEX이(가) 생성되었습니다.


--2)
--USER3 테이블 인덱스 확인
SELECT C.INDEX_NAME, COLUMN_NAME, UNIQUENESS FROM USER_INDEXES I JOIN USER_IND_COLUMNS C ON I.INDEX_NAME = C.INDEX_NAME WHERE C.TABLE_NAME='USER3';

2)

3) 인덱스 삭제

• 비유니크 인덱스(NON-UNIQUE INDEX) 삭제

형식

DROP INDEX 인덱스명

 

유니크 인덱스 삭제

--1)
--USER3 테이블의 "EMAIL 컬럼"에 대한 "인덱스" 삭제 - ERROR
--유니크 인덱스는 PRIMARY KEY제약조건이나 UNIQUE제약조건을 삭제하면 같이 삭제됨
--인덱스명 = UNIQUE제약조건명
DROP INDEX USER3_EMAIL_UK; --ERROR, 유니크 인덱스를 삭제할 경우 에러 발생
//ORA-02429: 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다.


--2)
--USER3 테이블의 "EMAIL 컬럼"에 설정된 "유니크 제약조건" 삭제 - OK
ALTER TABLE USER3 DROP CONSTRAINT USER3_EMAIL_UK;
//Table USER3이(가) 변경되었습니다.

--3)
--USER3 테이블의 제약조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER3';

--4)
--USER3 테이블의 인덱스 확인
SELECT C.INDEX_NAME, COLUMN_NAME, UNIQUENESS FROM USER_INDEXES I JOIN USER_IND_COLUMNS C ON I.INDEX_NAME = C.INDEX_NAME WHERE C.TABLE_NAME='USER3';

3)

4)

비유니크 인덱스 삭제

--1)
--USER3 테이블의 "NAME 컬럼"에 대한 "인덱스" 삭제 - OK
DROP INDEX USER3_NAME_INDEX;
//Index USER3_NAME_INDEX이(가) 삭제되었습니다.


--2)
--USER3 테이블의 인덱스 확인
SELECT C.INDEX_NAME, COLUMN_NAME, UNIQUENESS FROM USER_INDEXES I JOIN USER_IND_COLUMNS C ON I.INDEX_NAME = C.INDEX_NAME WHERE C.TABLE_NAME='USER3';

2)

 

 

 

 

 

 

 

 

3. 동의어(SYNONYM)

• 오라클 객체를 다른 이름으로 설정하여 사용하기 위한 객체

1) 동의어의 종류

전용 동의어

• 특정 사용자만 사용할 수 있는 동의어
• 일반 사용자에 의해 관리

공용 동의어

• 모든 사용자가 사용할 수 있는 동의어
• 관리자에 의해 관리

2) 동의어 생성 - CREATE

• PUBLIC : 공용 동의어를 생성하기 위한 키워드

CREATE [PUBLIC] SYNONYM 동의어 FOR 객체명

 

공용동의어

• 테이블 목록 확인 시 사용하는 동의어인 TABS는 공용동의어이다.

-- 📕USER_TABLES 딕셔너리 이용
-- 📕USER_TABLES 딕셔너리 : SYS 계정이 사용하는 뷰
-- => 원래는 📕USER_TABLES딕셔너리는 SYS만 사용할 수 있지만, SYS가 우리에게 접근권한을 줬기 때문에 사용 가능
-- => 또한 원칙적으로는 다른 계정에서 사용하려면 '사용자가 가진'을 표현해줘야함
-- => SYS.USER_TABLES (사용자, 사용자스키마)

-- 🍀다른 사용자의 테이블이나 뷰에 접근하는 방법 : 사용자명.테이블명 또는 사용자명.뷰이름
SELECT TABLE_NAME FROM SYS.USER_TABLES;

-- 💛공용동의어 이용 [USER_TABLES] [TABS]
--SYS.USER_TABLES 객체의 동의어로 USER_TABLES 생성하여 제공
SELECT TABLE_NAME FROM USER_TABLES;
--SYS.USER_TABLES 객체의 동의어로 TABS 생성하여 제공
SELECT TABLE_NAME FROM TABS;

전용동의어

--1)
-- 💛전용동의어 생성 - ERROR
--COMM 테이블에 대한 접속 사용자만 사용 가능한 전용 동의어로 BONUS 생성
--현재 접속 사용자는 동의어에 대한 시스템 권한이 없으므로 동의어를 생성할경우 에러 발생
--관리자에게 동의어에 대한 시스템 권한 요청해야함
CREATE SYNONYM BONUS FOR COMM; --ERROR, 권한 불충분

--2)
-- 🔥시스템 관리자로 접속하여 'SCOTT'에게 동의어에 대한 시스템 권한 부여
GRANT CREATE SYNONYM TO SCOTT;
//Grant을(를) 성공했습니다.

--3)
-- 💛전용동의어 생성 - OK
--시스템 관리자에게 동의어에 대한 시스템 권한을 부여 받은 후 동의어 관련 명령 사용 가능
CREATE SYNONYM BONUS FOR COMM;
//Synonym BONUS이(가) 생성되었습니다.
SELECT * FROM COMM; --같은 결과값이 나옴
SELECT * FROM BONUS; --같은 결과값이 나옴

3) 동의어 목록 확인

--📕USER_SYSNONYMS : 동의어 관련 정보를 제공하는 딕셔너리
SELECT TABLE_NAME,SYNONYM_NAME,TABLE_OWNER FROM USER_SYNONYMS WHERE TABLE_NAME='COMM';

4) 동의어 삭제 - DROP

DROP [PUBLIC] SYNONYM 동의어

--💛전용 동의어 BONUS 삭제

--1)
DROP SYNONYM BONUS;
//Synonym BONUS이(가) 삭제되었습니다.
SELECT TABLE_NAME,SYNONYM_NAME,TABLE_OWNER FROM USER_SYNONYMS WHERE TABLE_NAME='COMM';

--2)
SELECT * FROM COMM;

--3)
SELECT * FROM BONUS; --ERROR, 테이블이 없음

 

 

 

 

 

 

 

 

4. 사용자(USER)

• 시스템(DBMS)을 사용할 수 있는 객체
• 계정(ACCOUNT) : 권한을 가진 사용자
• 계정 관리는 시스템 관리자(SYSDBA - SYS 계정)만 가능
• 즉, ADMINN계정의 워크시트에서 실행할 수 있음
• SCOTT는 계정만들기, 계정삭제, 계정변경.. 등 사용 못함!!
• 오라클을 처음 설치하면 관리자 계정(SYS)만 만들어짐
• 관리자계정은 계정을 관리할 뿐 객체(테이블)을 만들지는 않음
• 객체를 관리하는 계정은 일반계정이 가능하므로, 일반계정을 생성해줘야함

1) 계정생성 - CREATE

--🖤형식
CREATE USER 계정명 IDENTIFIED BY 암호

--🍊관리자 세션에서 작업

--1)
--계정 생성
--KIM 계정 생성 - 관리자 세션에서 작업
--오라클 12C 버전이상에서는 계정 관리하기 전에 세션에 대한 환경설정 변경이 필요함
--변경하지 않으면 계정 생성과 삭제 시 계정명 앞에 C##를 붙여야함
ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;
//Session이(가) 변경되었습니다.
CREATE USER KIM IDENTIFIED BY 1234;
//User KIM이(가) 생성되었습니다.


--2)
--계정 확인
--📕DBA_USERS : 사용자 정보를 제공하는 딕셔너리
--딕셔너리 앞에 DBA 붙으면 관리자만 볼 수 있음
--딕셔너리 앞에 USER 붙으면 누구나 볼 수 있음
--USERNAME : 계정이름
--ACCOUNT_STATUS : 계정 상태( 활성화(OPEN) - 접속가능 , 비활성화(LOCKED) - 접속불가능 )
--DEFAULT_TABLESPACE : 테이블을 만들어줄 수 있는 작업 공간
--CREATED : 계정이 만들어진 날짜

--계정확인 - 관리자 세션에서 작업 (일반사용자는 볼 수 없는 딕셔너리임)
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS WHERE USERNAME='KIM';


--3)
--계정 비밀번호 변경
--계정의 비밀번호는 기본적으로 180일 유효기간으로 설정
ALTER USER 계정명 IDENTIFIED BY 암호
--KIM계정의 비밀번호 변경 - 관리자 세션에서 작업
ALTER USER KIM IDENTIFIED BY 5678;
//User KIM이(가) 변경되었습니다.

--🍊SQLPLUS프로그램에서 만든 계정으로 로그인해보기


--계정은 만들어졌지만, 아직 권한이 없기 때문에 로그인 되지 않음

 

 

2) 계정 상태 변경 - ALTER

• OPEN (계정 활성화 - 접속 가능)
• LOCK (계정 비활성화 - 접속 불가능)
• 오라클 접속시 계정의 비밀번호를 5번 틀리면 계정의 상태가 **자동으로 비활성화(LOCK)**로 변경되어 접속 불가능

--🖤형식
ALTER USER 계정명 ACCOUNT {LOCK|UNLOCK}

--🍊관리자 세션에서 작업


--1)
--🍊계정 상태 변경 - (LOCK)
--KIM계정을 비활성화(LOCK) 상태로 변경 - 관리자 세션에서 작업
ALTER USER KIM ACCOUNT LOCK;
//User KIM이(가) 변경되었습니다.
SELECT USERNAME,ACCOUNT_STATUS,DEdFAULT_TABLESPACE,CREATED
FROM DBA_USERS WHERE USERNAME='KIM';



--2)
--🍊계정 상태 변경 - (UNLOCK)
ALTER USER KIM ACCOUNT UNLOCK;
//User KIM이(가) 변경되었습니다.
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED
FROM DBA_USERS WHERE USERNAME='KIM';

1)

2)

3) 계정 테이블스페이스 변경 - ALTER

• 테이블스페이스(TABLESPACE)
  • 데이타베이스 객체(테이블,뷰,시퀀스,인덱스 등)가 저장되는 영역
  • 테이블스페이스가 있어야지 사용자의 작업공간에 대한 제한 가능!
  • 너는 이만큼만 쓸 수 있어!! 크기 제한 가능
• 오라클에는 기본적으로 SYSTEM 테이블스페이스와 USERS 테이블스페이스가 제공됨
  • SYSTEM 테이블스페이스 : 용량에 대한 제한 불가능 , 관리자가 사용
  • USERS 테이블스페이스 : 용량에 대한 제한 가능 , 일반사용자가 자신의 객체를 저장하는 작업 공간
• 우리가 사용하는 EXPRESS EDITION은 테이블 스페이스를 늘릴 수 없다!!!
• 일반적으로 USERS 테이블스페이스 이용함

--🖤형식
ALTER USER 계정명 DEFAULT TABLESPACE 테이블스페이스명

--🍊관리자 세션에서 작업



--1)
--🍊계정의 기본 테이블스페이스를 SYSTEM로 변경
--KIM 계정의 기본스페이스를 SYSTEM로 변경 - 관리자 세션에서 작업
ALTER USER KIM DEFAULT TABLESPACE SYSTEM;
//User KIM이(가) 변경되었습니다.
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED
FROM DBA_USERS WHERE USERNAME='KIM';


--2)
--🍊계정의 기본 테이블스페이스를 USERS로 변경
--KIM 계정의 기본스페이스를 USERS로 변경 - 관리자 세션에서 작업
ALTER USER KIM DEFAULT TABLESPACE USERS;
//User KIM이(가) 변경되었습니다.
SELECT USERNAME,ACCOUNT_STATUS,DEdFAULT_TABLESPACE,CREATED
FROM DBA_USERS WHERE USERNAME='KIM';

1)

2)

4) 계정의 테이블스페이스에 대한 물리적 저장크기 변경 - ALTER

• 용량 제한
• 사용자가 제한크기만큼 가질 수 있도록 제한
• 사용자가 가진 테이블스페이스를 통해 너는 이만큼만! 너는 이만큼만! 제한함

--🖤형식
ALTER USER 계정명 QUOTA 제한크기 ON 테이블스페이스명

--🍊관리자 세션에서 작업


--1)
--🍊 저장크기를 무제한으로 변경
--KIM계정에 물리적 저장크기를 무제한으로 변경 - 관리자 세션에서 작업
ALTER USER KIM QUOTA UNLIMITED ON USERS;
//User KIM이(가) 변경되었습니다.

--2)
--제약용량 확인
--📕DBA_TS_QUOTAS : 테이블스페이스의 물리적 저장공간에 대한 관련 정보를 제공하는 딕셔너리
SELECT TABLESPACE_NAME, USERNAME, MAX_BYTES FROM DBA_TS_QUOTAS;

--3)
--🍊 저장크기를 20MBYTE로 변경
--KIM계정에 물리적 저장크기를 20MBYTE로 변경 - 관리자 세션에서 작업
ALTER USER KIM QUOTA 20M ON USERS;
//User KIM이(가) 변경되었습니다.
SELECT TABLESPACE_NAME, USERNAME, MAX_BYTES FROM DBA_TS_QUOTAS;

2)

3)

5) 계정삭제 - DROP

--🖤형식
DROP USER 계정명

--🍊관리자 세션에서 작업



--1)
-- KIM 계정 삭제
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED
FROM DBA_USERS WHERE USERNAME='KIM';


--2)
--KIM 계정 삭제 - 관리자 세션에서 작업
DROP USER KIM;
//User KIM이(가) 삭제되었습니다.
--SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED
FROM DBA_USERS WHERE USERNAME='KIM';

1)

2)