오라클 DBMS 정리 08

08 Nov 2018

Reading time ~6 minutes

오라클 DBMS 정리 08 - VIEW, SYNONYM, INDEX, GRANT/REVOKE, BACKUP, RECOVERY

계정

관리자, 관리자권한을 가진 계정만 계정을 생성할 수 있다
관리자 로그인 방법

sqlplus "/as sysdba"

계정 생성
- 초기 계정은 아무것도 못함
  - 접속, 자원에 대한 사용 X

CREATE USER 계정명 IDENTIFIED BY 초기비밀번호;

권한
- CONNECT - DB Server에 접속하는 권한
  - 쿼리 중 SELECT만 가능
  - 테이블을 못 사용함, DUAL테이블만 사용가능
- RESOURCE - 테이블 생성, 사용하는 권한
- DBA - 관리자 권한
  - 모두 가능하기 때문에 권한을 쉽게 주면 안됨
  - 계정 생성 가능
  - 서버재시작은 불가능(system만 가능)
- CREATE SYNONYM - SYNONYM 생성권한
  - SYNONYM은 테이블에 별명을 주는 것. 사용간편화, 보안성 향상
- CREATE VIEW - VIEW 생성권한

GRANT 권한,.. TO 계정명;

권한 제거

REVOKE 권한,... FROM 계정명;

계정 삭제
- CASCADE
  - 해당 계정이 생성했던 모든 객체를 삭제하고 계정을 삭제하는 옵션

DROP USER 계정명 [CASCADE];

-- 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 사용불가
- 단순 뷰
  - 테이블 하나를 조회하여 뷰를 생성
  - 테이블과 똑같이 사용
- 복합 뷰
  - 테이블 여러개를 조회하여 뷰를 생성
  - 함수를 사용하여 조회한 결과를 가지고 뷰를 생성
권한 부여

GRANT CREATE VIEW TO 계정명

VIEW 생성

CREATE VIEW 뷰명
AS (SELECT ... )

VIEW 조회

SELECT * FROM user_views;

VIEW 삭제

DROP VIEW 뷰명

-- 단순 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);

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

-- 단순 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 딕셔너리에서 확인 가능
권한부여

GRANT CREATE SYNONYM TO 계정명;

SYNONYM 생성

CREATE SYNONYM 시노님명 FOR 실제테이블명;

SYNONYM 삭제

DROP SYNONYM 시노님명;

-- 긴 테이블명을 사용하게 편하게 짧은 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 생성

CREATE [UNIQUE|BITMAP] INDEX 인덱스명 ON 테이블명(컬럼명);

UNIQUE INDEX - 컬럼의 값이 유일할 때
- 인덱스를 만드는 Key값에 중복되는 데이터가 없다는 뜻
- PK, UNIQUE한 컬럼

CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명 [DESC});

NON-UNIQUE INDEX - 컬럼의 값이 중복될 때
- 일반컬럼에 인덱스를 쓸 때 사용

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

BITMAP INDEX - 컬럼의 값이 중복되나 독특한 형태의 값일 때
- 근데 Oracle Enterprise Edition만 사용가능…
- ‘S_0001’, ‘TEST_0001’ 같은 형태

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

COMPOSITE INDEX - 여러 컬럼이 합쳐져서 인덱스를 구성할 때

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

INDEX 갱신
- 실제 테이블과 인덱스를 비교하여 다시 만드는 것

ALTER INDEX 인덱스명 REBUILD;

INDEX 삭제

DROP INDEX 인덱스명;

인덱스의 사용
- 조회하는 컬럼명에 hint를 사용
  - DESC를 붙이면 인덱스를 아래부텉 읽으라는 뜻
  - 자세한 내용은 SQL튜닝학습 필요

SELECT /*+INDEX(인덱스명)*/ 컬럼명, ... 

B-TREE INDEX

오라클에서 주로 사용하는 인덱스
적은 횟수로 결과를 조회
- FULL SCAN
  - 위에서부터 하나씩 내려가면서 전체 레코드를 검색하는 방식(순차탐색)
  - WHERE절을 쓰면 기본으로 사용되는 방식
- B TREE INDEX
  - 인덱스로 값을 순차탐색이 아닌 이진탐색트리같은 방식으로 더 빠르게 찾는 방식
  - 키컬럼, ROWID컬럼으로 값이 정렬되어 들어가있음
    - ROWID : 컬럼값의 실제 주소

-- 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절에선 집계함수 사용불가

-- 사원수가 3명 이상인 부서의 부서번호, 사원수, 연봉합, 최고연봉을 조회
SELECT   deptno, COUNT(empno), SUM(sal), MAX(sal)
FROM     emp
GROUP BY deptno
HAVING   COUNT(*) >= 4;

REPLACE 함수

값을 치환할 때 사용

REPLACE(문자열컬럼, 바뀔문자열, 치환할문자열)

SELECT  REPLACE('오늘은 목요일입니다.', '오늘', '내일') output
FROM    DUAL;

생성된 객체들 보기

user_objects 딕셔너리를 사용하면 유저가 생성한 객체들을 볼 수 있다.

SELECT * FROM user_objects;

BACKUP

DB는 정보자산. BACKUP을 하면 .dump 파일로 생성됨
- DB와 어느정도 일정거리 떨어진 안전하게 보관할 수 있는 장소가 안전
BACKUP/RECOVERY는 툴(exp.exe, imp.exe)을 사용
exp.exe 사용하여 cmd에서 진행
- 모든 사용자가 백업과 복구 가능
- 백업하는 DB와 복구하는 DB가 bit버전(32, 64)이 다르면 안됨

-- 도움말
exp -help

-- 비밀번호를 보호하기 위해선 계정/비번을 입력하지 않고 진행하면 됨

-- 테이블만 백업
-- dmp나 ora파일, ora는 요즘엔 안씀
exp 계정/비번 tables=(백업할테이블명,테이블명,테이블명,...) file=경로/파일명.dmp

-- 계정이 생성한 모든걸 백업
exp 계정/비번 full=y file=경로/파일명.dmp

RECOVERY

imp.exe 사용하여 cmd에서 진행
모든 사용자가 복구할 수 있음
같은 이름의 객체가 있다면 복구되지 않는다
- MERGER할 수는 있으나 중복데이터 발생 가능
백업 데이터 생성 후 처리됐던 트랜잭션 복구는 직접 LOG파일 내용을 찾아 복구해야 하기 때문에 오래 걸린다

-- 도움말
imp -help

-- 테이블만 복구 시
imp 계정/비번 tables=(복구할테이블명, ...) file=경로/파일명.dmp

-- 모든 데이터를 복구 시
imp 계정/비번 full=y file=경로/파일명.dmp

오라클DBMS