DDL (데이터 정의어) -2

woom·2022년 10월 26일

oracle

ORACLE

목록 보기

11/13

🌼 2. 뷰 (VIEW)

테이블을 기반으로 만들어지는 가상의 테이블 (1.단순뷰와 2.복합뷰로 구분)
뷰는 테이블의 행검색 또는 테이블 권한 설정을 간편하게 사용하기 위한 기능을 제공

단순뷰 : 하나의 테이블을 기반으로 생성되는 뷰
- 검색뿐만 아니라 뷰를 이용한 테이블의 행 삽입, 변경, 삭제 가능
- 단순뷰 생성시 그룹함수 또는 DISTINCT 키워드를 사용한 경우 검색만 가능
복합뷰 : 다수의 테이블을 기반으로 생성되는 뷰 (테이블 결합을 기반으로 생성하는 뷰)
- 검색만 가능

🌻 뷰 생성 (서브쿼리 이용)

형식) CREATE [OR REPLACE][{FORCE|NOFORCE}] VIEW 뷰이름[(컬럼명,컬럼명,...)] AS SELECT 검색대상,검색대상,... FROM 테이블명 [WHERE 조건식][WITH CHECK OPTION] [WITH READ ONLY]
서브쿼리의 검색결과를 이용하여 뷰 생성
- CREATE OR REPLACE : 동일한 이름의 뷰가 존재할 경우 기존 뷰를 삭제하고 새로운 뷰 생성
- FORCE : 서브쿼리의 검색결과가 없어도 강제로 뷰를 생성하기 위한 기능 제공
- NOFORCE : 검색결과가 없으면 뷰를 생성하지 않음 (기본값)
- WITH CHECK OPTION : 뷰를 생성한 서브쿼리의 조건식에서 사용된 컬럼에 저장된 값을 변경하지 못하도록 설정하는 기능 제공
- WITH READ ONLY : 검색만 가능하도록 설정하는 기능 제공(단순뷰)
현재 접속 사용자는 뷰에 대한 시스템 권한이 없으므로 뷰를 생성할 경우 에러 발생
- 관리자(SYS)에게 뷰에 대한 시스템 권한 요청
- 시스템 관리자(SYSDBA -SYS계정)로 접속하여 현재 접속 사용자(SCOTT)에게 뷰에 대한 시스템 권한 부여 → GRANT CREATE VIEW TO SCOTT; (관리자 워크시트에 명령 실행)


--EMP 테이블에 저장된 모든 사원을 검색하여 EMP_COPY 테이블을 생성하고 검색행을 삽입 처리
CREATE TABLE EMP_COPY AS SELECT * FROM EMP;
SELECT * FROM EMP_COPY;

--EMP_COPY 테이블에서 부서번호가 30인 사원번호,사원이름,부서번호를 검색하여 EMP_VIEW30 뷰 생성
--현재 접속 사용자는 뷰에 대한 시스템 권한이 없으므로 뷰를 생성할 경우 에러 발생
--관리자에게 뷰에 대한 시스템 권한 요청
CREATE VIEW EMP_VIEW30 AS SELECT EMPNO,ENAME,DEPTNO FROM EMP_COPY WHERE DEPTNO=30;--에러

--시스템 관리자(SYSDBA -SYS계정)로 접속하여 현재 접속 사용자(SCOTT)에게 뷰에 대한 시스템 권한 부여
--GRANT CREATE VIEW TO SCOTT;

🐣 예제1 - 단순뷰

--시스템 관리자에게 뷰에 대한 시스템 권한을 부여 받은 후 뷰 관련 명령 사용 가능

--예제1
--단순뷰 생성
CREATE VIEW EMP_VIEW30 AS SELECT EMPNO,ENAME,DEPTNO FROM EMP_COPY WHERE DEPTNO=30;

--뷰 목록 확인 - USER_VIEWS : 뷰 정보를 제공하는 딕셔너리 
SELECT VIEW_NAME,TEXT FROM USER_VIEWS;--TEXT : 서브쿼리 내용 저장

