[TableSpace]TableSpace의 개념

-----------------

TableSpace 개념

-----------------

소개

- Oracle은 논리적으로는 TableSpace에 , 물리적으로는 DataFile에 Data를 저장한다.

- Oracle DataBase는 TableSpace라는 하나 이상의 논리적인 저장 단위로 구성되며, 또한 각TableSpace는 DataFile이라는 하나 이상의 OS File로 구성된다.

- Table이나 Index 같은 Schema Object가 생성되면 DB내의 지정된 TableSpace에 해당 Segment가 생성된다. Create Table 명령에 TableSpace Option을 사용하여 특정 TsableSpace에 Table을 저장한다.

1. DBA는 TableSpace를 이용하여 다음과 같은 작업을 수행한다.

- DataBAse의 Data를 저장하기 위한 Disk 영역 할당제어

- DataBAse 사용자에게 특정 영역 할당

- 각 TableSpace를 Online또는 OffLine으로 설정하여 Data의 가용성 제어

- 부분적인 DataBase BackUp또는 Recovery 수행

- 성능 향상을 위해 Data 저장영역을 여러 장치에 나누어 할당

2. SYSTEM TableSpace

- DB가 생성될때 자동으로 생성되며, 전체 DB에 대한 Dictionary를 포함하고, 항상 Online으로 유지되어야 한다.

- 크기가 작은 DB는 SYSTEM TableSpace만을 필요로 할수도 있으나 일반적으로는 적어도 하나이상의 TableSpace를 추가로 생성하여 사용자 Data를 Dictionary와 별개로 저장해야 한다.이렇게 함으로서 Dictionary Data와 Schema 개체간의 경합을 줄일수 있다.

- Procedure/Function/Package/Package Body등의 내장 PL?SQL단위로 저장된 자료는 SYSTEM TableSpace안에 저장되어야 한다.

3. DataBase 확장 방법

- 기존 TableSpace에 하나의 DataFile을 추가하는 방법

(alter tablespace users add datafile ‘/usr2/oradata/users02.dbf’ size 500m;)

- 새로운 TableSpace를 생성(DataFile도 당연히 추가되어야 한다.)

(create tablespace users2 datafile ‘/usr2/oradata/users02.dbf’ size 500m

default storage (initial 300k next 300k);

default storage (initial 100k next 100k pctincrease 0);)

- 추가 영역이 필요할 때 마다 DataFile의 Size를 늘여주는 방법

(alter database datafile ‘/usr2/oradata/users01.dbf’ autoextend on next 20m maxsize 1000m;)

4. TableSpace의 Offline

- DBA는 DB가 Open되었을때 SYSTEM TableSpace를 제외한 다른 TableSpace에 대하여 Online또는 OffLine으로 설정할수 있다.

OffLine으로 설정하는 이유는 다음과 같다.

a. DB의 일부분의 사용을 제한할 때

b. Offline TableSpace BackUp을 수행할때(DB가 Archive Mode로 운용중일때는 OnLine 상황 에서도 TableSpace의 BAckUp이 가능함)

c. .응용프로그램을 갱신또는 유지보수 하는 동안 응용프로그램과 해당 Table Group을 임시로 사용 못하게 함

- TableSapce의 Offline

a. 만약 TableSpace에 사용하는 RollBack Segment가 있다면 Offline으로 설정이 불가 함

b. TableSpace가 Offline 상태가 되거나 다시 Online상태가 될때 이러한 상태 변화는 SYSTEM TableSpace의 Dictionary에 기록되며,DB를 종료할때 TableSpace가 OffLine 이었다면 DB를 Mount하여 Open하여도 역시 해당 TableSpace는 Offline이다.

c. DB의 DBWR는 TableSpace의 DataFile에 기록할때 오류가 발생하면 해당 TableSpace를 Offline 시킨다.

d. 만약 Index와 Data를 서로다른 TableSpace에 분리하였을 경우

인덱스를 포함하는 TableSpace가 Offline인 경우에는 Query에서 여전히 Data를 Access할수 있다.그러나 Data를 포함하는 TableSpace가 Offline인 경우에는 Data Access불가함

5. 읽기 전용 TableSpace

- 기본목적은 DB의 정적부분의 백업 및 복구를 수행하지 않기위해

