--- title: "오라클 DBMS 정리 08" date: "2018-11-08" tags: ["오라클DBMS"] status: archived verified: false --- ## 오라클 DBMS 정리 08 - VIEW, SYNONYM, INDEX, GRANT/REVOKE, BACKUP, RECOVERY --- ### 계정 * 관리자, 관리자권한을 가진 계정만 계정을 생성할 수 있다 * 관리자 로그인 방법 ```sql sqlplus "/as sysdba" ``` * 계정 생성 * 초기 계정은 아무것도 못함 * 접속, 자원에 대한 사용 X ```sql CREATE USER 계정명 IDENTIFIED BY 초기비밀번호; ``` * 권한 * **CONNECT** - DB Server에 접속하는 권한 * 쿼리 중 SELECT만 가능 * 테이블을 못 사용함, DUAL테이블만 사용가능 * **RESOURCE** - 테이블 생성, 사용하는 권한 * **DBA** - 관리자 권한 * 모두 가능하기 때문에 권한을 쉽게 주면 안됨 * 계정 생성 가능 * 서버재시작은 불가능(system만 가능) * **CREATE SYNONYM** - SYNONYM 생성권한 * SYNONYM은 테이블에 별명을 주는 것. 사용간편화, 보안성 향상 * **CREATE VIEW** - VIEW 생성권한 ```sql GRANT 권한,.. TO 계정명; ``` * 권한 제거 ```sql REVOKE 권한,... FROM 계정명; ``` * 계정 삭제 * CASCADE * 해당 계정이 생성했던 모든 객체를 삭제하고 계정을 삭제하는 옵션 ```sql DROP USER 계정명 [CASCADE]; ``` ```sql -- system 계정은 많은 딕셔너리들을 갖고 있다 SELECT tname FROM tab; -- 계정 조회(all_users, dba_users) SELECT * FROM all_users; SELECT * FROM dba_users; ``` --- ### VIEW * 실제 테이블을 기반으로 생성된 **가상 테이블** * 보안성과 편의성을 향상시키기 위해 사용 * 보안성 * 실제 테이블을 알 수 없어도 DB 작업 가능 * 편의성 * 조인한 결과를 뷰로 생성하면 JOIN의 문법을 몰라도 조회 가능 * 예) JOIN이 서툴 시 DBA가 JOIN한 VIEW를 주면 간단히 SELECT 가능 * **실제 테이블의 데이터가 변경되면 VIEW에 바로 적용됨** * VIEW생성할 수 있는 권한을 가진 계정만 생성가능 * **DBA 권한, CREATE VIEW** * **단순 VIEW와 복합 VIEW 2가지로 생성가능** * **단순 VIEW는 DML 사용 가능, 복합 VIEW는 DML 사용불가** * 단순 뷰 * 테이블 하나를 조회하여 뷰를 생성 * 테이블과 똑같이 사용 * 복합 뷰 * 테이블 여러개를 조회하여 뷰를 생성 * 함수를 사용하여 조회한 결과를 가지고 뷰를 생성 * 권한 부여 ```sql GRANT CREATE VIEW TO 계정명 ``` * VIEW 생성 ```sql CREATE VIEW 뷰명 AS (SELECT ... ) ``` * VIEW 조회 ```sql SELECT * FROM user_views; ``` * VIEW 삭제 ```sql DROP VIEW 뷰명 ``` ```sql -- 단순 VIEW(하나의 테이블에 몇개의 컬럼으로 view 생성) -- cp_emp3테이블의 사원번호, 사원명, 직무, 연봉으로 VIEW 생성 CREATE VIEW emp_view AS (SELECT empno, ename, job, sal FROM cp_emp3); -- 뷰 사용 예)고객정보 사용할 때 사원이 보는 정보와 관리자가 보는 정보는 다를 것 -- 사원(고객명, 고객번호, 전화번호) 만 볼 수 있다면 세 컬럼을 갖는 뷰를 생성하면 됨 -- 관리자(고객명, 고객번호, 전화번호, 주민번호, 카드번호 ..) 이렇게 뷰를 만들어 관리자 사용 -- 이처럼 여러 정보들을 직급에 따라 제공하는 정보를 다르게 할 수 있다 (뷰의 장점) -- VIEW에 포함되지 않은 컬럼은 INSERT를 수행하면 NULL이 입력된다 INSERT INTO emp_view(empno, ename, job, sal) VALUES (1234, '오영근', '차장', 4100); ``` ```sql -- VIEW는 실제로 데이터를 갖지 않음, 참조만 하는 것 -- 복합 VIEW : 테이블 여러개를 사용하여 생성한 VIEW 또는 -- 조회할 때 함수를 사용하여 조회한 결과로 생성한 VIEW -- 복합 VIEW는 DML(INSERT, UPDATE, DELETE)이 수행되지 않는다 -- 여러개의 테이블을 모으고 단순 SELECT를 사용하여 조회할 때 사용 CREATE VIEW car_view AS (SELECT cma.maker, cma.model, cmo.car_year, cmo.price, cmo.cc, cmo.car_option FROM car_maker cma, car_model cmo WHERE cmo.model = cma.model); -- VIEW를 사용하면 JOIN된 결과를 단순쿼리로 얻을 수 있다 SELECT maker, model, car_year, price, cc, car_option FROM car_view; -- 복합 VIEW는 INSERT, UPDATE, DELETE를 할 수 없다 INSERT INTO car_view(maker, model, car_year, price, cc, car_option) VALUES('현대', '벨로스터', 2017, 3000, 1999, '문짝3개,ABS'); ``` ```sql -- 단순 VIEW에 INSERT, UPDATE, DELETE를 막기 위해선 -- VIEW 생성 시 WITH READ ONLY라는 옵션을 주면 된다 CREATE VIEW read_emp AS (SELECT * FROM cp_emp2) WITH READ ONLY; SELECT view_name,read_only FROM user_views WHERE view_name='READ_EMP'; -- WITH READ ONLY 옵션을 사용한 VIEW는 DML(추가, 삭제, 변경)을 할 수 없다. ``` --- ### SYNONYM * **테이블명에 별명을 부여하는 것** * 보안성과 편의성 향상 * 권한을 가진 계정만 생성 가능 * **DBA, CREATE SYNONYM** * SYNONYM의 이름으로 VIEW를 생성할 수 있음 * user_synonyms 딕셔너리에서 확인 가능 * 권한부여 ```sql GRANT CREATE SYNONYM TO 계정명; ``` * SYNONYM 생성 ```sql CREATE SYNONYM 시노님명 FOR 실제테이블명; ``` * SYNONYM 삭제 ```sql DROP SYNONYM 시노님명; ``` ```sql -- 긴 테이블명을 사용하게 편하게 짧은 SYNONYM을 만듦(편의성) CREATE SYNONYM tmp FOR table_multi_primary; -- SYNONYM은 일반 테이블명처럼 사용가능! SELECT * FROM tmp; ``` --- ### INDEX * **레코드가 많을 때 검색을 빠르게 하기 위해 사용하는 객체** * 접두어로 ix, idx사용 * 오라클은 자동인덱스, 수동인덱스 두가지 제공 * **자동인덱스** * 테이블 생성 시 PK, UNIQUE를 설정하면 자동으로 생성되는 인덱스 * **수동인덱스** * DBA(Database Administrator)가 생성하는 인덱스 * 생성된 인덱스를 검색하는 딕셔너리 user_indexes * 특정컬럼을 인덱스로 사용 * UNIQUE INDEX * 컬럼의 값이 유일한 경우 * NON UNIQUE INDEX * 컬럼의 값이 중복되는 경우 * BITMAP INDEX * 컬럼의 값이 중복되나 독특한 경우('ITEM_0001' 이런식의 형태) * COMPOSITE INDEX * 여러컬럼이 합쳐져 인덱스를 구성하는 경우 * **인덱스를 설정해야하는 경우** * 전체 레코드에서 조회를 했을 때 조회결과가 약 10~15%정도 였을 때 * 조건절(WHERE, JOIN 등)에서 자주 사용이 되는 컬럼이지만 값의 변경이 자주 발생하지 않는 경우 * 테이블의 변경값이 인덱스에 반영되지 않는다. * 오래되면 검색속도가 많이 떨어진다. * INDEX REBUILD로 해결 * 적은데이터를 가질 땐 인덱스를 안쓰는게 더 좋음 * user_indexes 딕셔너리에서 조회가능 * INDEX 생성 ```sql CREATE [UNIQUE|BITMAP] INDEX 인덱스명 ON 테이블명(컬럼명); ``` * **UNIQUE INDEX** - 컬럼의 값이 유일할 때 * 인덱스를 만드는 Key값에 중복되는 데이터가 없다는 뜻 * PK, UNIQUE한 컬럼 ```sql CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명 [DESC}); ``` * **NON-UNIQUE INDEX** - 컬럼의 값이 중복될 때 * 일반컬럼에 인덱스를 쓸 때 사용 ```sql CREATE INDEX 인덱스명 ON 테이블명(컬럼명); ``` * **BITMAP INDEX** - 컬럼의 값이 중복되나 독특한 형태의 값일 때 * 근데 Oracle Enterprise Edition만 사용가능... * 'S_0001', 'TEST_0001' 같은 형태 ```sql CREATE BITMAP INDEX 인덱스명 ON 테이블명(컬럼명); ``` * COMPOSITE INDEX - 여러 컬럼이 합쳐져서 인덱스를 구성할 때 ```sql CREATE INDEX 인덱스명 ON 테이블명(컬럼명, 컬럼명, ...); ``` * INDEX 갱신 * 실제 테이블과 인덱스를 비교하여 다시 만드는 것 ```sql ALTER INDEX 인덱스명 REBUILD; ``` * INDEX 삭제 ```sql DROP INDEX 인덱스명; ``` * 인덱스의 사용 * 조회하는 컬럼명에 **hint**를 사용 * DESC를 붙이면 인덱스를 아래부텉 읽으라는 뜻 * 자세한 내용은 SQL튜닝학습 필요 ```sql SELECT /*+INDEX(인덱스명)*/ 컬럼명, ... ``` --- ### B-TREE INDEX * 오라클에서 주로 사용하는 인덱스 * 적은 횟수로 결과를 조회 * **FULL SCAN** * 위에서부터 하나씩 내려가면서 전체 레코드를 검색하는 방식(순차탐색) * WHERE절을 쓰면 기본으로 사용되는 방식 * **B TREE INDEX** * 인덱스로 값을 순차탐색이 아닌 이진탐색트리같은 방식으로 더 빠르게 찾는 방식 * 키컬럼, ROWID컬럼으로 값이 정렬되어 들어가있음 * ROWID : 컬럼값의 실제 주소 ```sql -- cp_zipcode테이블의 seq컬럼(유니크한 값을 갖는 컬럼)에 UNIQUE INDEX 설정 CREATE UNIQUE INDEX idx_zipcode ON cp_zipcode(seq); -- 인덱스를 사용하지 않는 조회(FULL-SCAN) SELECT zipcode, sido, gugun, bunji, seq FROM zipcode WHERE seq=49000; -- 인덱스를 사용한 조회 SELECT /*+ORDERED INDEX(idx_zipcode)*/ zipcode, sido, gugun, bunji, seq FROM cp_zipcode WHERE seq=49000; -- 역순으로 정렬된 인덱스라면 ORDRED INDEX_DESC를 붙여줌 ``` * 인덱스를 사용해도 데이터가 충분하지 않다면 처리 시간이 더 걸릴 수 있다. --- ### GROUP BY HAVING * HAVING - 그룹으로 묶일 조건을 줄 때 사용 * **HAVING절에선 집계함수 사용 가능** * WHERE절에선 집계함수 사용불가 ```sql -- 사원수가 3명 이상인 부서의 부서번호, 사원수, 연봉합, 최고연봉을 조회 SELECT deptno, COUNT(empno), SUM(sal), MAX(sal) FROM emp GROUP BY deptno HAVING COUNT(*) >= 4; ``` --- ### REPLACE 함수 * 값을 치환할 때 사용 ```sql REPLACE(문자열컬럼, 바뀔문자열, 치환할문자열) ``` ```sql SELECT REPLACE('오늘은 목요일입니다.', '오늘', '내일') output FROM DUAL; ``` --- ### 생성된 객체들 보기 * user_objects 딕셔너리를 사용하면 유저가 생성한 객체들을 볼 수 있다. ```sql SELECT * FROM user_objects; ``` ### BACKUP * DB는 정보자산. BACKUP을 하면 .dump 파일로 생성됨 * DB와 어느정도 일정거리 떨어진 안전하게 보관할 수 있는 장소가 안전 * **BACKUP/RECOVERY는 툴(exp.exe, imp.exe)을 사용** * exp.exe 사용하여 cmd에서 진행 * **모든 사용자가 백업과 복구 가능** * 백업하는 DB와 복구하는 DB가 bit버전(32, 64)이 다르면 안됨 ```sql -- 도움말 exp -help -- 비밀번호를 보호하기 위해선 계정/비번을 입력하지 않고 진행하면 됨 -- 테이블만 백업 -- dmp나 ora파일, ora는 요즘엔 안씀 exp 계정/비번 tables=(백업할테이블명,테이블명,테이블명,...) file=경로/파일명.dmp -- 계정이 생성한 모든걸 백업 exp 계정/비번 full=y file=경로/파일명.dmp ``` --- ### RECOVERY * imp.exe 사용하여 cmd에서 진행 * 모든 사용자가 복구할 수 있음 * **같은 이름의 객체가 있다면 복구되지 않는다** * MERGER할 수는 있으나 중복데이터 발생 가능 * 백업 데이터 생성 후 처리됐던 트랜잭션 복구는 직접 LOG파일 내용을 찾아 복구해야 하기 때문에 오래 걸린다 ```sql -- 도움말 imp -help -- 테이블만 복구 시 imp 계정/비번 tables=(복구할테이블명, ...) file=경로/파일명.dmp -- 모든 데이터를 복구 시 imp 계정/비번 full=y file=경로/파일명.dmp ```