--뷰 검색 : 테이블에 저장된 행을 이용하여 검색
SELECT * FROM EMP_VIEW30;

--단순뷰에 행 삽입 : 테이블에 행을 삽입 처리 (뷰에 없는 컬럼에는 컬럼 기본값 전달되어 삽입)
INSERT INTO EMP_VIEW30 VALUES(1111,'홍길동',30);
SELECT * FROM EMP_VIEW30; --뷰에 삽입된 행 확인
SELECT * FROM EMP_COPY; --테이블에 삽입된 행 확인
COMMIT;



--예제2
--EMP_COPY 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,입사일,급여를 검색하여
--EMP_VIEW01 뷰 생성
CREATE VIEW EMP_VIEW01 AS SELECT EMPNO,ENAME,JOB,HIREDATE,SAL FROM EMP_COPY;

--EMP_COPY 테이블에서 업무가 SALESMAN인 사원의 사원번호,사원이름,성과급를 검색하여 
--EMP_VIEW02 뷰 생성
CREATE VIEW EMP_VIEW02 AS SELECT EMPNO,ENAME,COMM FROM EMP_COPY WHERE JOB='SALESMAN';

💡 뷰 생성 이점

뷰를 이용하여 테이블 관련 권한 설정을 하지 않고 필요한 정보를 제공
EMP_COPY 테이블에 접근 권한을 없애고 뷰를 이용해 EMP_COPY 테이블의 저장된 행에서
필요한 정보만 검색하여 제공
- 인사부에 근무하는 사원은 EMP_VIEW01 뷰에 접근 가능한 권한 제공하여 필요한 정보 검색
- 영업부에 근무하는 사원은 EMP_VIEW02 뷰에 접근 가능한 권한 제공하여 필요한 정보 검색
사용자에게 보여주는 정보를 선택적으로 제공

🐣 예제2 - 복합뷰


--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 부서번호와 DEPT 테이블의 부서번호가 같은 행을 서로 결합하여 검색
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색하여 
--EMP_VIEW 뷰 생성 - 복합뷰
CREATE VIEW EMP_VIEW AS SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT * FROM EMP_VIEW;--뷰를 이용하여 두개이상의 테이블에서 원하는 행의 결합결과 검색 



--EMP_VIEW30 뷰에 SAL 컬럼 추가 : 뷰 속성 변경 불가능 (기존 뷰를 삭제하고 새로운 뷰 생성)
--CREATE OR REPLACE VIEW  명령을 사용하여 기존 뷰를 삭제하고 새로운 뷰 생성 가능
CREATE OR REPLACE VIEW EMP_VIEW30 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY
WHERE DEPTNO=30;
SELECT VIEW_NAME,TEXT FROM USER_VIEWS; --변경된 서브쿼리 내용 확인
SELECT * FROM EMP_VIEW30;



--EMP_COPY 테이블에서 부서번호가 10인 사원의 사원번호,사원이름,급여,부서번호를 검색하여
--EMP_VIEW10 뷰 생성
CREATE OR REPLACE VIEW EMP_VIEW10 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY
WHERE DEPTNO=10;

--EMP_VIEW10 뷰에서 사원번호가 7782인 사원의 부서번호를 20으로 변경 
--EMP_COPY 테이블에 행의 컬럼값 변경
UPDATE EMP_VIEW10 SET DEPTNO=20 WHERE EMPNO=7782;
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY; -- 변경된 컬럼값 확인
SELECT * FROM EMP_VIEW10;--테이블에 저장된 컬럼값이 변경되어 뷰에서는 미검색

--롤백 처리
ROLLBACK;
SELECT * FROM EMP_VIEW10; -- 부서번호를 변경하기 전으로 돌아감

🐣 예제3 - 기능 추가

