오라클 관계형 데이터베이스 소개
* oracle 시스템 데이터 Dictionary에 들어 있는 정보
1. 테이블과 뷰, 스냅샷, 인덱스, 동의어, 프로시저, 함수를 포함한 모든 데이터베이스 오브젝트의 이름
2. 오라클 데이터베이스 사용자의 이름
3. 각 사용자에게 주어진 특권과 역할
4. 무결성 제한의 명세
5. 열을 위한 초기 값
6. 스페이스 소비와 할당
7. 정보 오디팅(auditing)
* oracle 시스템 데이터 Dictionary의 필수적인 두 컴포넌트
1. 베이스 테이블
2. 사용자가 액세스 할 수 있는 뷰
* 데이터 Dictionary는 sys 사용자의 소유이다.
* 오라클 데이터베이스 논리구조 특징 정의
1. 테이블스페이스
2. 세그먼트
3. 영역(extents)
4. data block
논리적 데이터베이스 저장구조
* 테이블 스페이스를 사용하여 관리자가 하는 일
1. 데이터 베이스에 의한 물리적 디스크 스페이스 사용을 컨트롤 한다.
2. 특정 디스크 쿼타를 개별 사용자에게 할당한다.
3. 여러 물리적 저장장치에 걸쳐 데이터 저장을 할당한다.
* 테이블 스페이스를 나누는 잇점
1. 시스템 데이타와 사용자 데이터를 구분한다.
2. 애플리케이션들을 서로 구분할 수 있다.
3. 각 테이블스페이스를 하나는 온라인으로 다른 것은 오프라인으로 할 수 있는 기회를 제공한다..
4. 퍼포먼스를 개선하고 데이터 회선을 줄이기 위하여 서로 다른 테이블스페이스를 별도의 디스크 드라이브에 저장할 수 있는 능력
* 시스템 테이블 스페이스에 포함되는 것들
1. 프로시저 함수, 트리거, 패키지와 같은 PL/SQL, 롤백 세그먼트(rollback segment)
* 시스템 테이블 스페이스는 항상 온라인으로 있어야 한다.
* 테이블 스페이스가 오프라인일 필요가 있는 경우
1. 데이터베이스에서 유지 또는 백업을 수행하기 위해
2. 애플리케이션에의 업그레이드나 수정 중에 이용할 수 없는 테이블 그룹을 만들기 위해
3. 데이타 베이스가 온라인으로 있을때 일부를 이용할 수 없도록 만들때
* 테이블 스페이스 만들기
create tablespace dbt_user_01
datafile '<directory>/datafile_01' size 10m
default storage (
initial 50k
next 50k
minextents 10
maxextents 50
pctincrease 0)
offline;
* 기존의 테이블 스페이스에 새로운 데이터 파일 추가
alter tablespace dbt_user_01
add datafile '<directory>/new_file_name_2' size 5m
* 테이블 스페이스의 정보 보기
select tablespace_name, initial_extent, next_extent, max_extents, pct_increase,
from sys.dba_tablespace;
* segment
데이터를 저장하는 데이터베이스 오브젝트의 물리적인 부분
종류: 테이블, 인덱스, 롤백, 템포러리
* 테이블 세그먼트
테이블, 클러스터, 스냅샷으로부터의 데이터를 저장한다.
pctfree명령은 세그먼트가 가지는 프리스페이스의 양을 모니터링한다.
* 인덱스 세그먼트
인덱스 데이터를 수용한다.
* 롤백 세그먼트
원하면 롤백 하여야 할 sql트랜잭션의 활동을 기록하는 데이타베이스의 일부분이다
변경된 데이타를 위한 블록 id와 파일명, 마지막 트랜잭션 이전에 존재하였던 데이타를 포함
* Temporary 세그먼트
sql트랜잭션의 중간 결과를 수용
* Extents(영역)
연속적인 블록 번호들로 이루어지는 논리적 저장구조
* 데이터 블록
데이터를 저장하기 위하여 오라클이 사용하는 최소 저장단위
헤더,테이블디렉토리,로우디렉토리,로우데이타,프리스페이스를 포함한다.
* pctfree와 pctused
pctfree와 pctused 는 개발자가 데이터 블록 프리스페이스의 사용을 콘트롤 하는데 이용할 수 있다. 이 2개의 파라미터는 테이블과 클러스터를 만들거나 변경할 때에만 지정할 수 있다.
pctfree 35 : create와 함께 사용되며, 그 테이블을 위하여 사용되는 데이터 테이블의 35%를 가능한 장래의 갱신을 위한 프리로 남겨두라는 것
pctused 40 : create와 함께 사용되어 오라클로 하여금 그 블록의 40% 이상이 프리 일경우에만 데이타를 삽입하라는 것.
데이터 타입
* 데이터 타입
값의 범위를 제한해줌
* char type
1부터 255의 범위 문자 숫자 데이타를 저장한다.데이타 길이는 고정된다.
실제 들어가는 데이타가 할당된 것보다 적으면 나머지 부분엔 공백이 들어가고 그 반대의 경우엔 에러가 난다.
* varchar2
1부터 2000바이트의 범위(variable length)
* long
2기가 바이트까지의 정보를 포함하는 문자 데이터(variable length)
* row, long row
2진 데이타를 위한 것으로 이 데이타들은 멀티미디어 데이타나 2진 데이타를 위해 적절하다.
raw는 varchar2와 비슷하고 long law는 long과 비슷하다.
* row id
클러스터되지 않은 오라클 테이블에서 행의 물리적 주소에 해당
다음과 같이 사용 가능하다.
select rowid, ename from emp
row id EMP_NAME
--------------------------
000c0b7.0000.0001 'WHITE'
000c0b7 : 데이터 블록
0000 : 행 시퀀스 번호
0001 : 데이터 파일 번호
* row id의 특징
1. 가장 빠른 행 액세스 속도
2. 테이블이 어떻게 조직되는지 보는데 사용할 수 있다.
3. 주어진 테이블에 있는 행들을 위한 고유한 식별자. rowid는 인덱스 구축에 사용됨
* mlslabel 데이타 타입
Trusted Oracle을 위해 사용되며 255바이트의 최대 크기를 갖는다.
테이블
* 테이블 만들기
create table emp (
empno number(15) primary key,
first_name varchar2(20) not null,
last_name varchar2(40) not null,
deptno varchar2(12),
hire_date date not null)
pctfree 20
pctused 40
tablespace emp_01
storage (initial 50k
next 50k
maxextense 15
pctincrease 25);
empno는 주키로 사용된다.
pctfree는 장래의 확장을 위하여 프리 스페이스로서 예비된 각 데이터블럭의 퍼센티지를 나타내다. 20%가 예비된다.
pctused는 새로운 레코드를 삽입하기 위해 남아 있어야 할 양이다.
pctfree 값을 크게 하면 사용되지 않는 공간을 증가시키지만 sql명령의 효율을 증가시킨다.
pctused값을 크게 하면 미사용저장 공간을 줄이긴 하지만 insert, update실행의 퍼포먼스를 저하시킨다.
* 테이블 코멘트
테이블이나 열에 대한 코멘트를 데이타 사전에 추가하려면 comment명령을 사용한다.
comment on column emp.hire_date IS '무라무라';
코멘트를 제거하려면
comment on column emp.hire_date IS ''
* 테이블 제작중에 테이블 로드하기
앞의 테이블 작성 예에 다음을 추가한다.
as select empid, f_name, l_name, dept, hire_date from uk_emp;
* 테이블 클러스터 만들기
클러스터는 테이블 테이터를 저장하는 선택적인 방법. 자주 함께 액세스되는 테이블은 동일한 데이터 블럭에 물리적으로 함께 저장된다.
create cluster emp_dept ( deptno number(5))
pstused 80
pctfree 5;
* 클러스터 인덱스 만들기
create index i_emp_dept
on cluster emp_dept;
* 테이블 변경
alter table dept
add (dept_mgr varchar2(35));
alter table emp
modify (sal not null);
alter table emp
add constraint s_dept_man_id_fk foreign key (deptno) references dept;
change dept to emp
alter table dept
drop constraint s_dept_man_id_fk
alter table emp
* 테이블 잘라내기
truncate table명령 사용
장점 : 롤백세그먼트를 사용하지 않는다.실행즉시 테이블스페이스를 해제한다.
단점 : 롤백되지 않으므로 사용에 신중해야 한다.
시노님(Synonym)
* 시노님(Synonym)
오라클 오브젝트를 위한 앨리어스(alias)이다.실제적인 오라클 오브젝트가 아니라 오브젝트에 대한 직접적인 참조이다. 이 오브젝트는 테이블, 뷰, 스냅샷, 시퀀스, 프로시저, 함수, 패키지 또는 다른 시노님이 될 수 있다.
제약: 프로시저 안에 포함될 수 없다.
* 시노님 사용 이유
1. 데이터 사전에 대한 정의 외에는 어떤 스토리지도 요구하지 않는다.
2. sql코딩을 단순화한다.
3. 데이터베이스 보안을 개선한다.
4. 오라클 오브젝트에의 퍼블릭 액세스를 제공한다.
* 시노님 만들기
create public synonym emp FOR emp_01.emply;
create synonym emp for emp_01.emply
public을 쓰지 않으면 전용 시노님이 된다.(반대: 공용)
리모트의 데이타베이스를 참조하는 시노님은 다음과 같이 만든다.
create synonym part for master_part.part_number@corp;
* 시노님 사용하기
select * from emp;
시노님을 사용하지 않는다면
select * from emp_01.emply;
* 시노님 이름 바꾸기
rename dept to bus_unit;
인덱스와 시퀀스
(Indexs and Sequence)
* 인덱스 생성 지침
1. 테이블에서 행의 15%이하만을 검색하는 경우가 많을때
2. 인덱스 열은 다수의 테이블 조인을 개선하기 위한 연결에 이용될때
3. 행이 몇 안되는 작은 테이블은 인덱스 않는다.
4. 주요키와 고유키는 자동적으로 인덱스를 갖지만 대개는 모든 외부 키에도 인덱스를 만드는 것이 좋다
5. 열에 널 값이 없는것이 좋다
6. 테이블이 읽기 전용이면 인덱스가 많을수록 편리 하고 갱신이 많을경우엔 인덱스가 적을수록 좋다
예.emp테이블에서 phone열에 고유한 인덱스를 만든다.
create unique index i_emp_phone_uk on emp(phone);
또한 복수 열에 대해서 인덱스를 만들수도 있다.
예. create index i_emp_city_zip
on emp(city, zip);
=> 이런 경우 가장 많이 쓰이는 열을 우선 놓는다. 모두 16개까지 열을 넣을 수 있다.
* 인덱스 생성시의 파라메터
1. tablespace : 인덱스가 생성되는 테이블 스페이스를 지정할 수 있다.
2. cluster : 클러스터를 위하여 인덱스된 클러스터 키의 이름을 제공
3. pctfree : table의 pctfree와 같은 용도
4. initrans : 인덱스된 세그먼트의 데이터 블록에 트랜잭션 엔트리를 위하여 초기에 얼마나 많은 스페이스를 할당될 수 있는지 정의
5. maxtrans : 인덱스된 데이터 블록에 트랜잭션 엔트리를 위하여 초기에 할당할 수 있는 최대 스페이스 수를 정의
6. storage : 스토리지 파라미터로서 작은 영역을 많이 갖는것 보다 많은 영역을 적게 갖는게 좋다.
* 인덱스 없애기
drop index i_emp_city_zip;
* 클러스터된 인덱스
클러스터된 테이블이 만들어지고 나면 데이타 입력 전에 우선 그 크럴스터에 대한 인덱스부터 만들어야 한다.
* 시퀀스 만들기
create sequence s_empno
increment by 1
start with 1
nomaxvalue
nocycle
cache 15;
=> s_empno라는 시퀀스를 만들고 번호들의 시퀀스 리스트는 1의 값부터 시작되며 연속되는 번호마다 1씩 증가한다. 이 시퀀스에는 최대값이 없으므로
그 시퀀스는 초기값으로부터 순환하지 않으며 서버의 퍼포먼드를 위해 메모리에 15개까지의 시퀀스를 미리 만들어둔다
* 시퀀스 확인
select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;
* 시퀀스 변경하기
alter sequence s_empno
maxvalue 50000
cache 20
cycle;
* 시퀀스의 사용
insert into emp (empno, first_name, last_name)
values (s_empno.nextval, 'thomas', 'smith');
* 시퀀스의 .nextval, .currval등의 의사열을 쓸 수 있는 상황
1. insert문에서 value절
2. select 문의 선택 리스트
3. update명령의 set 절
* 시퀀스의 .nextval, .currval등의 의사열을 쓸 수 있는 상황
1. distinct 절이 있는 select문
2. order by또는 group by절이 있는 select문
3. 연산자 union, minus또는 intersect에서 사용되는 select 문
4. 서브쿼리, 뷰 쿼리, 스냅샷 쿼리
뷰(Views)
* 뷰(view)를 사용하는 이유
1. 테이블의 사전 정의된 행과 열 세트에 대해서 액세스를 제한함으로써 데이터베이스 보안의 추가적인 단계를 제공
2. 기저 조인과 테이블 구조를 숨김으로써 사용자들에게 데이터 프리젠테이션을 간략하게 제공
3. 기본 테이블의 데이터와는 다른 재생으로 데이터를 표현한다.
* 뷰 만들기
create view emp_view as
select empno, f_name, l_name, deptno
from emp
where deptno=12
with check option constraint emp_cnst;
emp_view뷰는 deptno가 12인 행만을 인용하며 with check option은 뷰가 선택될 수 없는 행의 뷰가 만들어지거나 산출되도록 허용되지 않는 상황에서 insert, update문 같은 제한을 사용해서 뷰를 제작하게 된다. 따라서 deptno가 12인 행만이 뷰로 삽입될 수 있다.
또한 뷰를 만들때 여러 테이블을 조인하여 만들 수도 있다.
제약: 1. 행이 조인을 포함하는 쿼리를 사용해서 정의된 뷰로 삽입되거나 갱신되지 못하기 때문에 위의 예에서는 with check option이 상술되지 못한다.
2. for update절이나 order by절을 사용하지 못한다.
create view force는 테이블이 존재하지 않거나 사용자가 테이블에 대한 권한이 없어도 뷰를 만들게 한다.
반대로 create view noforce는 권한이 있거나 테이블이 존재 하여야만 뷰를 만들수 있다.
* 뷰 교체하기
뷰의 정의를 바꾸려면 뷰를 교체 해야 한다.
create or replace view emp_dept_name as
select f_name, l_name, empno, dept.deptno
from emp, dept
where emp.deptno = 15;
효과:
1. 데이터 사전에 있는 뷰 정의가 갱신된다. 모든 기저 오브젝트는 뷰 교체에 의한 영향을 받지 않는다
2. 원래의 뷰 정의에 있다가 교체된것에 정의되어 있지 않은 것은 드롭된다.
3. 교체된 뷰에 종속된 모든 뷰와 PL/SQL프로그램은 무효로 된다.
* 뷰 사용하기
select f_name, l_name, empno from emp_dept_name;
* 뷰가 사용가능한 절
select, insert, update, comment, lock table
* 뷰의 제한
1. 뷰의 쿼리가 join operation이나 set또는 distinct연산자, group by절, 또는 group함수를 포함하고 있을 때 insert, update 또는 delete연산을 수행하는데 뷰를 사용할 수 없다.
2. with check option 절을 이용하여 뷰를 정의할 경우에는 그 뷰를 사용하여 기저 테이블의 행을 삽입하거나 갱신할 수 없다.
3. default value절 없이 not null열이 정의될 때에는 뷰를 사용 하여 베이스 테이블에 행을 삽입할 수 없다.
4. 뷰가 decode표현식을 사용하여 만들어졌을 경우에는 그 뷰를 사용하여 행을 테이블에 삽입할 수 없다.
5. 뷰의 쿼리는 nextval, currval의사열을 참조할 수 없다.
6. 추가 프로세싱 타임으로 서버의 반응시간을 약간 감소시킨다.
* 뷰 재컴파일
alter view emp_dept compile;
스냅샷(Snapshots)
* 스냅샷
마스터 테이블이라고 하는 하나 이상의 테이블 쿼리의 결과를 포함하는 테이블 또는 전형적으로 리모트 노드에 위치하는 뷰. 본질적으로 리모트 데이터베이스의 로컬 카피라고 볼 수 있다. 읽기 전용이며. 갱신이 발생되지 않는다.
일단 스냅샷에 테이터가 캡춰되면 재생 사이클에 의해 재생될때까지 데이터는 변하지 않는다.
또한 내부적으로 스냅샷은 스키마 오브젝트의 집합이다.
* 스냅샷 제작 중에 만들어 지는 오브젝트
1. 스냅샷 쿼리의 결과를 수용하기 위해 리모트 노드에서 베이스 테이블이 만들어짐
2. 베이스 테이블에 대하여 쿼리가 주어질 때 사용될 읽기 전용 뷰가 이 베이스 테이블에서 만들어진다.
3. 주기적인 스냅샷 재생 프로세스를 위하여 로컬 노드에서 마스터 테이블에 뷰가 만들어진다
* 스냅샷 사용 이유
네트웍 통신량을 줄이고 어플리케이션 퍼포먼스를 개선할 수 있다.
* 스냅샷이 많이 사용되는 경우
1. 마스터 테이블이 잘 갱신되지 않지만 많이 액세스될대
2. 많은 리모트 사용자에 의하여 마스터 테이블이 액세스될때
* 스냅샷 설정시 필요한일
스냅샷의 제작, 사용, 재생, 올바른 특권설정
* 스냅샷 종류
1. 단순 스냅샷
단 하나의 테이블을 쿼리
2. 복합 스냅샷
복수의 테이블과 뷰를 쿼리할 수 있다.
* 스냅샷 준비
환경설정 스크립트 실행(catsnap.sql, dbmssnap.sql)
마스터 테이블을 수용하는 데이터베이스와 스냅샷을 수용하는 곳 모두에서 실행해야 함
* 스냅샷 만들기
create snapshot UK_emp
pctfree 15
pctused 55
tablespace user_01
storage(initial 50k
next 50k
pctincrease 10)
refresh fast
start with sysdate
next sysdate +1
as select * from emp@uk
* 스냅샷을 만들기 위해 필요한 특권
create snapshop, create table, create view
* 단순스냅샷은 다음을 포함하면 안된다.
group by, connect by, sub query, join, set 연산자
* 스냅샷의 장단점
단순 스냅샷: 재생이 발생할때 효율적, 쿼리 실행중엔 느려짐
복합 스냅샷: 쿼리 실행중에 효율적, 재생이 발생할때 비효율적
* 스토리지 파라미터 설정
복합 스냅샷이 재생중에 최대의 효과를 내기 위해서 pctfree는 0으로 설정되어야 하고 pctused는 100으로 설정해야 한다.
* 스냅샷 이용하기
select deptno, dept_name from dept
또한 스냅샷에 기초하여 시노님을 만들수도 있다.
create synonym emp for UK_emp;
* 한계
스냅샷은 읽기 전용이므로 update, insert, delete등은 불가능하다.
* 스냅샷 로그
스냅샷에서 재생을 수행하는데 필요한 프로세스의 양과 시간을 줄이는데 사용.
스냅샷 로그로부터 스냅샷을 재생하는 프로세스는 속성재생이라 한다.
스냅샷 로그를 사용하지 않고 스냅샷을 재생하는 것은 완전한 재생이라고 한다.
* 스냅샷 로그 만들기
create snapshot log on UK_emp
tablespace emp_01
storage (initial 20k next 20k pctincrease 25);
* 스냅샷 로그를 만들기 위한 조건
create table권한과 create trigger시스템 특권이 있어야 한다.
* 스냅샷 로그 Truncate하기( 로그가 너무 커지는것 방지)
execute dbms_snapshot.purge_log('master_table',3,'DELETE');
* 스냅샷 인덱스 하기
create index i_uk_emp on snap$UK_emp (deptno);
* 스냅샷 재생하기
오라클이 자동으로 스냅샷을 재생하게 만들려면 다음의 단계를 거치면 된다.
1. create snapshot문에 start with와 next파라미터를 지정한다. start with절은 첫번째 자동 재생 사이클 시간을 설정한다. next절은 자동 재생 사이의 간격을 지정한다.
2. 오라클은 start with와 next파라미터를 평가하여 이 정보를 데이터 사전에 저장한다. 이 파라미터는 장래의 한 시점으로 설정하여야 하며 그 렇지 않으면 에러난다.
create snapshot master_emp
pctfree 15
pctused 25
tablespace user_03
storage(initial 50k next 50k pctincrease 10)
refreshh start with round(sysdate+1) + 9/24
fast next trunc(sysdate, 'MONDAY') + 3/24
as select * from emp
스냅샷이 다음날 오전 9시와 그 후 매주 월요일 오전 3시에 재생될 것이라고 지정한다
* 재생 프로세스의 시간과 모드 변경
alter snapshot part_master mode fast;
* 빠른 스냅샷 재생 조건
1. 스냅샷이 단순 스냅샷이다.
2. 마스터 테이블에 스냅샷 로그가 있다.
3. 스냅샷이 마직막으로 재생되거나 만들어지기 전에 스냅샷 로그가 만들어 졌다.
* 스냅샷과 스냅샷 로그 DROP
drop snapshot UK_emp;
drop snapshot log on master_emp;
스냅샷 드롭위한 권한-> drop any table과 drop any trigger시스템 특권
* 커서란?
커서는 나중에 액세스하기 위하여 특정 문장을 수용할 전용인 메모리 구역을 사용자가 명명할 수 있게 해주는 구조
* implicit cursor
insert, delete, update문등이 사용
* explicit
사용자가 선언하며 복수의 행을 리턴하는 쿼리결과를 처리
커서(Cusors)
* 커서 사용 이유
커서는 복수 행 결과 세트를 한 번에 한 행씩 처리하는데 사용되고 커서는 어떤 행이 현재 액세스되고 있는지 추적하며 이것은 활성세트(쿼리로부터 리턴된 복수행)를 반복해서 처리할 수 있게 해준다
* 커서 사용 단계
1. 커서를 선언
2. 커서를 연다
3. 데이터를 커서에 불러온다
4. 커서를 닫는다
* 명시적(explicit) 커서 선언
선언하기 위해 커서의 이름을 붙이고, 커서를 쿼리와 결합한다.
declare
cursor c_parts is
select * from master_parts;
* 파라미터를 이용하여 선언하기
declare
cursor c_parts (v_part_qty number) is
select * from master_parts where parts_qty > v_part_qty
* 명시적 커서 열기
open c_parts
-> 커서는 그 활성 세트의 첫 행 바로 전으로 초기화 된다.
* 명시적 커서 속성
커서에 대한 유익한 정보를 얻기 위하여 사용
1. %isopen : 커서가 열려 있는지에 대한 불린 값 리턴
begin if c_cursor%isopen then fetch c_cursor into v_order_qty, v_order_price; else open c_cursor; end if; end;
2. %notfound : 가장 최근의 fetch실행에 의해 아무 행도 리턴되지 않으면 true
loop fetch c_cursor into v_ord_qty, v_ord_price; exit when c_cursor%notfound end loop; end;
3. %found : 가장 최근의 fetch가 행을 리턴하지 않을 때까지 True로 평가한다.
%notfound의 논리적 반대이다.
4. %rowcount : fetch에 의하여 리턴된 행의 총수가 되는 숫자 속성
* 명시적 커서 사용하기
커서는 이미 불러온 fetch정보를 다시 볼수 없기 때문에 다시 읽으려면 커서를 닫고 나서 처음부터 fetch를 다시 해야 한다.
* 명시적 커서 닫기
close c_orders;
* 암시적 커서(implicit cursor)
모든 sql문은 암시적 커서를 자동적으로 만든다.
* 패키지 커서
오라클은 커서의 명세가 패키지에 저장될 수 있도록 해준다. 이 명세에서는 러턴절이 사용되어야 한다. 커서에 대한 이 어프로치는 커서 명세를 바꾸지 않고도 커서를 바꿀수 있게 해준다.
create package emp_monthly as
cursor c_emp_sal return emp%rowtype;
emp emp_monthly;
create package body emp_monthly as
cursor c_emp_sal return emp%rowtype;
select emp_id, emp_salary from master_emp where hire_date > '01-JAN-1986';
end emp_monthly;
* 커서 for 루프
커서 for루프는 다음을 수행함으로써 코드화 작업을 단순화 할 수 있다.
1. 루프가 초기화될때 암시적 open이 실행된다.
2. 루프의 각 반복을 위하여 암시적 fetch가 실행된다.
3. 루프가 중지될 때 암시적 close가 실행된다.
begin for order_rec in c_cursor loop v_order_total := v_order_total+ ((order_rec.qty * order_rec.qty)*.95); insert into master_order (part_id, order_total) values (order_rec.part_id, v_order_total); end loop; commit end;
* 커서 앨리어스
쿼리 문이 표현식을 갖고 있을때 참조할때 문제가 생기는 경우 해결 방안
* 커서 퍼포먼스
커서 내부에서 실행 호출의 속도는 파리미터 cursor_space_for_time의 값을 조정하여 개선한다.
데이터 무결성(Data Integrity)
* 데이터 무결성
데이터 무결성은 테이블에 있는 열에 대하여 유효한 값을 제한하는 규칙을 정의한다
* 데이터 무결성 사용 이유
데이터 무결성 제한은 데이터베이스 테이블에 무효한 데이터 입력을 막는데 사용
* 데이터 무결성 이용 방법
데이터베이스의 테이블에서 정의됨
뷰와 테이블 시노님은 기저 베이스 테이블에서 정의된 무결성 제한에 따른다.
* 무결성 사용하는 곳
어플리케이션 보다 데이터 베이스 차원에서 하는 것이 효과적이다.
* 무결성 제한의 종류
1. 널형
2. 고유형
3. 주키 형
4. 참조형
5. 점검형
무결성 제한을 설정하기 위하여 create table 이나 alter table절에서 constraint절을 포함시킬 수 있다. 구문 포맷은 다음과 같다.
constraint constraint_name constraint_type
* 참조형 무결성 제한
서로 다른 테이블의 열 사이의관계를 지배하는 규칙을 정한다
alter table address
add constraint fk_state_abbr
foreign key (state_abbr)
references state_abbr (state_abbr)
* 외부 키의 삭제를 캐스케이드하기
on delete cascade는 자식 테이블에종속 행을 갖고 있는 부모 테이블에서 참조된 키 값을 삭제할 수 있게 해주며, 부모 테이블의 삭제가 발생할때 오라클은 자동적으로 그 자식 테이블에 있는 해당 행들을 삭제한다.
* 점검형 무결성 제한
check 무결성 제한은 참이 되어야 하는 조건을 명시적으로 정의한다.
제약: 서브쿼리를 포함할 수 없다. sysdate, uid, user, userenv함수를 포함불가능 curral, netval, leve, rownum의사열을 사용할 수 없다.
create table office_location
(office id number(5),
city varchar2(50)
constraint check_city
check(city in ("Dallas","Denver","Indianapolis"),
state varchar2(50));
* 무결성 제한 디스플레이하기
테이블:USER_CONSTRAINTS, USER_CONS_COLUMN
select constraint_name, constraint_type
from user_constraints
where table_name='mst_parts';
* 무결성 제한 유효화,무효화하기
alter table mst_parts
disable constraint s_parts_code_fk;
특히 Loader등을 통해 데이터를 로딩하는 동안과 프로세스를 임포트하고 엑스포트하는 동안이다.
* 무결성 제한의 시스템 뷰
1. all_constraints : 액세스 가능한 모든 테이블을 위한 제한 정의
2. all_cons_columns : 제한 정의에 있는 액세스 가능한 열에 대한 정보
3. user_constraints : 사용자의 테이블에 대한 제한 정의
4. user_cons_columns : 사용자가 소유하는 제한 정의에 있는 열에 대한 정보
5. dba_constraints : 데이타베이스의 모든 테이블에 대한 제한 정의
6. dba_cons_columns : 제한 정의에 있는 모든 열에 대한 정보
* 무결성 제한 drop하기
alter table mst_parts
drop constraints s_parts_code_라;
트랜잭션(Transactions)
* 트랜잭션 이란?
하나의 논리적 작업 단위를 구성하는 하나 이상의 sql문
* 트랜잭션의 효과를 데이타베이스에 확립시킴=> commit
* 트랜잭션의 효과를 데이타베이스에서 제거=> rollback
* 트랜잭션 사용 이유
사용자와 오라클 서버, 애플리케이션 개발자, 데이터베이스 개발자에게 데이터 일치성과 데이터 동시발생을 보장
* set transaction
1. 트랜잭션을 읽기 전용 트랜잭션이나 읽기-쓰기 트랜잭션으로 설정한다.
2. 현재 읽기-쓰기 트랜잭션을 지정된 롤백 세그먼트에 대입한다.
* 트랜잭션 커밋(commit)하기
커밋 하기 전까지 트랜잭션은 다음과 같은 특징을 갖는다.
1. DML은 데이터 베이스 버퍼에만 영향을 미친다. 따라서 변경사항이 백아웃 될수있다.
2. 롤백 세그먼트 버퍼가 서버에 만들어 진다.
3. 트랜잭션의 소유자는 select문을 사용하여 트랜잭션의 효과를 볼 수 있다.
4. 데이터베이스의 다른 용법으로는 트랜잭션의 효과를 볼 수 없다.
5. 영향을 받은 행들을 lock되며 다른 사용자들들 영향을 받은 행 안에 있는 데이터를 변경할 수 없다.
커밋이 실행되면 다음의 단계들이 발생한다.
1. 영향받은 행에 수용된 lock들이 풀린다.
2. 트랜잭션이 완료된 것으로 표시된다.
3. 서버의 내부 트랜잭션 테이블이 시스템 변경 번호를 생성하고, 이 번호를 트랜잭션에 대입하고, 번호들을 모두 테이블에 저장한다.
* commit과 함께 comment절을 사용하여 텍스트 스트링을 트랜잭션 ID와 함께 데이터 사전에 넣을수 있다.
* Two-Phase Commits
분산 commit을 수행(분산 데이타베이스 환경에서)
* 트랜잭션 롤백하기
트랜잭션을 롤백한다는 것은 현재 트랜잭션이 행한 모든 변경을 해제한다는 것을 의미 전체 트랜잭션의 롤백을 실행하려면 sqlplus상에서 rollback명령을 실행한다.
다른 방법으로 rollback to savepoint를 사용하여 일부만 rollback할 수 있다.
* 트랜잭션 전체 롤백할때의 단계
1. 현재 트랜잭션의 의하여 이루어진 모든 변경은 해당 롤백 세그먼트를 사용하여 해제할 수 있다.
2. 트랜잭션에 의하여 야기된 행에 있는 모든 lock이 풀린다.
3. 트랜잭션이 끝난다.
* 트랜잭션을 savepoint에 롤백할 때에는 다음의 단계가 발생한다.
1. 마지막 savepoint가 롤백된 후, sql문만 실행된다.
2. 롤백 명령에서 지정된 마지막 저장점(savepoint)이 보존되지만 그 저장점 이후의 다른 모든 저장점은 그 데이타 베이스로부터 제거된다.
3. 지정된 저장점 이후에 설정된 모든 lock은 풀린다.
4. 트랜잭션이 아직 활동 중이며 계속 될 수 있다.
* 저장점(savepoint) 만들기
savepoint master_credit;
* 저장점을 이용해 롤백하기
rollback to savepoint master_credit;
rollback to master_credit;
* 롤백 세그먼트
set transaction
use rollback segment rbs_mst_credit;
* 롤백 세그먼트의 크기는 롤백 세그먼트의 스토리지 파라미터에 의해정의됨
만약 애플리케이션이 매우 짧은 트랜잭션을 실행하고 있다면 롤백세그먼트는 항상 메모리에 캐시될 수 있을 정도로 작아야 할 것이다. 이렇게 되면 퍼포먼스가 향상된다.
* 롤백 세그먼트를 위해서는 높은 maxextents값을 설정해야 한다.
* 데이터 locking
2가지 레벨: 독점, 공유
* 이산 트랜잭션 관리(Discrete Transaction Management)
패키지(Packages)
* 패키지란?
관련된 스키마 오브젝트의 캡슐화된 집합. 이 오브젝트는 프로시저와 함수, 변수 상수, 커서, 그리고 예외(exception)를 포함할 수 있다.
* 패키지 구성 컴포넌트
1. 명세: 타입과 변수, 상수, 예외, 사용할 수 있는 서브프로그램을 선언
2. 본체: 커서와 함수, 그리고 프로시저를 완전히 정의하고 그 명세를 구현한다
* 패키지 사용 잇점
1. 어플리케이션 개발을 효율적으로 모듈에 조직하는 능력.
2. 패키지는 특권(privileage)를 보다 효율적으로 허가할 수 있게 해준다.
3. 패키지의 퍼블릭 변수와 커서는 그 세션동안 지속된다. 그러므로 이 환경을 실행하는 모든 커서와 프로시저가 패키지의 퍼블릭 변수와 커서들을 공유할수 있다.
4. 패키지는 프로시저와 함수에서의 오버로딩을 수행할 수 있게 해준다.
5. 패키지는 한 번에 여러 오브젝트를 로드함으로써 퍼포먼스를 개선한다.
* 패키지 사용법
SQL*DBA나 SQL*PLUS를 사용
* 패키지에 필요한 힌트
1. 기존의 오라클함수와 중복되는 패키지를 쓰는 것을 피한다.
2. 재사용에 대비해 간단하고 범용적으로 만든다.
3. 어플리케이션을 설계한 후 패키지 본체를 설계한다. 모든 사용자가 볼 수 있도록 하고 싶은 오브젝트만 패키지 명세에 넣는다.
4. 너무 많은 아이템을, 특히 컴파일링을 필요로 하는 아이템을 패키지 명세에 넣는 것을 피한다.
* 패키지 명세
패키지 명세에는 패키지 이름의 퍼블릭 선언과 모든 인수의 데이터 타입과 이름이 포함된다.
* 패키지 명세 선언
create package inv_pck_spec as
function inv_count(qty number, part_nbr varchar2(15))
return number;
procedure inv_adjust(qty number);
end inv_pck_spec
* 본체가 없는 패키지 선언
create package inv_constings is
type inv_rec is record
(part_name varchar2(30),
part_price number,
part_cost number);
price number;
qty number;
no_cost exception;
cost_or exception;
END inv_costings;
* 패키지 만들기
1. 명세 만들기
create or replace package inv_pcg_spec as
function inv_count(qty integer, part_nbr varchar2(15))
return integer;
procedure inv_adjust(qty integer);
end inv_pck_spec;
위의 예에서 create or replace를 썼는데 이렇게 하면 기존 패키지와 관련된 허가권한은 그대로 유지된다.
2. 본체 만들기
create or replace package body inv_control is
function inv_count
(qty integer,
part_nbr varchar2(15))
return integer is;
new_qty:= qty*6;
insert into mst_inv values
(new_qty, part_nbr);
return(new_qty);
end inv_count;
procedure inv_adjust(qty integer);
begin
delete from user_01.mst_inv
where inv_qty<10000;
end;
begin -- package initialization begins here
insert into inv_audit values
(sysdate, user);
end inv_control;
* 패키지 서브프로그램 호출
1. 오라클이 사용자 액세스를 확인한다.
2. 프로시저 유효성을 확인한다.
3. 패키지 서브프로그램이 실행된다.
* 패키지 서브프로그램 참조
package_name.type_name
package_name.object_name
package_name.sunprogram_name
* 패키지를 위한 시노님
1. 패키지의 이름과 소유자의 정체를 감출수 있게 해준다.
2. 리모트에 저장된 패키지를 위하여 위치의 투명성을 제공해 준다.
* 패키지 재컴파일
본체
alter package inventory_pkg compile body;
alter package inventory_pkg compile package;
모든 패키지는 오라클 유틸리티 dbms_utility를 사용하여 재컴파일할 수 있다.
execute dbms_utility,compile_all;
* 패키지 drop하기
drop package body inventory;
drop package inventory;
* 전용패키지 오브젝트와 공용 패키지 오브젝트
패키지 본체 안에서 선언된 오브젝트는 그 패키지 안에서만 사용할 수 있다.
* 패키지 상태
유효 아니면 무효상태이다.
* 오버로딩
오라클은 동일한 패키지에 있는 여러 프로시저가 동일한 이름을 갖는 것을 허용한다
* 패키지 정보 리스팅
all_errors
all_source
dba_errors
dba_object_size
dba_source
user_errors
user_source
user_object_size
트리거(Triggers)
* 트리거란?
결합된 테이블에 대하여 insert, update, delete명령이 발생될때 implicit하게 생행되는 프로시저
트리거는 하나의 단위로서 실행되는 sql과 pl/sql문을 포함할 수 있으며 다른 프로시저와 트리거를 호출할 수 있다.
트리거는 insert, update, delete명령 전후에 호출되며, 제작자는 DML문과 관련하여 언제 실행될 것인지를 지정한다
트리거는 테이블의 뷰나 시노님에서가 아니라 테이블에서 정의되지만 뷰의 베이스 테이블에 있는 그 뷰에 대하여 DML문이 발생될 때도 실행된다
* 트리거 사용 이유
1. 파생 열 값을 자동적으로 생성한다.
2. 보안 허가와 제한을 집행한다.
3. 투명 이벤트 로깅을 제공한다.
4. 테이블 액세스에 대한 통계를 수집한다.
5. 오라클 내부 오디팅 시스템을 보충하는데도 사용할 수 있다.
6. 분산 데이타베이스의 서로 다른 노드에 위치한 복제 테이블을 동기적으로 유지하는 데에도 사용 할 수 있다. 스냅샷에 이런 방식으로 트리거를 사용하는 것이 한 예이다.
* 트리거를 위한 지침
1. 미리 정의된 다른 연산이 실행될 때마다 특정 연산을 수행하고 싶을 때에만 트리거를 사용한다.
2. 트리거링 문을 위하여 실행되어야 할 중앙화된 글로벌 연산을 위하여 트리거를 사용한다.
3. 반복적인 트리거를 만들지 않도록 조심하여야 한다.
4. 코드를 되도록 작게 하기 위하여 트리거를 만드는 것을 삼가야 한다.
5. sql문이 실행될때 프로세스되는 행의 순서에 의존하여 트리거를 만들면 안된다
* 트리거 파트(요소)
1. 트리거 문: 어떤 sql문이 오라클로 하여금 트리거를 지시하게 만드는지 지정한다. 이 트리거문은 insert, update, delete문이 될 수 있다.
2. 트리거 제한: 지시할 트리거를 위하여 참이 되어야 할 조건을 지정. 이 조건은 pl/sql 조건이 아니라 sql조건이어야 한다. 그 조건은 when절에 상주할 수 있다.
3. 트리거 활동: 이것은 트리거가 지시될 때 오라클 싱행하는 pl/sql블록을 지정한다
* 트리거 타입
트리거 활동이 실행되는 횟수를 지정한다.
1. 행 트리거: 트리거 문에 의하여 테이블이 영향을 받을 때마다 실행된다.
예를 들어 update문이 실행되고 30행을 갱신할 경우 이 트리거가 30회 지시된다.
2. 문장 트리거: 트리거 문을 위하여 단 한번 지시된다.
예를 들어 update문이 실행되고 30행을 갱신할 경우 이 트리거는 단 1번 실행된다
* 트리거 타이밍
언제 시작할 지를 지정한다.
before트리거는 트리거링 문 앞에서 실행된다. 다음을 위해 실행된다.
1. insert나 update문의 트리거링을 끝내기 전에 열 값을 끌어내기 위하여
2. 트리거 활동을 실행해야 할 것인지 여부를 결정하기 위하여
after트리거는 트리거 문이 실행된 후 트리거 활동을 실행한다. 다음을 위해 시행됨
1. 트리거 활동 이전에 트리거링 문을 끝내고 싶을때
2. before트리거 활동 외에 추가의논리를 시행하기 위하여
* 트리거 만들기
create trigger 'trigger command'
trigger command: before, after, delete, insert, update, of, on, for each row
when, or replace
create trigger parts_delete
after delete on mst_parts
for each row
when(part_price < 100)
declare
.
.
.
end;
* 트리거 유.무효화 하기
alter trigger t_inv_count disable
* 트리거 실행
sql문은 트리거를 4개까지 실행할 수 있다. 이 4개의 트리거는 before row, after row, before statement, after statement트리거 이다.
* 트리거 변경
트리거를 명시적으로 변경할 수는 없다.
create trigger or replace를 사용하여야 한다.
또는 트리거를 drop했다가 다시 만들어야 한다.
* 트리거 재컴파일하기
기존 트리거의 재컴파일을 집행하려면 alter trigger명령을 사용한다. 트리거는 재컴파일될 때 유효화될 수도 있고 무효화될 수도 있다. alter triggfer명령은 기존 트리거의 정의를 변경하지 않는다.
* 스냅샷 로그 트리거
테이블을 위하여 스냅샷 로그가 만들어질때 오라클은 그 테이블에 after row트리거를 명시적으로 만든다. 그러므로 사용자가 정의한 after row 트리거는 이 동일한 테이블에서는 만들어질 수 없다.
내장 프로시저
* 내장 프로시저(stored procedure)
특정 작업을 수행하는 논리적으로 그룹지어진 sql과 pl/sql문의 셋이다.
선언파트와 실행파트로 구성되어 있다.
* 내장 프로시저 사용 이유
1. pl/sql은 특정 요구에 맞도록 프로시저를 맞출수 있게 해준다.
2. 이 프로시저들은 모듈적이다. 하나의 프로그램을 관리할 수 있는 잘 정의된 유니트로 구성되어 있다.
3. 프로시저들은 데이타베이스에 내장되기 때문에 재사용할 수 있다. 일단 유효화되면, 네트워크를 가로질러 재컴파일하거나 분포하지 않고도 반복해서 사용할 수 있다.
4. 데이타의 보완성을 개선한다.
5. 공유메모리 자원을 활용함으로써 메모리를 개선할 수 있다.
* 내장 프로시저 사용 필요조건
데이터베이스 관리자는 catproc.sql스크립트를 실행할 책임이 있다.
* 내장 프로시저 만들기
create procedure명령은 독립형 프로시저를 만든다.
* create문 에서 사용할 수 있는 파라미터의 리스트
1. in : 호출되는 서브 프로그램에 값을 패스해야 한다고 지정한다.
2. out: 호출하는 프로시저에 값을 리턴한다는 것을 지정한다.
3. inout : 위의 둘다
4. or release : 프로시저가 존재할 경우 다시 만든다.
create procedure user_o1.parts (part_id number, qty number)
as begin
update journal
set journal.qty = journal.qty + qty
where journal_id = part_id;
end;
* return문
프로시저에서 return문은 표현식을 포함할 수 없다.
* 내장프로시저 재컴파일하기
alter procedure로 가능하며, 독립형 내장 프로시저에서만 사용해야 하며 패키지의 일부인 프로시저에서 사용해서는 안된다.
* 내장 프로시저 호출하기
parts_sum(qty, wip_nbr);
execute parts_sum(qty, wip_nbr);
임포트와 익스포트 (Import and Export)
* 임포트, 익스포트를 사용하는 이유
1. 데이터 보관하기
2. 오라클의 새로운 릴리즈로업그레이드하기
3. 오라클 데이터베이스 간에 데이터 이동하기
4. 테이블스페이스 drop하기
5. 데이터베이스 defragment하기
* 익스포트
모드: 테이블 모드, 사용자 모드
테이블 모드
1. 테이블 데이터
2. 테이블 정의
3. 소유자의 테이블 허가
4. 테이블 제한
5. 테이블 트리거
사용자모드: 테이블 모드를 포함하여 다음의 것들
1. 뷰
2. 스냅샷
3. 클러스터
4. 데이타베이스 링크
5. 시퀀스
6. 전용 시노님
7. 공용 시노님
* 익스포트 준비
1. 저장공간 확인
2. 오라클 데이타베이스에 대한 create session시스템 특권이 있어야 한다
* 실행
exp를 타이핑 하고 유틸리티를 구동한다.
* 사용방법 3가지
1. 명령라인에서 그 파라미터 파일의 이름과 함께 exp명령을 입력
exp username/password parfile=filename
2. 명령라인에서 exp명령과 함께 그 뒤에 익스포트의 실행을 콘트롤하는 파리미터를 쓴다.
exp username/password tables=(emp,master_dept) grants=y
3. 명령라인에서 사용자명과 패스워드를 입력한다. (대화형으로 익스포트)
exp username/password
* 익스포트 파라미터
1. buffer : 데이터 행들을 불러오는데 사용되는 버퍼의 크기
정확한 버퍼크기 계산 공식: rows_in_arrary*maximin_row_size
2. file : 출력파일의 이름 초기값은 expdat.dmp
3. grants : 허가를 익스포트 할 것인지를 나타낸다. (y/n) 디폴트는 y
4. indexes : 인덱스도 익스포트할 건가?
5. rows : 테이블로부터 데이터 행을 익스포트 할 건인지..
6. constraints : 제한을 익스포트할 건가?
7. compress: 압축할건가?
8. full : 완전한 풀 익스포트인가?
9. owner: 익스포트할 사용자명. 초기값은 없다.
10. tables: 익스포트할 테이블 이름.
11. recordlength: 출력파일의 바이트 길이
12. inctype : 증분의 타입: complete, cumulative, incremental: 초기값 없음
13. parfile : 파라미터 파일의 이름
14. log : 로그파일의 파일명
15. statistics : 데이터베이스 옵티마이저 통계의 타입 - estimate, compute, none 초기값은 none
16. consistent : 이 파라미터는 익스포트 중에 다른 애플리케이션이 데이터베이스를 갱신하고 있을때 편리하다
* 익스포트 시간을 아낄려면
1. 필요한 테이블만 익스포트한다.
2. 일치된 것으로 남아 있을 필요가 있는 테이블들을 함께 익스포트한다.
* 익스포트 시퀀스
export나 import 중에는 시퀀스 번호를 액세스 하지 않는 것이 좋다.
* 익스포트 온라인 헬프
오라클은 온라인 헬프를 제공한다.
exp help=y
* 임포트 할때 수행 되는 순서
1. 테이블 정의 - 우선 테이블이 만들어 진다.
2. 테이블 데이터 - 새로 만들어진 테이블에 데이터가 로드된다.
3. 테이블 인덱스 - 데이터가 로드된 후 인덱스가 만들어 진다.
4. 통합된 제한과 트리거- 마지막으로 트리거가 임포트 되고 제한이 유효화 된다.
* 스냅샷 익스포트/임포트
스냅샷은 마스터 테이블과 옵션 스냅샷 로그, 마스터 테이블 트리거, 스냅샷 자체
다음과 같은 규칙에 따라서 임포트/익스포트 된다.
1. 마스터 테이블이 이미 존재하고 있을 경우 임포트 데이터는 그 스냅샷로그에 기록된다. 만약 존재하지 않으면 새로운 마스터 테이블이 임포트된다.
2. 오라클은 마스터 테이블이 만들어지고 로드된 후 모든 트리거를 임포트한다
3. 임포트 후에 스냅샷 로그는 비게된다. 그러므로 속성 재생을 시도하기전에 완전한 재생을 실행하여야 한다.
* 내장된 프로시저와 함수 그리고 패키지의 익스포트/임포트
내장된 프로시저와 함수 그리고 패키지가 임포트될 때 그것들은 그 본래의 시간 스탬프를 유지한다.
* 임포트 파라미터
buffer, file, grants, indexes, rows, full, tables, recordlength, inctype, parfile
log, destroy, commit, show(임포트파일과 파일내용 리스트 여부)
* 임포트 유틸리티 실행
1. imp username/password parfile=filename
2. imp username/password tables=(emp, master_dept) grants=y
3. imp username/password
* 임포트 온라인 헬프
imp help=y
SQL*Loader
* SQL*Loader란?
외부 파일로부터 오라클 데이타베이스 테이블에 데이터를 로드하는 오라클 툴이다. 다양한 포맷의 파일을 로드할 수 있고, 필터링도 수행할 수 있다.
복수의 테이블에 로드할 수 있으며 자세한 로그를 남긴다.
* SQL*Loader를 사용하는 이유
1. 서로다른 데이터 타입의 여러 데이터 파일로부터 데이터를 여러 오라클 테이블에 자동으로 로드한다
2. 선별해서 특정 레코드를 로드할 수 있도록 데이터를 필터링한다.
3. 특정 열을 위하여 자동적으로 고유 시퀀스 번호를 생성할 수 있다.
4. 에러 교정등을 제공한다.
5. 데이터를 데이타베이스에 넣기 전에 sql함수와 함께 입력 데이타를 조작할 수 있다.
* SQL*Loader 사용 단계
1. 입력 데이터 소스를 결정한다.
2. 콘트롤 파일을 만든다.
3. 로딩 프로세스를 결정한다
4. 결과를 확증한다.
* 입력 데이타
1. varchar
2. date
3. integer external
4. float external
5. decimal external
6. zoned
7. double
8. graphic
9. raw
10. vargraphic
11. smallint
12. byteint
* 콘트롤 파일이 제공하는 정보
1. 데이터가 로드될 위치(외부 파일 이름이나 실제 데이터 자체)
2. 해당 오라클 테이블 열에 입력 데이터의 매핑. 이때 어떤 데이터를 어떤 테이블의 어떤 열에 넣을것인가 결정
3. 데이터를 로드하기 위한 명세. 특정 테이블 열을 위한 데이터를 필터링하고 포맷하고 만들 논리를 포함
예1)
LOAD DATA
INFILE filename
INSERT
INTO TABLE table_name
(field_name1 POSITION(start:end) CHAR,
field_name2 POSITION(start:end) CHAR)
실예1)
LOAD DATA
INFILE 'DISK1:EMP_DEPT.DAT'
INSERT
INTO TABLE EMP
(EMP_ID SEQUENCE (MAX,1),
F_NAME POSITION(01:35) CHAR,
L_NAME POSITION(36:75) CHAR,
EMP_NBR POSITION(76:85) CHAR,
START_DATE SYSDATE)
INTO TABLE DEPT
WHEN DEPT !=' '
(EMP_NBR POSITION(76:85) CHAR,
DEPT POSITION(86:95) INTEGER EXTERNAL)
* SQL*Loader에서 SQL연산자
insert문의 values절에서 다양한 sql연산자가 사용가능하다.
sql문자열 뒤에 날짜 마스크가 평가된다.
field1 DATE 'mm-dd-yyyy' "RTRIM(:field1)"
job_title필드를 대문자로 변환한다.
job_title POSITION (34:55) CHAR "UPPER(:job_title)"
* SQL*Loader실행
sqlload contro=case1.ctl userid=userid/password
* 인수들
log : 로그 파일이 로딩 프로세스에 대한 정보를 저장하도록 지명
bad : sql*loader가 만든 불량 파일이 로딩 중에 야기된 레코드를 저장하도록 지명
discard : sql*loader가 만든 옵션 로그 폐기파일이 테이블에 삽입되었지만 에러를 포함하지 않은 레코드를 저장하도록 지명
discardmax : 로드를 종결하기 전에 허용된 discard레코드의 수를 지정
errors : 로드를 종결하기 전에 허용된 삽입 에러의 수를 지정
skip : 로드되어서는 안될 파일의 처음부터 논리적 레코드의 수를 지정
rows : 바인드 배열에 있는 행의 수를 지정한다.
* SQL*Loader출력파일
1. 로그파일
2. 실패 레코드 파일(bad record file)
3. 폐기 파일(discard file)
SQL*DBA
* SQL*DBA란? 사용 이유는?
오라클 데이타베이스의 운영을 효과적이고 효율적으로 관리할 수 있게 해주는 유틸리티
사용이유
1. 데이터베이스 인스턴스를 기동하고 중지함
2. 데이터베이스를 위한 새로운 사용자를 만듬
3. sql과 pl/sql문을 실행
4. 데이터베이스 퍼포먼스 통계를 모니터링
* 인터페이스
메뉴중심 대화형 모드
sqldba mode=screen
대화형 라인모드
sqldba mode=line
* sql*dba 명령들
1. archive log : 온라인 redo로그파일의 보관을 기동하고 중지함
파라미터: list, start, stop, netx, all, to
archive log parameter
2. connect : 지정된 사용자명을 사용하여 데이타베이스에 접근
connect username/password instance/database
3. describe : 테이블이나 내장프로시저, 또는 함수를 기술
4. disconnect : 사용자의 접속을 끊는다
5. execute : pl/sql문을 실행한다.
excute PL/SQL statement
6. exit : 무조건 현재 sql*dba세션을 떠난다.
7. host : 호스트의 명령을 사용한다.
8. print : 변수값을 프린트 한다.
9. recover : 하나 이상의 테이블스페이스나 데이터베이스파일, 또는 전체 데이터베이스에서 미디어 복구를 수행한다.
파라미터: database:테이터베이스 전체 복구(until time절 사용가능)
tablespace tablespace_name : 복구할 테이블 스페이스를 지정
datafile : 복구할 데이터 파일의 이름을 지정
10. remark : 스크립트 파일에 코멘트를 만드는데 사용
11. set : sql*dba세션의 특징을 설명하거나 변경
12. shutdown : 실행중인 오라클 인스턴스를 셧다운 시킨다.
파라미터:
abort: 가능한 가장 빠른 방법으로 셧다운과 함께 처리된다.이것은 인스턴스를 셧다운하지만 트랜잭션의 완료를 기다리지 않는다. 또한 데이터베이스를 닫거나 디스마운트한다.
immediate: 현재 호출이 완료되기를 기다리지 않고 데이터베이스를 닫고 디스마운트한다.
normal: 모든 호출이 완료되기를 기다리고 정상적인 방법으로 그 인스턴스를 닫느다.
13. spool : 모든 출력이 지정된 파일에 이코되게 만든다.
14. startup : 오라클 인스턴스를 기동한다.
파라미터:
exclusive : 그 데이터베이스가 현재 인스턴스에 의해서만 마운트되고 열릴수 있음을 의미
force : 현재 실행중인 인스턴스가 있을 경우엔 그 인스턴스를 재기동하기 전에 폐기(abort)옵션을 사용하여 현재 인스턴스를 셧다운 시킨다.
mount : 데이터베이스를 마운트하지만 열지는 않는다
15. variable : 현재 세션에서 사용할 바인드 변수를 선언한다.
데이터베이스 사용자 관리하기
* 사용자 만들기
사용자를 만들때 지정해야 할 것들
1. 사용자명
2. 초기값 테이블 스페이스
3. 임시 세그먼트 테이블 스페이스
4. 테이블스페이스 쿼터
5. 프로파일
create user user_01
identified by D82KLW9
default tablespace ts_101
temporary tablespace ts_temp
quota 5m on ts_101
quota 5m on ts_temp
quota 3m system
profile clerk;
이렇게 만들어진 사용자일지라도 connect 권한이 주어지지 않으면 데이타베이스에 연결할 수 없다.
암호 바꾸기
alter user tom_01
identified by 2JAW3HZ;
* 사용자 자원 관리하기
create profile을 통하여 사용자 자원을 관리하게된다.
프로파일을 만들때 다음의 자원 제한을 명시적으로 설정한다.
1. session_per_user : 사용자를 위한 동시발생 수를 제한
2. cpu_per_session : 하나의 세션을 위한 CPU시간 1/100 단위
3. cpu_per_call : 하나의 호출을 위한 cpu시간을 제한
4. connect_time : 한 세션의 총 소요시간을 제한
5. idle_time : 비 활성 최대 지속시간을 정의
6. logical_read_per_session : 한 세션에서 읽어들이는 데이터블록의 수를 제한
7. logical_reads_per_session : sql문을 처리하기 위한 호출에서 읽어들이는 데이터 블록수 제한
8. private_sga : 하나의 세션이 시스템 글로벌 구역에 예비할 수 있는 전용 스페이스의 양을 제한
9. composite_limit : 세션당 총 자원 코스트를 제한 이것은 sessions_per_user, connect_time, logical_reads_per_session, private_sga등을 제한한다. 이들 자원과 결합된 코스트를 정의하려면 sql명령 alter resource cost를 사용한다.
* 프로파일 만들기 예
create profile clerk limit
sessions_per_user 1
connect_time 560
cpu_per_call unlimited
idle_time 15;
alter문을 통하여 변경도 간능하다.
* 사용자 관리를 위한 데이터사전 뷰
포함되는 정보들
1. 데이터벵이스에 있는 사용자들의 리스트
2. 테이블과 크러스터, 그리고 인덱스를 위한 각 사용자의 초기 테이블스페이스
3. 각 현재 세션을 위한 메모리 사용
4. 임시 세그먼트를 위한 각 사용자의 테이블스페이스
5. 각 사용자를 위한 스페이스 쿼터
6. 각 사용자에 대입된 프로파일과 자원 제한
7. 각 애플리케이션 시스템 자원에 할당된 코스트
데이터사전뷰
all_users
user_users
dba_user
user_ts_quotas
dba_ts_quotas
user_resource_limits
dba_profiles
resource_cost
* 라이센싱
license_max_sessions
license_max_warning
license_max_users
데이터사전 뷰
v$license
select sessions_max s_max,
session_warning s_warn,
session_current s_curr,
session_highwater s_high,
users_max u_max
from v$license;
롤(Roles)
* 롤(Roles)이란?
롤은 개별 사용자나 다른 롤에 주어진 관련 권한 지명 그룹이다.
* 롤을 사용하는 이유
1. 단순한 특권관리
2. 개별적 동적 특권관리
롤의 특권이 수정될때 이 변경은 자동적으로 그 롤이 주어진 사용자에게 주어진다.
3. 애플리케이션이 지정하는 보안
* 롤 만들기
create role clerk
identified by Y8DS20J;
* 시스템이 정의하는 롤
오라클은 다섯개의 미리 정의된 롤을 오라클 서버에 제공한다
1. connect: alter session, create cluster, create database link,
create sequence, create session, create synonym, create table,
create view
2. Resource: create cluster, create procedure, create sequence,
create table, create trigger
3. DBA: admin옵션과 함께 모든 시스템 특권
4. Exp_full_database : 테이블 SYS.INCVID와 SYS.INCFIL그리고
SYS.INCEXP에서 select any table, back up_any_table, insert, delete, update
5. Imp_full_database : become user, writedown(Trusted 오라클 서버에서만)
* 롤 변경하기
롤을 유효화하는데 필요한 암호, 롤이 유효화될 때 그 롤을 확인할 것인지 여부.
alter role clerk identified by JL10C3Q
* 사용자의 롤 바꾸기
alter user tom_01
default role all except dba;
* 롤 drop
drop role clerk;
롤 부여하기
grant manager to NT9K3BH with admin option;
-> admin옵션을 가진 사용자 NT9K3BH에게 롤 manager를 부여한다.
* 롤 취소하기
revoke clerk from tom_01;
* 운영체계 허가와 취소
세션이 만들어질때 데이터베이스가 운영체계를 이용하여 사용자의 역할을 관리하려면 초기화변수 os_roles를 True로 설정하고 그 인스턴스를 재기동 하여야 한다.
특권(Privileges)
* 특권(Privileges)이란?
활동을 실행하거나 다른 사용자의 오브젝트에 액세스하는 것을 허가하는 것
특권엔 시스템 특권과 오브젝트 특권의 두가지.
시스템특권은 사용자가 일정한 타입의 오브젝트에서 활동을 수행할 수 있게 해주는반면 오브젝트 특권은 사용자가 특정 오브젝트에서 활동을 수행하도록 허가한다
* 특권 사용 이유
데이터베이스 보안의 가장 기초적인 통제 가운데 하나.
특권은 데이터베이스 관리자로 하여금 적당한 레벨의 보안을 데이터베이스 오브젝트와 사용자에게 할당할 수 있게 해준다.
* 특권을 사용하는 법
1. 시스템 특권
80여가지가 있다.
분석(모든 테이블, 인덱스 또는 클러스터)
. analyze any
오디트(데이터베이스에 있는 모든 스키마)
. audit any
. audit system
클러스터
. create cluster
. create any cluster
. alter any cluster
. drop any cluster
데이터베이스
. alter database
데이터베이스 링크
. create database link
인덱스
. create any index
. alter any index
. drop any index
기타 등등
* 시스템 특권 허가하기
grant create session to kabin, finance;
위 문장은 시스템 특권을 사용자 kabin과 finance롤에게 준다
* 시스템 특권 취소하기
revoke all from robert_c
* 오브젝트 특권
오브젝트 특권은 테이블이나 패키지 또는 뷰와 같은 특정 오브젝트에서 활동을 수행할 수 있는 허가이다.
* Public 사용자 그룹
사용자 그룹 public에 부여된 모든 롤은 모든 데이터베이스 사용자가 액세스할 수 있다.
* 특권정보 리스트
all_col_privs
user_col_privs
dba_col_privs
dba_sys_privs
all_col_privs_made
all_col_privs_recd
all_tab_privs
all_tab_privs_made
all_tab_privs_recd
user_role_privs
user_sys_privs
column_privileges
session_privs
데이터베이스 오디팅하기
* 데이터베이스 오디팅(auditing)이란?
선택된 데이터베이스 사용자의 활동을 감시하는 프로세스
일반적으로 세타입의 데이터베이스활동에서 수행된다.
1. 문장
2. 오브젝트
3. 특권
오디팅은 오디팅된 오퍼레이션, 그 운영을 수행하는 사용자, 그 운영 일시, 그리고 그 운영의 성패 표시와 같은 정보들을 기록한다. 이 정보는 오라클 오디트 트레일 테
최고관리자
07-04-18 18:03
0개
533회
Oracle
오라클 사용시 알아둬야 할 사항
추천
0
비추천
0
댓글목록
등록된 댓글이 없습니다.