[6/25] 인라인뷰와 시퀀스, 권한 부여

--단순 뷰 생성
CREATE VIEW EMP_VIEW01 AS SELECT EMPNO,ENAME, JOB, SAL HIREDATE, SAL FROM EMP;

--EMP_COPY테이블을 모든 사용자에 대한 접근 권한 미부여
--인사부 사원에게는 EMP_VIEW01 뷰에 대한 접근 권한 부여
SELECT * FROM EMP_VIEW01;--뷰를 생성하면 사용자에따라 주고싶은 데이터를 정해 줄 수 있음

 

📌 CREATE OR REPLACE VIEW

• 기존의 뷰를 제거하고 새로운 뷰를 생성하도록 한다.

--EMP_VIEW30 뷰에 SAL컬럼 추가 - 뷰 구조 변경 불가능 : 기존 뷰를 제거하고 새로운 뷰 생성
--CREATE OR REPLACE 명령으로 기존 뷰를 제거하고 새로운 뷰를 생성
CREATE OR REPLACE VIEW EMP_VIEW30 AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP_COPY WHERE DEPTNO = 30;

 

📌 WITH CHECK옵션

• 뷰를 만들때 썼던 조건식의 커럼값을 절대 바꿀 수 없도록 검사하는 기능
• 즉, 이 옵션을 사용하며 뷰를 생성한 조건식의 컬럼값을 변경할 경우 에러발생

CREATE OR REPLACE VIEW EMP_VIEW10
    AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP_COPY WHERE DEPTNO = 10;
SELECT * FROM EMP_VIEW10;

--EMP_VIEW10 뷰에서 사원번호가 7782인 사원의 부서번호를 20으로 변경
UPDATE EMP_VIEW10 SET DEPTNO = 20 WHERE EMPNO = 7782;

--롤백처리
ROLLBACK;

--EMP_VIEW10 뷰를 생성할때 WITH CHECK 옵션을 주도록한다.--기존의 뷰는 삭제
CREATE OR REPLACE VIEW EMP_VIEW10
    AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP_COPY WHERE DEPTNO = 10 WITH CHECK OPTION;
    
--EMP_VIEW10 뷰에서 사원번호가 7782인 사원의 부서번호를 20으로 변경
UPDATE EMP_VIEW10 SET DEPTNO = 20 WHERE EMPNO = 7782;

 

📌 WITH READ ONLY

• 복합뷰를 원래 읽기만 가능하지만 단순뷰는 아니므로 읽기 전용으로 만들어 주기 위한 기능
• 이 기능을 사용하면 해당 뷰에서 삽입, 삭제 , 변경 명령을 사용할 경우 ERROR가 발생한다.

--WITH READ ONLY기능 추가
CREATE OR REPLACE VIEW EMP_VIEW20
    AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP_COPY WHERE DEPTNO = 20 WITH READ ONLY;
    
--사원번호가 7369인 사원 삭제
DELETE FROM EMP_VIEW20 WHERE EMPNO = 7369; --불가능

 

📌 DROP VIEW

• 테이블을 삭제해도 관련뷰는 삭제되지 않는다.
• 단, 삭제된 테이블 관련 뷰를 사용할 경우 ERROR가 발생한다.

--뷰삭제
DROP VIEW EMP_VIEW;
--연관 뷰 검색
SELECT * FROM VIEW EMP_VIEW30; -- INVLID TABLE 이 없다고 ERROR뜸

 

📌ROWNUM

• 검색행에 순차적으로 행번호를 제공하는 키워드
• 정렬이 제일 나중에 된다
• 따라서 검색행의 행번호를 제공한 후 정렬함으로, 차례대로 행번호를 제공하지 못한다.
• WHERE구문에서 ROWNUM 키워드 사용가능하다

--ROWNUM : 검색행에 순차적으로 행번호를 제공하는 키워드
SELECT ROWNUM, EMPNO, ENAME, SAL FROM EMP;