WITH CHECK OPTION : 뷰를 생성한 서브쿼리의 조건식에서 사용된 컬럼에 저장된 값을 변경하지 못하도록 설정하는 기능 제공
WITH READ ONLY : 검색만 가능하도록 설정하는 기능 제공(단순뷰)

--예제1
--EMP_COPY 테이블에서 부서번호가 10인 사원의 사원번호,사원이름,급여,부서번호를 검색하여
--EMP_VIEW10 뷰를 생성하고 EMP_VIEW10 뷰에 WITH CHECK OPTION 기능 추가
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으로 변경
--WITH CHECK OPTION 기능에 의해 서브쿼리의 조건식에서 사용한 컬럼값을 변경할 경우 에러 발생
UPDATE EMP_VIEW10 SET DEPTNO=20 WHERE EMPNO=7782;--에러



--예제2
--EMP_COPY 테이블에서 부서번호가 20인 사원의 사원번호,사원이름,급여,부서번호를 검색하여
--EMP_VIEW20 뷰를 생성하고 EMP_VIEW20 뷰에 WITH READ ONLY 기능 추가
CREATE OR REPLACE VIEW EMP_VIEW20 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY
WHERE DEPTNO=20 WITH READ ONLY;
SELECT * FROM EMP_VIEW20;

--EMP_VIEW20 뷰에서 사원번호가 7788인 사원정보 삭제 (EMP_COPY 테이블의 행 삭제)
--WITH READ ONLY 기능에 의해 단순뷰에서 DML 명령을 실행할 경우 에러 발생
DELETE FROM EMP_VIEW20 WHERE EMPNO=7788;--에러

🌻 뷰 삭제

형식) DROP VIEW 뷰이름

		DROP VIEW EMP_VIEW;

뷰 목록 확인

		SELECT VIEW_NAME,TEXT FROM USER_VIEWS;

테이블을 삭제해도 테이블을 기반으로 생성된 뷰 미삭제

		DROP TABLE EMP_COPY PURGE;
		SELECT VIEW_NAME,TEXT FROM USER_VIEWS;--미삭제된 뷰 존재

뷰가 참조할 수 있는 테이블이 없는 경우 에러 발생
- 테이블을 삭제할 경우 테이블을 기반으로 생성된 뷰는 삭제되지 않으나 뷰가 정상적으로 동작하지 않음

📌 ROWNUM

검색행에 순차적으로 행번호를 제공하는 키워드
테이블 또는 뷰의 모든 컬럼을 다른 검색대상과 같이 사용할 경우
[테이블명.*] 또는 [뷰.*] 형식으로 표현하여 검색 가능

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 검색
--행번호를 제공받아 검색
SELECT ROWNUM,EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;

--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,EMP_VIEW.* FROM EMP_VIEW;

📌 인라인 뷰(INLINE VIEW)

SELECT 명령에서 FROM의 서브쿼리에 의해 일시적으로 생성되어 사용되는 뷰
뷰 관련 시스템 권한이 없어도 인라인 뷰를 생성하여 사용 가능
형식) SELECT 검색대상,... FROM (SELECT 검색대상,... FROM 테이블명 [WHERE 조건식])
인라인 뷰에 별칭 설정 가능 (인라인 뷰의 별칭을 사용하여 인라인 뷰의 모든 컬럼값 검색을 표현)

SELECT * FROM (SELECT EMPNO,ENAME,SAL FROM EMP);
SELECT EMPNO,ENAME,SAL FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC);



--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 
--행번호를 제공받아 검색 → 서브쿼리로 정렬된 인라인 뷰를 생성하여 행번호 제공받아 검색
SELECT ROWNUM,EMPNO,ENAME,SAL FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC);


--인라인 뷰에 별칭 설정 가능 - 인라인 뷰의 별칭을 사용하여 인라인 뷰의 모든 컬럼값 검색을 표현
SELECT ROWNUM,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP;

⭐ 페이징 처리 ⭐

