데이터베이스

TABLESPACE의 CREATION과 DROP

웹관리자 작성
작성일 2005.04.13 04:17

5,011 조회
0 추천
목록

글검색

TABLESPACE의 CREATION과 DROP

현재상황
kang이라는 유저의 디폴트 테이블스페이스는 ts_kang이다.
현재 kang이라는 유저가 접속되어 있고 ts_kang의 status는 online상태이다.
system계정에서 테이블스페이스 ts_kang을 drop(삭제)하려고 한다.

SQL> select * from dba_tablespaces;

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO PLU
------------------------------ -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- ---------- --------- ---
SYSTEM 65536 65536 1 2147483645 50 65536 ONLINE PERMANENT LOGGING DICTIONARY USER NO
RBS 524288 524288 8 4096 50 524288 ONLINE PERMANENT LOGGING DICTIONARY USER NO
USERS 131072 131072 1 4096 0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER NO
TEMP 65536 65536 1 0 65536 ONLINE TEMPORARY LOGGING DICTIONARY USER NO
TOOLS 32768 32768 1 4096 0 32768 ONLINE PERMANENT LOGGING DICTIONARY USER NO
INDX 131072 131072 1 4096 0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER NO
DRSYS 65536 65536 1 2147483645 50 65536 ONLINE PERMANENT LOGGING DICTIONARY USER NO
TS_KANG 16384 16384 1 505 0 0 ONLINE PERMANENT LOGGING DICTIONARY USER NO
OEM_REPOSITORY 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO

9 개의 행이 선택되었습니다.

테이블스페이스가 empty상태가 아니라면 즉, 테이블스페이스내 데이터를 가진 segment들이 존재할 경우,
including contents를 사용하여 drop 시켜야 한다.

SQL> drop tablespace ts_kang;
drop tablespace ts_kang
*
1행에 오류:
ORA-01549: 테이블스페이스가 비어있지 않으므로 INCLUDING CONTENTS 옵션을 사용해 주십시오

SQL> drop tablespace ts_kang including contents;

테이블 영역이 삭제되었습니다.

SQL> select * from dba_tablespaces;

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO PLU
------------------------------ -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- ---------- --------- ---
SYSTEM 65536 65536 1 2147483645 50 65536 ONLINE PERMANENT LOGGING DICTIONARY USER NO
RBS 524288 524288 8 4096 50 524288 ONLINE PERMANENT LOGGING DICTIONARY USER NO
USERS 131072 131072 1 4096 0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER NO
TEMP 65536 65536 1 0 65536 ONLINE TEMPORARY LOGGING DICTIONARY USER NO
TOOLS 32768 32768 1 4096 0 32768 ONLINE PERMANENT LOGGING DICTIONARY USER NO
INDX 131072 131072 1 4096 0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER NO
DRSYS 65536 65536 1 2147483645 50 65536 ONLINE PERMANENT LOGGING DICTIONARY USER NO
OEM_REPOSITORY 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO

8 개의 행이 선택되었습니다.

위에서는 테이블스페이스가 online인 상태에서 바로 drop시켰다.
하지만 현재 활성화된 작업이 있다면 실패하게 된다.
이럴때는 테이블스페이스를 offline으로 변경하여 삭제한다.

SQL> alter tablespace ts_kang offline;
SQL> drop tablespace ts_kang including contents;

drop tablespace을 하고나면, DB의 control file에서 해당 데이터파일의 file pointer만 drop시킨다.
실제 데이터파일은 해당 디렉토리에 그대로 존재한다.
따라서 완전삭제를 위해서는 OS명령으로 해당 데이터 파일을 삭제해줘야한다.

drop할 테이블스페이스내에 primary key or unique제한조건을 가진 테이블을 가지고 있고,
다른 테이블스페이스에 있는 테이블이 이를 reference하는 foreign키를 가진다면

drop tablespace ts_kang including contents cascade constraints