- 새로운 TableSpace를 생성하면 항상 읽기/쓰기 가능모드로 생성되면, 읽기 전용으로 설정할 경우에는 read only Option을 이용한다. 물론 이후에 read write Option을 사용하여 다시 쓸수있게 만들수 있다.

- Offline된 DataFile은 Access할수 없으며 읽기전용 TableSpace의 DataFile을 Online으로 설정하면 file을 읽을수 있게 되며, 연관된 TableSpace가 read write Option으로 설정되어야만 기록할수 있다. 읽기전용 TableSpace의 DataFile은 alter database 명령어에 datafile Option을 사용하여 독립적으로 onlin이나 Offline될수 있다.

- TableSpace를 Offlin으로 설정한 경우에도 읽기전용 TableSpace에 DataFile을 추가할수 없습니다. 즉 읽기전용 TableSpace를 갱신하려면 먼저 TableSpace를 read write로 만들어야 합니다.그런후 다시 read only로 설정합니다.

[스키마개체]Table이란?

a. Oracle에서 Table은 Data저장 영역의 기본단위이다.

b. 선택적으로 Table의 각 Column에 규칙을 정할수 있는데 이를 Constraints(무결성제약조건) 이라고 한다.한예로 Not Null은 무결성제약조건 입니다.

c. Cluster화되지 않은 Table을 작성할때 Oracle은 Table의 향후 Data를 보관하기위해 TableSpace에 Data Segment를 자동으로 할당한다.

Data Segment에 대한 저장영역 매개변수를 설정하여 (initial, next parameter등) Data Segment에 대한 할당된영역(extents)을 제어할수 있다.

Data Segment에 대해 pctfree와 pctused를 이용하여 Data Block의 빈영역에 대해 제어할수도 있다.

d. 전체행을 단일 Data Block에 Insert할수 있다면 Oracle은 행을 하나의 행조각으로 저장하나 전체행을 단일 Data Block에 Insert할수 없다면 기존 행을 갱신하여 행이 Data Block의 크기를 증가시키는 경우(Update된 Record가 이전의 Record보다 Size가 커지는 경우) Oracle은 여러 개의 행조각을 사용하여 Data를 저장한다. Oracle이 하나의 행을 두개이상의 행조각으로 저장하는 경우 Block을 통해 Chain화 되며, 체인화된 행조각은 부분의 RowID를 이용하여 서로 체인화 된다. 또한 체인화에 관계없이 각각의 행조각은 행/열 전체또는 일부에 대한 행 헤더 및 Data를 포함한다.

e. 행 헤더는 행조각,(체인화된 행조각의 경우)체인화, 행조각의 열,(Cluster인경우)클러스터 키등을 포함한다.

하나의 Block에 완전히 포함된 Cluster화 되지않은 행에는 적어도 3 Byte의 행헤더가 있으며, 행헤더 정보 다음에 열길이와 Data가 포함된다. 열 길이는 250 Byte이하를 저장하는 열의 경우

1 Byte,그이상인경우 3 Byte가 필요하며 열 Data에 선행한다.

열 Data에 필요한 영역은 Data Type에 따라 틀리며, 가변길이인 경우엔 영역은 증가또는 감소할수있다.영역을 보존하기 위해 Null은 열길이(0)만을 저장한다. Oracle은 Null Column에 대한 Data를 저장하지 않으며 후행하는 Null의 경우 행헤더가 새로운 행의 시작을 나타내므로 열길이를 저장하지 않는다.(예를들어 Table의 마지막세개의 Column이 Null인 경우 열에 정보를 저장하지 않는다.)

또한 각각의 행은 Data Block헤더의 행 Directory에 2 Byte를 사용한다.

f. Column순서는 Table의 모든 행에 대해 동일 하다.일반적으로 열은 Create Table명령문에 나열된 순서대로 저장되지만 꼭 그렇지는 않다. 예를들어 Long Data Type의 Column이 있는 Table을 Create하는 경우 항상 이 Column을 마지막으로 저장한다. 또한 alter table 등의 명령으로 Column을 Add하는 경우 새로운 Column을 마지막에 추가한다.