하나의 웹페이지에 원하는 범위의 행만 검색하여 출력하는 기능
ROWNUM 키워드를 사용한 인라인 뷰를 생성하여 작성

--EMP 테이블에 저장된 모든 사원의 사원번호, 사원이름, 급여를 급여로 내림차순 정렬하여
--행번호를 제공받아 검색하고 행번호가 5보다 작거나 같은 행 검색 
--ROWNUM 키워드를 WHERE의 조건식에서 사용하여 검색
SELECT ROWNUM,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP 
WHERE ROWNUM<=5;


--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 
--행번호를 제공받아 검색하고 행번호가 10인 행 검색
--ROWNUM 키워드는 인라인 뷰의 검색행에 순차적으로 부여되는 행번호로
--처리행 전에 행번호를 미리 부여받지 못해 비교 검색 불가능
--ROWNUM 키워드를 WHERE의 조건식에서 사용할 때 < 또는 <= 연산자는 사용 가능하지만
-- > 또는 >=, = 연산자를 이용한 연산식 사용 불가능
SELECT ROWNUM,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP
WHERE ROWNUM=10;--검색 실패



--ROWNUM 키워드를 포함한 인라인 뷰를 생성하고 ROWNUM 키워드에 컬럼 별칭을 설정하여
--조건식에 사용해 검색 가능
SELECT * FROM (SELECT ROWNUM RN,TEMP.* 
FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP) WHERE RN=10;



--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여
--행번호를 제공받아 검색하고 행번호가 6~10 범위에 포함되는 행 검색 
--페이징 처리시 사용하는 SELECT 명령
--페이징 처리 : 하나의 웹페이지에 원하는 범위의 행만 검색하여 출력하는 기능
SELECT * FROM (SELECT ROWNUM RN,TEMP.* 
	FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP) 
    WHERE RN BETWEEN 6 AND 10;

🌼 3. 시퀀스 (SEQUENCE)

숫자값(정수값)을 저장하여 자동 증가되는 값을 제공하는 객체

🌻 시퀸스 생성

형식) CREATE SEQUENCE 시퀀스명 [START WITH 초기값][INCREMENT BY 증가값]
[MAXVALUE 최대값][MINVALUE 최소값] [CYCLE][CACHE 갯수]
- START WITH 초기값 : 시퀸스에 저장되는 초기값 설정 - 생략 : NULL
- INCREMENT BY 증가값 : 자동 증가되는 숫자값 설정 - 생략 : 1
- MAXVALUE 최대값 : 저장 가능한 최대값 설정 - 생략 : 숫자값으로 표현 가능한 최대값
- MINVALUE 최소값 : 시퀸스에 저장 가능한 최소값 설정 - 생략 : 1
- CYCLE : 시퀸스의 저장값이 최대값을 초과할 경우 최소값부터 다시 제공되도록 반복하는 기능을 제공
- CACHE 갯수 : 임의의 저장공간에 자동 증가값을 미리 생성하여 제공할 수 있는 갯수를 설정하는 기능 - 생략 : 20
시퀸스에 저장된 숫자값 확인 - 형식) 시퀸스명.CURRVAL
시퀸스에 NULL이 있는 경우 시퀸스의 최소값을 제공한 후 최소값으로 시퀸스의 저장값 변경 처리
시퀸스에 저장된 숫자값을 이용하여 증가된 값을 제공하는 방법
- 증가된 값 제공 후 시퀸스에는 증가된 값 자동 저장
- 형식) 시퀸스명.NEXTVAL


--USER2 테이블 생성 - 회원번호(숫자형-PRIMARY KEY),회원이름(문자형),생년월일(날짜형)
CREATE TABLE USER2(NO NUMBER(2) PRIMARY KEY,NAME VARCHAR2(20),BIRTHDAY DATE);

--USER2 테이블의 NO 컬럼값으로 저장하기 위한 자동 증가값을 제공하는 시퀸스 생성
CREATE SEQUENCE USER2_SEQ;