로 child table의 foreign key constraints를 제거할 수 있다.

다음은 실제 테이블스페이스생성, 사용자추가, 테이블생성, 사용자변경, 사용자삭제, 테이블스페이스삭제의 과정이다.

SQL> select t.name, d.status, d.name from v$tablespace t, v$datafile d where t.ts# = d.ts#;

NAME STATUS NAME
------------------------------ ------- ------------------------------------------------------
SYSTEM SYSTEM C:ORACLEORADATAMADDOGSYSTEM01.DBF
RBS ONLINE C:ORACLEORADATAMADDOGRBS01.DBF
USERS ONLINE C:ORACLEORADATAMADDOGUSERS01.DBF
TEMP ONLINE C:ORACLEORADATAMADDOGTEMP01.DBF
TOOLS ONLINE C:ORACLEORADATAMADDOGTOOLS01.DBF
INDX ONLINE C:ORACLEORADATAMADDOGINDX01.DBF
DRSYS ONLINE C:ORACLEORADATAMADDOGDR01.DBF
OEM_REPOSITORY ONLINE C:ORACLEORADATAMADDOGOEM_REPOSITORY.ORA

8 개의 행이 선택되었습니다.

SQL> create tablespace ts_kang
2 datafile 'c:oracleoradatamaddog s_kang.dbf' size 5m
3 default storage( minextents 1 maxextents 50 initial 100k next 100k pctincrease 0 );

테이블 영역이 생성되었습니다.

SQL> select t.name, d.status, d.name from v$tablespace t, v$datafile d where t.ts# = d.ts#;

NAME STATUS NAME
------------------------------ ------- -------------------------------------------------------
SYSTEM SYSTEM C:ORACLEORADATAMADDOGSYSTEM01.DBF
RBS ONLINE C:ORACLEORADATAMADDOGRBS01.DBF
USERS ONLINE C:ORACLEORADATAMADDOGUSERS01.DBF
TEMP ONLINE C:ORACLEORADATAMADDOGTEMP01.DBF
TOOLS ONLINE C:ORACLEORADATAMADDOGTOOLS01.DBF
INDX ONLINE C:ORACLEORADATAMADDOGINDX01.DBF
DRSYS ONLINE C:ORACLEORADATAMADDOGDR01.DBF
TS_KANG ONLINE C:ORACLEORADATAMADDOGTS_KANG.DBF
OEM_REPOSITORY ONLINE C:ORACLEORADATAMADDOGOEM_REPOSITORY.ORA

9 개의 행이 선택되었습니다.

2명의 사용자를 생성한다.
테이블스페이스 drop시, maddog은 drop할 것이고 kang은 남겨둘 것이다.
SQL> create user kang identified by xxxxxx
2 default tablespace ts_kang
3 temporary tablespace temp
4 quota 1m on ts_kang;

사용자가 생성되었습니다.

SQL> create user maddog identified by xxxxxx
2 default tablespace ts_kang
3 temporary tablespace temp
4 quota 2m on ts_kang;

사용자가 생성되었습니다.

SQL> grant connect, resource to kang;

권한이 부여되었습니다.

SQL> grant connect, resource to maddog;

권한이 부여되었습니다.

사용자 kang으로 로그인한후 테이블을 만들고 데이터를 하나 넣는다.
SQL> connect kang/xxxxxx
연결되었습니다.
SQL> create table member
2 (
3 id varchar2(10),
4 password varchar2(10) not null,
5 name varchar2(10),
6 personnum number(13),
7 sex number(1) default 1 not null,
8 constraint member_pk primary key(id),
9 constraint member_ch_pn check( length(personnum) = 13 )
10 )
11 storage( initial 32k next 32k minextents 1 maxextents 30 pctincrease 0 )
12 pctused 75 pctfree 5
13 tablespace ts_kang;

테이블이 생성되었습니다.

SQL> insert into member values('maddog', 'hahaha', '강명규', 1234567890123, 1 );