--EMP테이블에서 모든 사원을 급여로 내림차순으로 검색
--검색행의 행번호를 제공한 후 정렬함으로, 차례대로 행번호 미제공
SELECT ROWNUM, EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC;

 

📌ROW_NUMBER()

• 이 함수를 이용하면 행번호(순위)를 검색행에 제공한다
• 하지만 WHERE과 함께 사용하면 WHERE보다 더 늦게 검색이 되기 때문에 조건절과 함께 사용할 수 없음

--ROW_NUMBER 함수를 이용하여 행번호(순위)를 검색행에 제공 - OVER함수와 함께 사용해야함
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) ,EMPNO, ENAME, SAL FROM EMP ;

---ROW_NUMBER 함수를 이용하여 행번호(순위)를 검색행에 제공
--5등까지만 검색 => 행번호가 5보다 작은 행만 검색되도록 조건식을 설정 -> 컬럼을 찾을 수 없어 ERROR발생됨
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) NUM ,EMPNO, ENAME, SAL FROM EMP WHERE NUM <=5; --WHERE보다 NUM이 나중에 검색되기 때문

 

📌해결방법

: 내가 원하는 조건으로 행번호를 붙여 검색하고 싶다면 뷰(또는 인라인뷰)를 이용하면 된다.

1) 뷰를 만들어 뷰와 ROWNUM을 함께 사용

2) 또는 인라인뷰를 만들어 ROWNUM을 함께 사용

--EMP테이블에서 모든 사원의 사원번호, 사원이름, 급여로 내림차순으로 정렬하여 EMP_VIEW생성
CREATE OR REPLACE VIEW EMP_VIEW AS SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC;
SELECT * FROM EMP_VIEW;

--EMP_VIEW에 행번호를 제공하여 검색 
SELECT ROWNUM, EMPNO, ENAME, SAL FROM EMP_VIEW;
SELECT ROWNUM, * FROM EMP_VIEW; --불가능
SELECT ROWNUM, EMP_VIEW.* FROM EMP_VIEW; --테이블(뷰)의 모든 컬럼을 검색하고자 할 경우, 테이블(뷰).*로 표현가능

--EMP_VIEW 뷰에 행번호를 제공하여 검색하되, 행번호가 5보다 작은 행 검색
SELECT ROWNUM, EMP_VIEW.* FROM EMP_VIEW WHERE ROWNUM <= 5;

 

📌INLINEVIEW -> 게시판에 많이 사용되니 잘 알아두도록 하자

• 뷰를 직접적으로 만드는것이 아니라, SELECT 명령에서 FROM 구문에 서브쿼리를 사용하여 일시적으로 생성되어 사용하기 위한 키워드
• 일종의 가상의 테이블을 일시적으로 만든다고 생각하면 됨.
• 인라인에 존재하지 않는 컬럼을 사용할 경우 ERROR발생
• 인라인뷰에도 별칭을 부여할 수 있다.
• 일반사용자도 접근할 수 있도록 인라인뷰를 더 자주 사용하기 때문에 잘 알아두자.

--inline view : select 명령에서 from 구문에 서브쿼리를 사용하여 일시적으로 생성되어 사용하는 
SELECT EMPNO, ENAME, SAL FROM EMP;
SELECT EMPNO, ENAME, SAL FROM (SELECT EMPNO, ENAME, SAL FROM EMP);
--INLINE에 존재하지 않는 컬럼을 사용할 경우 에러발생
SELECT EMPNO, ENAME, SAL, DEPTNO FROM (SELECT EMPNO, ENAME, SAL FROM EMP);
SELECT EMPNO, ENAME, SAL FROM (SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO =10);
SELECT EMPNO, ENAME FROM (SELECT * FROM EMP);
 
--EMP테이블에서 모든 사원의 사원번호 사원이름, 급여를 급여를 내림차순으로 
SELECT ROWNUM, DEPTNO, ENAME, SAL FROM EMP ORDER BY SAL DESC;
 