일반적으로 행이 적은 영역을 차지하도록 Null을 자주 포함하는 Column을 마지막에 두는 것이 좋다 만약 Table이 Long Column을 포함하면 Long Column이 마지막에 위치 하므로 이러한 이점을 살리기 힘들다.

g. ROWID는 각각의 행조각을 위치나 주소로 식별한다. 일단 행조각에 ROWID를 부여 하면 행조각은 IMPORT 및 EXPORT Utility를 사용하여 행을 삭제할 때까지 ROWID를 보유 합니다.

행의 Cluster Key값이 변경되면 행은 유일한 ROWID를 유지하면서 새로운 값에 대한 추가 포인터 ROWID를 얻는다.

h. Null은 행의 열에 값이 없음을 나타내는데 Data값을 가진 열사이에 위치하는 경우 Null 은 열길이(0)을 저장하는데 1 Byte가 필요함

[Table생성 예제]

create table test

(

id char(3) not null,

name varchar2(12) null,

addr varchar2(50) null,

constraint pk_test (id) primary key

using index

pctfree 10

pctused 60

storage

(initial 100k next 100k)

tablespace users_idx

)

storage

(initial 200k

next 200k

minextents 1

maxextents 121

pctincrease 0)

tablespace users;

[스키마개체]뷰(View)란?

A. 정의

- View는 하나 이상의 Table또는 다른 View에 포함된 데이터를 원하는대로 나타낸 것

- View는 내장질의또는 가상 Table등으로 표현할수 있다.

- View는 Table에서 파생되므로 둘사이에는 많은 유사성이 있다. 예를들어 Table과 같이 최대 254개의 Column이 있는 View를 정의할수 있고 뷰를 질의하고 몇가지 제한 사항을 사용하여 뷰를 갱신,삽입,삭제를 할수있다. 뷰에대해 수행되는 모든 작업은 뷰의 기본 Table에 있는 Data에 실제 영향을 주며 기본 Table의 무결성 제약조건과 트리거를 따른다.

- 뷰에는 저장영역이 할당되지 않으며 실제로 Data를 포함하지도 않는다.

B. 처리방법

- Oracle은 View를 정의하는 질의 Text를 Data Dictionary(user_views)에 저장한다,

- Oracle은 기존공유 SQL영역에 동일한 멸령이 들어있지 않을때만 새로운 공유 SQL영역에 뷰를 참조하는 명령문을 Parsing합니다. 따라서 뷰를 사용시 공유 SQL영역과 관련하여 메모리 사용이 감소하는 이익이 있다.

- Oracle은 원래 질의를 뷰정의 질의와 병합할 때 원래 뷰를 변형하여 뷰에 대한 질의에 인덱스를 사용할지를 결정한다.

Create view emp_view as

Select emp_no, ename, sal, loc

From emp

Where emp.deptno = dept.deptno

And dept.deptno = 10;

<사용자 Query>

select ename from emp_view

where empno = 9876;

select ename

from emp, dept

where emp.deptno = dept.deptno

and dept.deptno = 10

and emp.empno = 9876;

Oracle은 가능한 모든 경우에 뷰에대한 질의를 뷰정의 질의 및 기본뷰 질의와 병합한다. 또한 뷰를 참조하지않고 질의를 발생하는 것 처럼 병합된 질의를 최적화한다.

따라서 Column이 뷰 정의 또는 뷰에대한 사용자정의에서 참조되는지의 여부에 관계없이 모든 참조된 기본 Table의 Column에 대한 Index를 사용한다.

만약 뷰에대한 정의와 사용자 질의를 병합할수 없다면 Index를 사용하지 않을수 있다.

C. 갱신가능한 Join View

- Join View란 From절에 하나이상의 Table또는 View를 가지며, Distinct, group by, start with, connect by, rownum, union all, intersect, for update등의 절에서는 사용하지 않는다.

- 갱신가능한 Join View는 둘이상의 기본 Table 및 View를 포함하며 Update/Insert/Delete등의 작업이 가능하다.

- Dictionary View인 all_updatable_columns,윰_updatable_columns, user_updatable_colums 에는 갱신가능한 View의 Column을 나타낸다.