1 개의 행이 만들어졌습니다.

사용자 maddog에도 동일하게 작업한다.
SQL> connect maddog/xxxxxx
연결되었습니다.
SQL> create table member
2 (
3 id varchar2(10),
4 password varchar2(10) not null,
5 name varchar2(10),
6 personnum number(13),
7 sex number(1) default 1 not null,
8 constraint member_pk primary key(id),
9 constraint member_ch_pn check( length(personnum) = 13 )
10 )
11 storage( initial 32k next 32k minextents 1 maxextents 60 pctincrease 0 )
12 pctused 75 pctfree 5
13 tablespace ts_kang;

테이블이 생성되었습니다.

SQL> insert into member values('maddog', 'hahaha', '강명규', 1234567890123, 1 );

1 개의 행이 만들어졌습니다.

여기서 잠깐 tablespace와 segment(segment type: table)의 저장절을 위시한 내용을 짚고 넘어가자

TABLE
create table member
(
id varchar2(10),
password varchar2(10) not null,
name varchar2(10),
personnum number(13),
sex number(1) default 1 not null,
constraint member_pk primary key(id) using index tablespace ts_index,
constraint member_ch_pn check( length(personnum) = 13 )
)
storage( initial 32k next 32k minextents 1 maxextents 60 pctincrease 0 )
pctused 75 pctfree 5
tablespace ts_kang;

insert into member values('maddog', 'hahaha', '강명규', 1234567890123, 1 );

create table member_detail
(
id varchar2(10),
address varchar2(50),
vocation varchar2(10),
constraint member_detail_fk foreign key(id) references member(id)
)

SQL> drop table member cascade constraints;

테이블이 삭제되었습니다.

storage줄의 괄호안 부분은 extent의 할당과 관련을 가진다.

initial: segment의 처음 extent의 크기를 지정한다. 여기서는 23k bytes이다.
현재 db block의 크기가 8k bytes로 설정되어 있다.
extent는 db block들의 합이므로 크기는 db block크기의 배수로 지정하는 것이 좋겠다.

next: 데이블내에 데이터가 많아져서 다음번째 extent를 할당해야 한다.
다음번째 증가하는 extent의 크기를 지정한다.
pctincrease가 0이므로 여기서는 다음 extent의 크기는 32k bytes이다.
원래, 다음 할당되는 extent의 크기는 다음과 같다.

( 마지막 할당된 extent의 크기 + 마지막 할당된 extent의 크기 x pctincrease(백분율임을 유의))

여기서는 pctincrease가 0이므로 항상 다음 extent의 크기는 32k bytes가 된다.
만일 여기서 pctincrease를 10으로 잡았다면 처음 extent는 32k, 2번째는 32k, 3번째는 32.1k(32k x 0.1),
4번째는 32.1k x 0.1, ... 이 될것이다.
extent는 db block의 집합이므로 만일 DB블럭크기에 맞지 않다면, extent의 크기는 DB블럭크기에 맞게 조정될 것이다.

minextents: 최소 extent의 개수, 참고로 롤백세그먼트는 항상 minextents의 개수가 2이상이어야 한다.

maxextents: 최대 extent의 개수. 이 값을 넘어서면 더이상 extent가 할당될 수 없다.

pctincrease: 다음 extent증가치(백분율)

그외: rollback segment의 경우 optimal이 사용가능하다.
freelists, freelist groups

그 밑줄의 pctused, pctfree는 DB Block의 공간할당과 연관된다.

pctused: db block크기의 몇 퍼센트까지 데이터를 저장하기 위해 사용할 것인가?
여기서는 75%를 지정했다. 따라서 블럭에 들어가는 row들이 블럭내 사용율 75%를
초과하면 다음 db block에 row를 저장할 것이다.

pctfree: update연산을 위해 db block에서 여분으로 둘 공간퍼센트