--시퀸스 확인 - USER_SEQUNCES : 시퀸스 정보를 제공하는 딕셔너리
SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;

--시퀸스에 저장된 숫자값 확인
--형식)시퀸스명.CURRVAL
SELECT USER2_SEQ.CURRVAL FROM DUAL;--시퀸스에 NULL이 있는 경우 숫자값을 확인하면 에러 발생

--시퀸스에 저장된 숫자값을 이용하여 증가된 값을 제공하는 방법 
--증가된 값 제공 후 시퀸스에는 증가된 값 자동 저장
--시퀸스에 NULL이 있는 경우 시퀸스의 최소값을 제공한 후 최소값으로 시퀸스의 저장값 변경 처리
--형식)시퀸스명.NEXTVAL
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

--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;

🌻 시퀸스 변경

형식) 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;
--변경된 시퀀스 정보 확인


--USER2 테이블에 행 삽입
SELECT USER2_SEQ.CURRVAL FROM DUAL;--검색결과 : 5
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'일지매','03/09/09');
SELECT * FROM USER2; -- 일지매의 회원번호 10
COMMIT;

🌻 시퀸스 삭제

형식) DROP SEQUENCE 시퀸스명

--USER2_SEQ 시퀸스 삭제
DROP SEQUENCE USER2_SEQ;
SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;
-- 삭제되어 실행 X

🌼 4. 인덱스 (INDEX)

테이블에 저장된 행을 보다 빠르게 검색하기 위한 기능을 제공하는 객체
컬럼에 인덱스를 설정하면 인덱스 영역을 생성하여 컬럼에 대한 행 검색 속도 증가
조건식에서 많이 사용하는 컬럼에 설정하며 행이 많을 때 설정하는 것이 효율적
인덱스 종류
1. 유니크 인덱스(UNIQUE INDEX) : PRIMARY KEY 제약조건이나 UNIQUE 제약조건에 의해 자동으로 생성되는 인덱스
2. 비유니크 인덱스(NON-UNIQUE INDEX) : 사용자가 컬럼을 이용하여 수동으로 생성하는 인덱스


--USER3 테이블 생성 - 회원번호(숫자형-PRIMARY KEY),회원이름(문자형),이메일(문자형-UNIQUE)
--PRIMARY KEY 제약조건이나 UNIQUE 제약조건에 의해 유니크 인덱스 자동 생성
CREATE TABLE USER3(NO NUMBER(2) CONSTRAINT USER3_NO_PK PRIMARY KEY,NAME VARCHAR2(20)
    ,EMAIL VARCHAR2(50) CONSTRAINT USER3_EMAIL_UK UNIQUE);
    
--USER3 테이블의 제약조건 확인
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER3';

--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';

🌻 인덱스 생성

비유니크 인덱스 (NON-UNIQUE INDEX)
형식) CREATE INDEX 인덱스명 ON 테이블명(컬럼명)


--USER3 테이블의 NAME 컬럼을 이용하여 인덱스 생성
CREATE INDEX USER3_NAME_INDEX ON USER3(NAME);

--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';

🌻 인덱스 삭제

비유니크 인덱스 (NON-UNIQUE INDEX)
형식) DROP INDEX 인덱스명
유니크 인덱스는 PRIMARY 제약조건이나 UNIQUE 제약조건을 삭제하면 같이 삭제


--USER3 테이블의 EMAIL 컬럼에 대한 인덱스 삭제
DROP INDEX USER3_EMAIL_UK;--유니크 인덱스를 삭제할 경우 에러 발생

--USER3 테이블의 EMAIL 컬럼에 설정된 UNIQUE 제약조건 삭제
ALTER TABLE USER3 DROP CONSTRAINT USER3_EMAIL_UK;

--USER3 테이블의 제약조건 및 인덱스 확인
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='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';

--USER3 테이블의 NAME 컬럼에 대한 인덱스 삭제
DROP INDEX USER3_NAME_INDEX;

🌼 5. 동의어(SYNONYM)

