[데이터베이스] 23일차 - tablespace, index, synonym, sequence, backup

데이터베이스 11일차

 

 

2012/08/28 - [Dev. 데이터베이스/Oracle 관련] - [오라클 SQL 강좌] 시노님 (Synonym-동의어)

 

2012/08/28 - [Dev. 데이터베이스/Oracle 관련] - [오라클 SQL 강좌] 시퀀스 (Sequence)

 

2012/08/28 - [Dev. 데이터베이스/Oracle 관련] - DB 백업(Backup) 및 복구(Recovery) - 전체, 계정(스키마), 테이블

 

2012/08/28 - [Dev. 데이터베이스/Oracle 관련] - [오라클 SQL 강좌] 뷰 (View)

 

2012/08/29 - [Dev. 데이터베이스/Oracle 관련] - [오라클 SQL] 대용량 데이터 sqlldr 이용하여 넣기(csv파일 이용)

-- 테이블스페이스 만들기
create tablespace javats
  datafile 'D:\oracle\app\oracle\oradata\XE\javats.dbf'
  size 50M  -- size 50[M, K] 크기지정
  autoextend on -- 자동 확장 여부
  -----------------------------------------여기까지 필수항목
/*
  ** 추가적인 옵션들
  [ONLINE|OFFLINE]  바로 사용여부
  [PERMANENT|TEMPORARY] 영구, 일시여부
  extent management dictionary | local autoallocate
  -- extent 관리를 dict에서 할지, datafile에서 비트맵타입으로 할지 여부
  -- uniform : 유저가 관리 ( 기본 1M )
  blocksize 8K 8K가 기본값
  segment space management auto -- 오라클이 세그먼트 공간 관리
  flashback on  -- 쓰레기통 사용여부
*/

-- 사용자별 테이블 스페이스 허용량 설정
alter user hkp
  quota 10m on javats;
-- quota unlimited on javats; -- 허용량 무제한 설정

-- 사용방법
-- scott 접속 : 인덱스를 다른 테이블스페이스로 지정하자

drop index idx_emp_ename;
create index idx_emp_ename
  on emp(ename)
  tablespace javats;
 
select * from user_indexes;

-- sys접속
-- 새로 계정 추가
create user hkp
  identified by 1234
  default tablespace javats;
-- 테이블 스페이스 미지정시 자동으로 users가 지정된다.
grant create session to hkp;
grant resource to hkp;
-- role : connect(접속권한포함), resource(테이블 작성 및 자원사용권한)
grant create synonym to hkp;      -- 권한 수여
revoke create synonym from hkp;   -- 권한 박탈

select * from dict where lower(comments) like '%role%';
select * from dba_roles;
select * from role_sys_privs order by 1;  -- role에 주어진 권한 목록

grant create view to scott;
grant create synonym to scott;

-- scott 접속
-- view 작성
create view v_emp
as
select e.empno 사원번호, e.ename 사원명, d.dname 사원부서명,
  m.empno 관리자번호, m.ename 관리자명, md.dname 관리자부서명
from emp e
  left join dept d on e.deptno = d.deptno
  left join emp m on e.mgr = m.empno
  left join dept md on m.deptno = md.deptno;
 
-- sys 계정에서
grant select on scott.v_emp to hr;  -- hr에게 scott의 v_emp 조회권한부여

-- scott 접속
-- sequence : 일련번호
create table testseq (
  no number primary key
  , memo varchar2(4000)
);
create sequence seq_test
  nocycle   -- 순환하지 않음
  nocache;   -- 미리 캐쉬를 이용해서 숫자를 뽑아놓지 않음
-- start with 1 : 1부터 시작
-- increment by n : n 값으로 증가
-- maxvalue n|nomaxvalue : 최대값 지정
-- minvalue n|nominvalue : 최소값 지정
insert into testseq values (seq_test.nextval, '테스트'||seq_test.currval);
select * from testseq;
select seq_test.currval from dual;
rollback;

-- synonym : 동의어
-- ex)
select * from dictionary;
select * from dict;

-- 사용예)
create synonym em for emp;
select * from em;

-- 우편번호 테이블 생성
create table ziptbl (
  zipcode varchar2(7)
  ,sido   varchar2(50)
  ,gugun  varchar2(1000)
  ,dong   varchar2(2000)
  ,bunji  varchar2(1000)
  ,seq    number(5)
);

-- zipfinder.co.kr 에서 type1 우편번호 파일 다운로드
-- 엑셀을 이용하여 csv로 변환
-- zipcode.csv로 c드라이브 루트에 저장
-- 새롭게 아래와 같은 내용으로 zipcode.ctl(컨트롤파일) 작성
/*
load data
infile 'c:\zipcode.csv'
append
into table ziptbl
fields terminated by ','
(zipcode, sido, gugun, dong, bunji, seq)
*/
-- zipcode.csv와 zipcode.ctl을 같은 폴더에 넣고 sqlldr 실행
-- C:\>sqlldr scott/tiger control=zipcode.ctl

select * from ziptbl
where dong like '%부평%';

/*
  백업
  1. 전체 DB 백업 (sys가 아닌 system)
    exp system/cjdsuscnldjq1! full=y file="c:\all.dmp"
   
  2. 계정(스키마) 백업
    exp hr/1234 full=y file="c:\hr.dmp"
   
  3. 테이블 백업
    exp scott/tiger file="c:\scotttbl.dmp" tables=(ziptbl, emp, dept)
   
 
  복구
  1. 계정 복구
    -- 삭제된 내용에 대해서 복구가 되나, 이미 존재하는 테이블의 내용을 변경하지는 않는다.
    -- ignore=y 로 옵션을 주면 기존 테이블에 데이터를 추가 가능
    -- ignore는 primary key가 없는 데이터들은 중복으로 생성될 수 있다. 사용에 유의할 것!
    imp hr/1234 file="c:\hr.dmp" full=y [ignore=y]
   
  2. 테이블 복구
    -- scott -> hkp 으로 테이블을 복구
    imp system/cjdsuscnldjq1! fromuser=scott touser=hkp file="c:\scotttbl.dmp"
   
*/
select * from employees;
drop table employees cascade constraint purge;
-- cascade constraint : 제약조건까지 삭제(강제로 지우게 된다)

select * from tab;
desc buy;