--INLINE뷰를 이용하여 검색 후 ROWNUM 키워드를 사용
SELECT ROWNUM , EMPNO, ENAME, SAL FROM (SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC);
 
--TABLE ALIAS기능을 이용하여 INLINE VIEW에 별칭을 부여하여 사용
SELECT ROWNUM , TEMP.* FROM (SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC) TEMP;--뷰를 따로 생성하지 않아도됨.
 
--행번호를 제공하여 검색하되 행번호가 5보다 작은 행 검색 -> 조건식을 사용할 수 있다.
SELECT ROWNUM , TEMP.* FROM (SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC) TEMP WHERE ROWNUM < 5;--뷰를 따로 생성하지 않아도됨.
 
--행번호 제공하여 검색하되 행번호가 6부터 10범위의 행검색
--WHERE 구문에서 ROWNUM키워드의 조건식은 작다(<) OR 작거나 같다(<=) 연산자만 가능
SELECT ROWNUM , TEMP.* FROM (SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC) TEMP WHERE ROWNUM BETWEEN 6 AND 10;--검색안됨
SELECT ROWNUM , TEMP.* FROM (SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC) TEMP WHERE ROWNUM = 10;--검색안됨
--해결방법⬇
--행번호를 COLUMN ALIAS 처리된 INLINEVIEW를 이용하여 행번호에 대한 조건식 사용가능 - 인라인 뷰를 두번 사용
SELECT * FROM (SELECT ROWNUM RN, TEMP.* FROM (SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC) TEMP)WHERE RN = 10;
--행번호가 6부터 10범위의 행만 검색할 수 있도록
SELECT * FROM (SELECT ROWNUM RN, TEMP.* FROM (SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC) TEMP)WHERE RN BETWEEN 6 AND 10;
 
--EMP테이블에서 모든 사원의 사원번호, 사원이름, 검색행 직전행의 사원번호 사원이름 검색행 직후 행의 사원번호와 사원이름을 검색
--사원번호, 사원이름을 사원번호로 오름차순 정렬하여 검색
--단, 직전또는 직후 사원이 없는 경우 사원번호는 0, 사원이름은 NULL로 검색
SELECT EMPNO, ENAME, LAG(EMPNO, 1,0) OVER (ORDER BY EMPNO) BEFORE_EMPNO, 
                    LAG(ENAME,1,NULL) OVER(ORDER BY EMPNO) BEFORE_ENAME,
                   LEAD(EMPNO,1,0) OVER(ORDER BY EMPNO) AFTER_EMPNO , LEAD(ENAME,1,NULL) OVER(ORDER BY EMPNO) AFTER_ENAME FROM EMP;
 
--사원번호가 7844인 사원의 사원번호, 사원이름...
--WHERE구문에 의해 행 검색 후 검색대상을 제공하므로 이전행과 이후 행이 미존재..
SELECT EMPNO, ENAME, LAG(EMPNO, 1,0) OVER (ORDER BY EMPNO) BEFORE_EMPNO, 
                    LAG(ENAME,1,NULL) OVER(ORDER BY EMPNO) BEFORE_ENAME,
                   LEAD(EMPNO,1,0) OVER(ORDER BY EMPNO) AFTER_EMPNO , LEAD(ENAME,1,NULL) OVER(ORDER BY EMPNO) AFTER_ENAME FROM EMP WHERE EMPNO = 7844;
                   --FROM을 다 실행하고 나서WHERE검색을 해야 정확한 검색이 가능함
                   --인라인뷰로 만들어 주면 됨.
                   
--직전행,이전행 테이블을 먼저 만들고, 그테이블에서 WHERE을 검색해주세요~ 
--INLINE VIEW로 모든 사원을 검색한 후 원하는 사원에 대한 검색결과 제공
SELECT * FROM (SELECT EMPNO, ENAME, LAG(EMPNO, 1,0) OVER (ORDER BY EMPNO) BEFORE_EMPNO, 
LAG(ENAME,1,NULL) OVER(ORDER BY EMPNO) BEFORE_ENAME,
LEAD(EMPNO,1,0) OVER(ORDER BY EMPNO) AFTER_EMPNO , LEAD(ENAME,1,NULL) OVER(ORDER BY EMPNO) AFTER_ENAME FROM EMP) WHERE EMPNO = 7844;