오라클 객체를 다른 이름으로 설정하여 사용하기 위한 객체
동의어 종류
1. 전용 동의어 : 특정 사용자만 사용할 수 있는 동의어 (일반 사용자에 의해 관리)
2. 공용 동의어 : 모든 사용자가 사용할 수 있는 동의어 (관리자에 의해 관리)


-- 테이블 목록 확인 : USER_TABLES 딕셔너리(SYS 계정에 의해 생성된 뷰) 이용
SELECT TABLE_NAME FROM SYS.USER_TABLES;

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

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

📌 다른 사용자의 테이블이나 뷰에 접근하는 방법

다른 사용자의 테이블이나 뷰에 사용자 스키마를 이용하여 접근
형식) 사용자명.테이블명 OR 사용자명.뷰이름

SELECT TABLE_NAME FROM SYS.USER_TABLES;

🌻 동의어 생성

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


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


--시스템 관리자에게 동의어에 대한 시스템 권한을 부여 받은 후 동의어 관련 명령 사용 가능
CREATE SYNONYM BONUS FOR COMM;
SELECT * FROM COMM;
SELECT * FROM BONUS;

--COMM 테이블 관련 동의어 확인 (USER_SYNONYMS : 동의어 관련 정보를 제공하는 딕셔너리)
SELECT TABLE_NAME,SYNONYM_NAME,TABLE_OWNER FROM USER_SYNONYMS 
WHERE TABLE_NAME='COMM';

📌 동의어 권한 부여

시스템 관리자(SYSDBA - SYS 계정)로 접속하여 현재 접속 사용자(SCOTT)에게 동의어에 대한 시스템 권한 부여

GRANT CREATE SYNONYM TO SCOTT;

🌻 동의어 삭제

형식) DROP [PUBLIC] SYNONYM 동의어

--전용 동의어 BONUS 삭제
DROP SYNONYM BONUS;
SELECT TABLE_NAME,SYNONYM_NAME,TABLE_OWNER FROM USER_SYNONYMS WHERE TABLE_NAME='COMM';
SELECT * FROM COMM;
SELECT * FROM BONUS;--테이블이 없으므로 에러 발생

🌼 6. 사용자 (USER)

시스템(DBMS)을 사용할 수 있는 객체
계정(ACCOUNT) : 권한을 가진 사용자
계정 관리는 시스템 관리자(SYSDBA - SYS 계정)만 가능

🌻 계정 생성 / 삭제

형식) CREATE USER 계정명 IDENTIFIED BY 암호
형식) DROP USER 계정명

📌 계정 관리 (관리자 세션)

계정 관리는 관리자 세션에서 작업
오라클 12C 버전이상에서는 계정 관리하기 전에 세션에 대한 환경설정 변경

ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;

💡 KIM 계정 생성 (관리자 세션에서 작업)

CREATE USER KIM IDENTIFIED BY 1234;

💡 계정 확인

--DBA_USERS : 사용자 정보를 제공하는 딕셔너리
SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, CREATED FROM DBA_USERS
WHERE USERNAME='KIM';
-- USRENAME : KIM / ACCOUNT_STATUS : OPEN OR ROCKED / DEFAULT_TABLESPACE : USERS 
--/ CREATED : 생성날짜

💡 계정의 비밀번호 변경

계정의 비밀번호는 기본적으로 180일 유효기간으로 설정
형식) ALTER USER 계정명 IDENTIFIED BY 암호

--KIM 계정의 비밀번호 변경 (관리자 세션에서 작업)
ALTER USER KIM IDENTIFIED BY 5678;

💡 계정 상태 변경

OPEN (계정 활성화 - 다른 프로그램(SQLPLUS)에서 접속 가능), LOCK (계정 비활성화 - 접속 불가능)
오라클 접속시 계정의 비밀번호를 5번 틀리면 계정의 상태가 자동으로 비활성화(LOCK) 상태로 변경되어 접속 불가능
형식) ALTER USER 계정명 ACCOUNT {LOCK | UNLOCK}

