Oracle Sqlplus – 2. TABLE SPACES에 대해 알아 보겠습니다.
2. TABLE SPACES
TABLE은 아래의 그림과 같이 2차원의 형태로 구성되며 한 개의 열(ROW, RECORD)는 각각의 ATTRIBUTE(FIELD, ITEM)으로 구성되며 각 ATTRIBUTE의 모임을 TUPLE이라고한다.
ATTRIBUTE의 속성은 다음과 같다.
| TYPE | 의 미 | |
| INTEGER | 정수형 | |
| NUMBER | 일반숫자 | |
| DECIMAL | 고정소수 | |
| FLOAT | 부동소수 | |
| CHAR | 문자 | |
| VARCHAR | 문자열 | |
| VARCHAR2 | 문자열 | |
| LONG | 2GB지원 | |
| DATE | 날짜형 |
2-1. TABLE의 구성
각 테이블의 구성체를 DOMAIN이라 하며 TABLESPACE는 DOMAIN으로 구성되어 있다.
2-2. DATABASE상태
(1) 현재 DB내의 가용공간을 확인한다.
select * from dba_data_files;
.TABLESPACE_NAME : TABLESPACE명
.FILE_ID : TABLESPACE의 물리적인 위치 ADDRESS
.BLOCK_ID : TABLESPACE의 물리적인 위치 ADDRESS
.BYTES : 각 TABLESPACE가 차지하고 있는 크기
.BLOCKS : 각 TABLESPACE가 차지하고 있는 크기로 1BLOCK=2048BYTE
(2) 현재DB를 구성하고 있는 파일들을 확인한다.
SQL> select * 2 from dba_tablespaces;
.FILE_NAME : Tablespace가 위치하고 있는 Directory
.FILE_ID : Tablespace의 데이터파일의 생성순서
.TABLESPACE_NAME : 각 테이블이 위치할 SPACE
.BYTES : Tablespace의 size
.BLOCKS : Tablespace의 size로 1 block은 2048byte
.STATUS : 각 데이터파일의 동작 상태를 나타낸다.
(3) tablespace의 이름과 storage parameter값들을 확인한다.
select * from dba_tablespaces;
.TABLESPACE_NAME : Tablespace명
.INITIAL_EXTENT : 초기 EXTENT의 값으로 BYTE로 나타난다.
.NEXT_EXTENT : 다음에 일어날 EXTENT의 값으로 BYTE단위로 나타낸다.
.MIN_EXTENTS : 초기 EXTENT값으로 DEFAULT는 1이다.
.MAX_EXTENTS : 최대 EXTENT를 일으킬 값으로 DEFAULT는 121이다.
.PCT_INCREASE : NEXT_EXTENT의 비율
.STATUS : TABLESPACE의 상태로 ONLINE/OFFLINE로 표시된다.
2-3. tablespace의 생성
(1) TABLESPACE의 생성
| SQL>create tablespace business 2* datafile’\BUSINESS01.dbf’ size 10k; 테이블 영역이 생성되었습니다. |
create tablespace business : business는 tablespace명
datafile’\BUSINESS01.dbf’ size 10k : size가 10KB인 datafile의 path와 datafile의 명칭
size가 10KB인 BUSINESS라는 명칭의 TABLESPACE가 생성된 것을 확인할 수 있다.
SQL> select * from dba_tablespaces;
BUSINESS TABLESPACE에 dul1이라는 table을 생성하려한다. 만약 BUSINESS TABLESPACE에 10KB를 초과하는 TABLE을 생성하려 한다면 TABLE은 생성되지 않는다. 이유는 Tablespace가 10KB로 설정되어있기 때문이다. 때문에 TABLE의 SIZE는 TABLESPACE를 초과할 수 없다는 것을 알수 있다.
[예] 정상적인 TABLE의 생성
initial size가 5KB인 dul1이라는 명칭의 tablespace가 생성되었다.
| SQL> create table dul1 2 (idno number(8), 3 snamek char(10), 4 addr char(60)) 5 tablespace business 6 storage(initial 5k); 테이블이 생성되었습니다. |
[예] 생성된 TABLE 학인
| SQL> select * from tab; TNAME TABTYPE CLUSTERID —————————— ——- ——— DUL1 TABLE |
2-4. TABLESPACE의 확장
scott/tiger의 table중 emp table에 있는 내용을 dul1에 insert한다. DUL1은 5KB의 SIZE를 갖고 있으며 5KB까지는 INSERT작업을 계속 수행할 수 있다. 만약 INSERT시키는 양이 5KB를 초과하더라도 TABLESPACE SIZE범위에서 TABLE의 SIZE를 계속 확장(EXTENT)시키며 INSER작업이 가능하게 된다.
| SQL>insert into dul1 2* select empno, ename, null 3* from SCOTT.empSQL> /(17회반복수행) 14 개의 행이 작성되었습니다. |
그러나 아래와 같이 17회 반복 수행을 하면 최종적으로 에러가 발생하게 되는데 이는 TABLESPACE의 SIZE를 초과하기 때문이다.
| SQL> /insert into dul1 * 라인 1 에 오류:ORA-01653: 테이블 SCOTT.DUL1를 5에 의해 BUSINESS 테이블 공간에서 확장할 수 없습니다 |
next extent를 할당할 수 없는 dul1 table의 상태를 확인한다.
SQL> select bytes, blocks,extents, initial_extent,next_extent 2 from user_segments 3* where segment_name=’DUL1′;
BYTES : 현재 TABLE DUL1의 SPACE(DEFAULT임)
BLOCK : 8192/2048 =4로 1BLOCK은 20487 BYTE임
EXTENTS : EXTENT를 일으킨 횟수로 표시된 수치만큼 EXTENT를 수행했음을 알수 있다.
INITIAL_EXTENT : EXTENT를 일으킬때의 SPACE로 초기 5,120B보다 1KB큰 6,144B부터 EXTENT를 일으킨다.
NEXT_EXTENT : 다음에 일어날 EXTENT SPACE로 따로 지정치 않을 경우 10KB씩 일어난다.
초기 5KB로 설정된 SPACE의 확장은 6KB를 기준으로 50%씩 EXTENT가 이루어진다. 초기 5,120B로 설정된 DUL1은 EXTENT를 일으킬때는 6,144B/2=3,072B를 EXTENT한다. 1회 EXTENT후 SPACE는 5,120+3,072=8,192B가 된다. 2회 EXTENT시에는 8,192/2=4,096B를 EXTENT하여야 하는데 BUSSINESS TABLESPACE의 전체 SPACE는 10KB(10240B)이기 때문에 8,192+4,096=12,288B(12KB)를 수용할 수 없으므로 EXTENT를 할수 없는 것이다.
[보기] EXTENT시 SPACE
| 초기 SPACE | NEXT EXTENT | EXTENT후 SPACE | |
| 1차 EXTENT | 5,120B | (6,144/2)=3,072B | 5,120+3,072=8,192 |
| 2차 EXTENT | 8,192B | (8,192B/2)=4096B | 8,192+4,096=12,288 |
그러면 늘어나는 TABLE의 SIZE를 어떻게 조절할 수 있는가? 간단히 tablespace에 data file을 추가하여 next entent를 일으킬 수 있도록 하는 것으로 해결할 수 있다. 물론 DISK에는 확장할만큼의 여유 SPACE가 있어야 한다.
[예] DATAFILE을 확장한다.
| SQL>alter tablespace business 2* add datafile ‘\BUSINESS02.dbf‘ size 5k; |
| SQL>select * from dba_data_files; |
| SQL>select bytes, blocks,extents, initial_extent,next_extent 2 from user_segments 3* where segment_name=’DUL1′ |
2-5. TABLESPACE의 DROP
TABLESPACE를 DROP시킬 경우 TABLESPACE내의 모든 TABLE도 함께 DROP된다. 그러므로 TABLESPACE를 DROP시킬 경우 TABLE을 EXPORT시켜 놓아한다. tablespace를 삭제시 scott/tiger에서는 tablespace를 삭제할 권한이 없으므로 에러가 발생하므로 system/manager로 connect하여 tablespace를 drop시킨다.
| SQL> drop tablespace business; drop tablespace business * 라인 1 에 오류:ORA-01031: 권한이 불충분합니다 |
TABLESPACE를 DROP시 tablespace내에 이미 table이 존재할 경우 에러가 발생된다.
| SQL> connect system/manager; 연결되었습니다. SQL> drop tablespace business; * 라인 1 에 오류:ORA-01549: 테이블 영역이 비어있지 않으므로 INCLUDING CONTENTS를 사용해 주십시오. |
tablespace를 drop시킬 때 이미 tablespace에 table이 존재한다면 존재하는 table을 drop시킨 후 tablespace를 drop시켜야 한다. 그러나 많은 table이 tablespace에 존재한다면 일일이 drop시키는것도 무리일 것이다. 이때는 ‘including contents’ statement를 사용하여 해결할 수 있다.
| SQL> drop tablespace business including contents; 테이블 영역이 삭제되었습니다. |
data file을 확인해보면 BUSINESS TABLESPACE가 DROP된 것을 알 수 있다.
SQL> select * from dba_data_files;
BUSINESS TABLESPACE가 DROP된 것을 확인할 수 있다.
SQL> select * from dba_tablespaces;
이상 “Oracle Sqlplus – 2. TABLE SPACES에 대해 알아 보겠습니다.”에 대해 알아보았습니다.
답글 남기기