- Join View에 대한 Insert/Update/Delete등의 작업은 한번에 하나의 기본 Table에서만 가능하며, With Check Option을 사용하여 View를 정의하는 경우에는 반복된 Table의 모든 Join Column과 모든 Column은 갱신할수 없으며, Insert명령문은 허용되지 않는다.또한 with Check Option을 사용하여 뷰를 정의하고 키예약 Table이 반복되는 경우에는 뷰에서 행을 삭제할수 없다.

D. View생성

- Create View명령을 사용한다.

예] create view sales_staff as

select empno, ename, deptno

from emp

where deptno =10

with check option constraint sales_staff_cnst;

위에서 check option은 뷰가 선택할수 없는 행에 대해서는 Insert와 Update명령문이 실행되지 않는다는 제약 조건을 갖는다.

만약 위의 View에 deptno가 30인 행을 Insert하려고 하면 RollBack되고 오류가 발생한다.

E. View수정하기

위에서 만든 staff View를 재정의 하는경우

create or replace view sales_staff_view as

select empno, ename, deptno

from emp

where deptno =30

with check option constraint sales_staff_cnst;

F. View의 삭제

- drop view sales_staff_view;

[스키마개체]시퀀스(Sequence)란...

A. 개념

- Sequence는 순차적인 일련번호를 생성한다.(최대 38자리의 Oracle정수)

- Sequence는 다중 사용자환경에서 디스크 입출력 오버헤드나 Transaction Locking없이 고유한 순번을 생성하는데 용이하다.

- Sequence생성시 Sequence이름, 오름차순또는 내린차순여부, 번호간격등을 정의한다.

- Sequence번호는 Table과 독립적으로 생성되며,따라서 동일한 Sequence를 하나또는 여러 개의 Table에 사용할수 있다.

B. Sequence생성

- create sequence emp_sequence

increment by 1

start with 1

nomaxvalue

nocycle

cache 10;

cache Optiom은 일련의 Sequence를 미리 할당하고 메모리에 유지하면서 시퀀스를 빠르게 Access 할수있게 한다. 일련의 Sequence를 Cache하면 Sequence를 건너 뛸수있다. 예를들어 Instance가 비정상적으로 종료되면 캐시되었지만 사용안된 Sequence를 잃어버리게 된다. 즉 Cache를 상요하면 Sequence를 빠르게 Access할수 있으나 비정상적으로 종료돨 경우에는 번호가 건너 뛰게된다.

C. Sequence 변경

Alter sequence emp_sequence

Increment by 10

Maxvalue 10000

Cycle

Cache 20;

D. Sequence에 영향을 주는 변수

Sequence_cache_entries매개변수는 캐시될 시퀀스를 설정하다. 너무 낮으면 시퀀스값을 건너 뛸수도 있다. 예를들면 다섯개의 Cache된 Sequence를 사용하고 캐시가 다찼으며,매개변수 값이 4라면 현재 4개의 시퀀스가 Cache되었다면 캐시에서 최근에 가장적게 사용한 Sequence는 5번째 Sequence로 바뀌면서 이 Sequence의 모든 다른값(캐시된 마지막 Sequence까지)은 손실된다.

E. Sequence삭제

- drop sequence emp_sequence;

[스키마개체]동의어(Synonym)란?

-------------------

Synonym(동위어)

-------------------

A. 개념

- 동의어(Synonym)은 Table, View, SnapShot,Sequence,Procedure, Function, Package에 대한 별칭이다.

- 공용 및 전용 동의어의 두가지 종류가 있다. 공용동의어는 public이라는 특정사용자 그룹에서 소유하며 DB의 모든 사용자가 사용할수 있다. 전용동의어는 다른 사용자에 대해 전용동의어의 가용성을 제어할수 있는 특정 사용자의 스키마에 들어있다.

B. 생성

- 예를들어 scott의 Schema에 포함된 Emp Table에 대해 puiblic_emp라는 공용 Synonym생성

- Create public synonym public_emp for scott.emp;

- 이상과 같이 공용으로 생성하면 Oracle의 다른 사용자는 public_emp라는 별칭을 사용하여 Query 할수있다.

- tiger라는 User는 public_emp라는 별칭을 사용하여 다음과 같이 Query 할수있다.

Sqlplus>select * from public_emp;

C. 삭제

- drop public synonym public_emp