아티클 목록

오라클 DBMS 정리 08

오라클 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

댓글