• 웹사이트를 만들때 이전게시글, 다음게시글 기능을 구현할때 위의 쿼리에서 젤 마지막 쿼리를 자주 사용하기 때문에 잘 알아두자.

---

📌 시퀀스(SEQUENCE)

• 정수값을 저장하여 자동 증가되는 값을 제공하는 객체(고유값)
• 게시판에서는 글번호 <- 여기에 사용됨
• 형식 ) CREATE SEQUENCE 시퀀스명 (START WITH 초기값) (INCREMENT  BY 증가값)(MAXVALUE 최대값) (MINVALUE 최소값) (CYCLE) (CACHE갯수)
• START WITH 초기값 : 시퀀스 객체에 저장된 초기값 설정 - 생략 : NULL
• INCREMENT  BY 증가값 : 자동 증가되는 정수값을 설정 - 생략한다면 1씩 증가로 된다.
• MAXVALUE 최대값 : 시퀀스 객체에 저장될 수 있는 최대값 설정 - 생략 : 숫자형에 대한 최대값
• MINVALUE 최소값 : 시퀀스 객체에 저장될 수 있는 최소값 설정 - 생략 : 1
• CYCLE : 시퀀스의 저장값이 최대값을 초과할 경우, 최소값부터 다시 제공되도록 반복하는 기능을 제공하는 옵션
• CACHE 갯수 :  임시 저장공간에 자동증가값을 미리 생성하여 제공할 수 있는 갯수 설정 - 생략 : 20개

1) 테이블 생성(예제를 위해)

CREATE TABLE USER2(
    NO NUMBER(2) PRIMARY KEY,
    NAME VARCHAR2(20),
    BIRTHDAY DATE
);

 

2) 테이블의 NO컬럼값으로 저장하기 위해 시퀀스 객체를 생성

CREATE SEQUENCE USER2_SEQ;

 

3) 시퀀스 확인

SELECT SEQUENCE_NAME, MAX_VALUE, MIN_VALUE, INCREMENT_BY FROM USER_SEQUENCES;

 

4) 시퀀스에 저장된 정수값을 확인

• 시퀀스명.CURRVAL
• 시퀀스에 저장된 정수값이 없는 경우 에러발생
• 이럴땐, NEXTVAL을 해주면 됨
• 현재저장된 값에 증가된값을 주는 키워드 -> 증가된 값이 계속 시쿼스에 저장이되고
• 계속 증가하여 최대값까지 가질 수 있다.

SELECT USER2_SEQ.CURRVAL FROM DUAL; --에러

 

5) 시퀀스 저장값에 증가값을 더하여 제공하기 위한 표현식

• 시퀀스에 저장된 값은 증가값이 더해진 값으로 자동변경됨
• 시퀀스에 저장된 값이 없는 경우, 시퀀스의 최소값을 제공하며 시퀀스 저장값을 최소값으로 변경한다.

SELECT USER2_SEQ.NEXTVAL FROM DUAL;--1이 검색됨
SELECT USER2_SEQ.CURRVAL FROM DUAL; --이제 잘 실행됨
--위의 두개 명령을 번갈에서 계속 실행하면 값이 1씩 계속 증가하게 된다.

--USER2테이블에 행을 삽입 : 시퀀스 저장값을 이용하여 NO컬럽값으로 저장
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL, '홍길동', '00/01/01');
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL, '임꺽정', '00/03/11');
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL, '장길산', '00/12/01');
SELECT * FROM USER2;
COMMIT;

 

6) 시퀀스 변경

• alter sequence 시퀀스 명 (maxvalue | minvalue | increment by) 변경값