마지막 줄은 사용할 테이블을 지정한다.
지정하지 않으면 사용자의 default tablespace에 테이블을 생성한다.

TABLESPACE
create tablespace ts_test
datafile 'ts_test.dbf' size 1m
default storage
(initial 100k next 100k minextents 1 maxextents 10 pctincrease 0)
online;

tablespace는 테이블과 달리 storage앞에 꼭 default가 붙어야 한다.
storage절의 내용은 테이블과 동일.
pctused, pctfree사용못함

세그먼트(segment name: member, segment type: table)에 할당된
extent의 개수는 dba_segments에서 확인할 수 있다.

SQL> select segment_name, segment_type, tablespace_name,bytes, blocks, extents
2 from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS
-------------------- ------------------ ------------------------------ ---------- ---------- ----------
MEMBER TABLE TS_KANG 32768 4 1
PLAN_TABLE TABLE TS_KANG 122880 15 1
MEMBER_PK INDEX TS_INDEX 122880 15 1

이제 테이블스페이스 ts_kang을 삭제할 것이다.
우선, 사용자 maddog을 삭제하겠다.
SQL> connect system/xxxxxx
연결되었습니다.

drop할 계정이 자신의 shema에 이미 데이터를 가졌다면 cascade옵션을 줘야 한다.
SQL> drop user maddog;
drop user maddog
*
1행에 오류:
ORA-01922: 'MADDOG'(을)를 삭제하려면 CASCADE를 지정하여야 합니다

SQL> drop user maddog cascade;

사용자가 삭제되었습니다.

이제 테이블 스페이스를 drop하자.
drop하기 전에 테이블스페이스를 offline으로 만들어 활성화된 작업이 없도록 하자.
처음에 설명한대로 테이블스페이스내 데이터가 있다면 including contents옵션을 사용해야 한다.

참고
테이블스페이스의 옵션에서 offline외에 read only, read write옵션도 있다.
보통의 테이블스페이스는 read write상태로 읽기와 쓰기가 가능하다.
만일 테이블스페이스에 데이터를 읽기만 가능하고(select only),
쓰지 못하게 하려면(insert, update, delete not permitted)
read only옵션을 사용한다.

SQL> alter tablespace ts_kang offline;

테이블 영역이 변경되었습니다.

SQL> drop tablespace ts_kang;
drop tablespace ts_kang
*
1행에 오류:
ORA-01549: 테이블스페이스가 비어있지 않으므로 INCLUDING CONTENTS 옵션을 사용해 주십시오

SQL> drop tablespace ts_kang including contents;

테이블 영역이 삭제되었습니다.

tablespace ts_kang을 drop했을때 kang이 만든 member테이블과 그 안의
데이터들은 날아가버렸다.
사용자의 kang의 default tablespace(ts_kang)가 존재하지 않는다.
따라서 새로운 object를 생성할 수도 없다.

SQL> connect kang/xxxxxx
연결되었습니다.
SQL> select * from tab;

선택된 레코드가 없습니다.

SQL> create table test(id number);
create table test(id number)
*
1행에 오류:
ORA-00959: 테이블 영역 'TS_KANG' 가 존재하지 않습니다

사용자 kang의 default tablespace를 users로 변경한다.
SQL> alter user kang
2 default tablespace users;

사용자가 변경되었습니다.

SQL> connect kang/xxxxxx
연결되었습니다.
SQL> create table test(id number);

테이블이 생성되었습니다.

SQL> drop table test;

테이블이 삭제되었습니다.

끝

이전

롤과 권한에 대한 실습

작성일 2005.04.13 04:35
다음

아카이브모드 사용

작성일 2005.04.13 04:17

등록된 댓글이 없습니다.

로그인한 회원만 댓글 등록이 가능합니다.

메뉴
검색
클라우드포털

TABLESPACE의 CREATION과 DROP

공지사항

뉴스광장

작성자 정보

컨텐츠 정보

본문

관련자료

공지사항

뉴스광장