--KIM 계정을 비활성화 상태로 변경 (관리자 세션에서 작업)
ALTER USER KIM ACCOUNT LOCK;
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS
WHERE USERNAME='KIM'; --ACCOUNT_STATUS : LOCKED 확인


--KIM 계정을 활성화 상태로 변경 (관리자 세션에서 작업)
ALTER USER KIM ACCOUNT UNLOCK;
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS
WHERE USERNAME='KIM'; --ACCOUNT_STATUS : OPEN 확인

💡 계정의 기본 테이블스페이스 변경

테이블스페이스(TABLESPACE) : 데이타베이스 객체(테이블,뷰,시퀸스,인덱스 등)가 저장되는 영역
오라클에는 기본적으로 SYSTEM 테이블스페이스와 USERS 테이블스페이스 제공
형식) ALTER USER 계정명 DEFAULT TABLESPACE 테이블스페이스명

--KIM 계정의 기본 테이블스페이스를 USERS로 변경 (관리자 세션에서 작업)
ALTER USER KIM DEFAULT TABLESPACE USERS;
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS
WHERE USERNAME='KIM'; --DEFAULT_TABLESPACE : USERS 확인

💡 계정의 테이블스페이스에 대한 물리적 저장크기 변경 - 용량 제한

형식) ALTER USER 계정명 QUITA 제한크기 ON 테이블스페이스명

--KIM 계정의 물리적 저장크기를 무제한으로 변경 (관리자 세션에서 작업)
ALTER USER KIM QUOTA UNLIMITED ON USERS;

--KIM 계정의 물리적 저장크기를 20MBYTE로 변경 (관리자 세션에서 작업)
ALTER USER KIM QUOTA 20M ON USERS;

💡 제약용량 확인

DBA_TS_QUOTAS : 테이블스페이스의 물리적 저장공간에 제한 관련 정보를 제공하는 딕셔너리

SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES FROM DBA_TS_QUOTAS;
--MAX_BYTES에서 무제한은 -1로 표현됨

💡 계정 삭제

형식) DROP USER 계정명

--KIM 계정 삭제 (관리자 세션에서 작업)
DROP USER KIM;
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS
WHERE USERNAME='KIM'; -- 계정 삭제된 것 확인

woom

Study Log 📂

이전 포스트

DDL (데이터 정의어) -1

다음 포스트

DDL (데이터 정의어) -2

ORACLE

🌼 2. 뷰 (VIEW)

🌻 뷰 생성 (서브쿼리 이용)

🐣 예제1 - 단순뷰

💡 뷰 생성 이점

🐣 예제2 - 복합뷰

🐣 예제3 - 기능 추가

🌻 뷰 삭제

📌 ROWNUM

📌 인라인 뷰(INLINE VIEW)

⭐ 페이징 처리 ⭐

🌼 3. 시퀀스 (SEQUENCE)

🌻 시퀸스 생성

🌻 시퀸스 변경

🌻 시퀸스 삭제

🌼 4. 인덱스 (INDEX)

🌻 인덱스 생성

🌻 인덱스 삭제

🌼 5. 동의어(SYNONYM)

📌 다른 사용자의 테이블이나 뷰에 접근하는 방법

🌻 동의어 생성

📌 동의어 권한 부여

🌻 동의어 삭제

🌼 6. 사용자 (USER)

🌻 계정 생성 / 삭제

📌 계정 관리 (관리자 세션)

💡 KIM 계정 생성 (관리자 세션에서 작업)

💡 계정 확인

💡 계정의 비밀번호 변경

💡 계정 상태 변경

💡 계정의 기본 테이블스페이스 변경

💡 계정의 테이블스페이스에 대한 물리적 저장크기 변경 - 용량 제한

💡 제약용량 확인

💡 계정 삭제

DDL (데이터 정의어) -1

DCL (데이터 제어어)

0개의 댓글