--USER2_SEQ 시퀀스 최대값을 99 증가값을 5로 변경
ALTER SEQUENCE USER2_SEQ MAXVALUE 99 INCREMENT BY 5;
SELECT SEQUENCE_NAME, MAX_VALUE, MIN_VALUE, INCREMENT_BY FROM USER_SEQUENCES;

 

7) 시퀀스 삭제

• DROP SEQUENCE 시퀀스명

DROP SEQUENCE USER2_SEQ;

 

---

📌 인덱스(INDEX)

• 테이블에 저장된 행을 보다 빠르게 검색하기 위한 기능을 제공하는 객체
• 컬럼에 인덱스를 설정하면 인덱스 영역을 생성하여 컬럼에대한 행검색 속도를 증가시킬 수 있다. <- 인덱스를 사용하는 가장 큰 이유
• 조건식에 많이 사용하는 컬럼에 설정하며 행이 많은 경우 설정하는 것이 효율적이다.
• 테이블의 데이터양이 많을때 유용하기 때문에 평소에는 자주 쓰지 않는다.
• 인덱스 종류

1)유니크 인덱스 : PRIMAYKEY 또는 UNIQUE제약조건에 의해 자동생성하는 인덱스

2)비유니크 인덱스 : 사용자가 수동으로 테이블 컬럼에 생성하는 인덱스

 

1) 인덱스 확인

• USER_INDEXES : 인덱스 정보를 제공하는 딕셔너리
• USER_IND_COLUMN : 컬럼인덱스 정보를 제공하는 딕셔너리
• 둘이 조인해서 사용

SELECT C.INDEX_NAME, COLUMN_NAME, UNIQUENESS, INDEX_TYPE FROM USER_INDEXES I
    JOIN USER_IND_COLUMNS C ON I.INDEX_NAME = C.INDEX_NAME WHERE C.TABLE_NAME = 'USER3'; 

 

2) 인덱스 생성

• CREATE INDEX 인덱스명 ON 테이블명(컬럼명)
• 저장행이 많은 테이블의 조건식에서 많이 사용되는 컬럼에 설정
• 검색결과가 저장행의 2%~4%인 경우, 또는 조인 조건으로 많이 사용되는 컬럼에 설정하면 됨
• NULL이 많이 저장된 컬럼에 설정하는 것이 효율적임

--USER3 테이블에 NAME컬럼에 인덱스를 생성하여 설정
CREATE INDEX USER3_NAME_INDEX ON USER3(NAME);

SELECT C.INDEX_NAME, COLUMN_NAME, UNIQUENESS, INDEX_TYPE FROM USER_INDEXES I
    JOIN USER_IND_COLUMNS C ON I.INDEX_NAME = C.INDEX_NAME WHERE C.TABLE_NAME = 'USER3'; 

3) 인덱스 삭제

• DROP INDEX 인덱스 명
• 유니크 인덱스는 제약조건을 제거하면 자동삭제된다.

--USER3이메일 컬럼에 인덱스를 삭제(유니크 인덱스)
ALTER TABLE USER3 DROP CONSTRAINT USER3_EMAIL_UK;
 --USER3 NAME컬럼의 인덱스 삭제
 DROP INDEX USER3_NAME_INDEX;

---

 

📌 동의어(SYNONYM)

• 오라클 객체에 별명을 부여하여 사용하는 객체
• 좀 더 쉽게 객체를 사용(표현)할 수 있도록 하기 위해 사용한다.
• 전용 동의어 : 특정 사용자만 사용할 수 잇는 동의어 - 일반 사용자가 생성하여 관리
• 공용동의어 : 모든 사용자가 사용할 수 있는 동의어 - 관리자가 생성하여 관리

1) 동의어 생성

• 형식) CREATE [PUBLIC] SYNONYM 동의어 FOR 객체명
• PUBLIC : 공용동의어를 생성하기 위한 키워드

 

2)테이블 목록확인

SELECT TABLE_NAME FROM USER_TABLES;

--USER_TABLES 딕셔너리 대신 TABS 동의어를 제공하여 사용 - 공용동의어
SELECT TABLE_NAME FROM TABS;

 

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

• SHELL>>SQLPLUS /NOLOG
• SQL> CONN SYS/SYS AS SYSDBA
• SQL> GRANT CREATE SYNONYM TO SCOTT;
• 이제 생성가능

CREATE SYNONYM BONUS FOR COMM;
SELECT * FROM BONUS;

 

4) 동의어 확인 : USER_SYNONYMS -> 동의어 정보를 제공하는 딕셔너리

SELECT TABLE_NAME, SYNONYM_NAME, TABLE_OWNER FROM USER_SYNONYMS WHERE TABLE_NAME = 'COMM';

 

5)공용 동의어 생성

• 오라클은 사용자의 외부스키마(SCHENA)를 이용하여 접근
• 외부스키마가 생략된 경우 현재 접속 사용자의 스키마로 표현
• 외부 스키마(사용자이름) 사용권한이 있는 객체에 접근하고자 할 경우 [외부스키마.객체명] 형식으로 접근

--SQL> SELECT EMPNO, ENAME, SAL FROM SCOTT.EMP;
--SCOTT사용자의 EMP테이블에 SCOTT_EMP 공용동의어 생성
--SQL> CREATE PUBLIC SYNONYM SCOTT_EMP FOR SCOTT.EMP;

 

6) 공용동의어를 사용하여 검색

SELECT * FROM SCOTT_EMP;--동의어이기 때문에 사용가능하다.

 

7) 대표적인 공용동의어 : DUAL또는 딕셔너리

SELECT SYSDATE FROM SYS.DUAL;
SELECT SYSDATE FROM DUAL;--위랑 아래 둘다 사용가능
SELECT OWNER,TABLE_NAME, SYNONYM_NAME,TABLE_OWNER FROM ALL_SYNONYMS WHERE TABLE_NAME = 'DUAL';
SELECT TABLE_NAME FROM SYS.USER_TABLES;

 

8) 동의어 삭제

--형식) DROP [PUBLIC] SYNONYM 동의어
--SCOTT_EMP 공용동의어 삭제
--SQL>DROP PUBLIC SYNONYM SCOTT_EMP 

SELECT OWNER,TABLE_NAME, SYNONYM_NAME,TABLE_OWNER FROM ALL_SYNONYMS WHERE TABLE_NAME = 'SCOTT_EMP'; --제거된것을 볼 수 있음
SELECT * FROM SCOTT_EMP; --DROP됐으므로 에러
--현재 접속 사용자의 BONUS전용 동의어 삭제
DROP SYNONYM BONUS;

---

📌 사용자 객체->ALTER USER를 더 중요

• DBMS서버에 접속하여 사용할 수 있는 허락받은 사용자(또는 계정)
• 계정관리(계정생성 또는 삭제)는 관리자만 가능하다
• ALTER USER를 이용하여 사용자의 암호,상태,테이블스페이스 변경이 가능하다.

1) 계정생성

• 형식) CREATE USER 계정명 IDENTIFIED BY 암호
• SHELL>>SQLPLUS /NOLOG
• SQL> CONN SYS/SYS AS SYSDBA //관리자로 접속하여
• SQL>CREATE USER KIM IDENTIFIED BY 1234 //KIM유저를 생성
• 만든 계정을 확인하기 위해서는  DBA_USERS 딕셔너리를 확인하면 된다.
• SQL> SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, CREATED FROM DBA_USERS WHERE USERNAME = 'KIM';

2) 계정의 암호를변경★★★

• 형식) ALTER USER 계정명 IDENTIFIED BY 변경암호

 

3) 계정의 상태를 변경

• OPEN(DBMS 접속가능) 또는 LOCK상태(DBMS 접속 불가능)
• 오라클 접속시, 계정의 암호를 5번 틀리면 계정상태가 자동으로 LOCK상태로 변경되어 접속이 불가능해진다
• 형식) ALTER USER 계정명 ACCOUNT (LOCK | UNLOCK)
• SQL> ALTER USER KIM ACCOUNT LOCK;
• SQL> ALTER USER KIM ACCOUNT UNLOCK;

잠긴 상태의 사용자

 

---

 

📌 테이블 공간(TABLE SPACE)

• 테이블 스페이스 : 데이터베이스 객체(테이블, 인덱스 등)에 의해 실제 데이터를 저장하는 공간

1) 계정의 테이블 스페이스를 변경

• 형식) ALTER USER 계정명 DEFAULT TABLESPACE 테이블스페이스명

 

2)계정의 물리적 저장크기에 대한 제한 변경

• 형식) ALTER USER 계정명 AUOTA 제한크기 ON 테이블스페이스명
• SQL> ALTER USER KIM QUOTA UNLIMITED ON USERS; // KIM계정의 테이블스페이스를 무제한으로 변경

 

3)계정삭제

• 형식) DROP USER 계정명
• SQL> DROP USER KIM;

 

4)DCL(DATA CONTRL LANGUAGE) : 데이터 제어어

• 계정에게 권한을 부여하거나 회수하는 명령
• 오라클 권한(PRIVILEGE) : 시스템 권한(관리자) 또는 객체 권한(일반 사용자)
• 롤(ROLE) : 시스템 권한들을 그룹화하여 사용하는 기능

 

5) 시스템권한 부여 

• 형식) GRANT {PRIVILEGE|ROLE},... TO 계정명 [WITH ADMIN OPTION] [IDENTIFIED BY 비밀번호] 
• 계정명 대신 PUBLIC 키워드를 사용할 경우 모든 사용자에게 일괄적으로 시스템 권한 부여
• 권한을 부여할 계정이 존재하지 않는 경우 자동으로 계정 생성
• 계정이 자동으로 생성될 경우 반드시 IDENTIFIED BY 구문으로 비밀번호 설정
• WITH ADMIN OPTION : 부여 받은 시스템 권한을 다른 사용자에게 부여하거나 회수하는 기능 제공

--KIM 계정 생성 : 관리자
SHELL>SQLPLUS /NOLOG
SQL>CONN SYS/SYS AS SYSDBA
SQL>CREATE USER KIM IDENTIFIED BY 1234;

--KIM 계정으로 오라클 접속
SHELL>SQLPLUS /NOLOG
--KIM 계정에게 CREATE SESSION 권한이 존재하지 않아 접속 거부
SQL>CONN KIM/1234

--관리자가 KIM 계정에게 CREATE SESSION 시스템 권한 부여
SQL>GRANT CREATE SESSION TO KIM;

--CREATE SESSION 시스템 권한 부여 후 KIM 계정으로 오라클 접속 가능
SQL>CONN KIM/1234

--KIM 계정으로 SAWON 테이블 생성 : 사원번호(숫자형-PRIMARY KEY),사원이름(문자형),급여(숫자형)
--KIM 계정에게 CREATE TABLE 시스템 권한이 존재하지 않아 테이블 관련 명령을 사용할 경우 에러 발생
SQL>CREATE TABLE SAWON(NO NUMBER(4) PRIMARY KEY,NAME VARCHAR2(20),PAY NUMBER);

--관리자가 KIM 계정에게 CREATE TABLE 시스템 권한 부여
SQL>GRANT CREATE TABLE TO KIM;
--관리자가 KIM 계정의 테이블스페이스와 제한용량 변경
SQL>ALTER USER KIM DEFAULT TABLESPACE USERS;
SQL>ALTER USER KIM QUOTA 50M ON USERS;

--CREATE TABLE 권한 및 테이블스페이스와 제한용량 변경 후 KIM 계정으로 SAWON 테이블 생성
SQL>CREATE TABLE SAWON(NO NUMBER(4) PRIMARY KEY,NAME VARCHAR2(20),PAY NUMBER);