WORKSHOP - TABLESPACE에 관하여...

오늘은 예전에 OCP 자격증을 취득하기위해 공부했던 내용 중 TABLESPACE에 관한 내용을 정리해보고자 한다. Tablespace 실습 및 내용을 정리하기 전에 간단히 테이블 스페이스 개념을 집고 넘어가려 한다. 테이블 스페이스는 테이블을 생성할 수 있는 물리적인 파일이다. 데이터베이스는 데이터를  테이블에 담는다. 그 테이블들은 어디에 담기게 되는것일까? 바로  물리적 파일인 테이블 스페이스에 담기게 되는 것이다. 데이터베이스를 만드는 것은 테이블스페이스를 만드는 것이라 보아도 되겠다.

 

 

Tablespace 실습 및 내용 정리.

[Tablespace Syntax]

CREATE[undo|tempoary|smallfile] TABLESPACE테이블스페이스 이름
DATAFILE[tempfile]'/경로/파일 이름1.dbf' SIZE integer [T/G/M/K] [reuse]
[autoextend on next n [M] maxsize n [[M]| unlimnited]
,'파일 이름2' SIZE integer [M/K]...
[ENCRYPTION USING '3DES168' DEFAULT STORAGE (ENCRYPTION) ->11gR1~
[MINIMUM EXTENT integer [M/K]]
[BLOCK integer [k]]
[DEFAULT STORAGE(
INITIAL integer [M/K]
NEXT integer [M/K]
MAXEXTENTS integer
PCTINCREASE integer)]
[ONLINE|OFFLINE]
[LOGGING|NOLOGGING]
[PERMANENT|TEMPORARY]
[EXTENT MANAGEMENT
DICTIONARY|LOCAL
AUTOALLOCATE|UNIFORM SIZE integer [M/K]]
[SEGMENT SPACE MANAGEMENT (MANUAL/AUTO)]

- 테이블스페이스 생성하기

SYS> create tablespace sales_tbs
2 datafile '/u01/oradata/orcl/sales_tbs01.dbf' size 10m
3 autoextend on next 1m maxsize 100m
4 online logging
5 extent management local uniform size 128k
6 segment space management auto;

Tablespace created.

- sales_tbs 테이블 스페이스에 table생성
SYS> create table hr.sales00
2 (order# number(10) primary key, order_date date default sysdate)
3 tablespace sales_tbs;

Table created.

- 데이터 입력

SYS> begin
2 for i in 1..1000 loop
3 insert into hr.sales00 (order#) values (i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

-- EM을 통해 Sales_tbs의 tablespace용량이 늘어나는 것을 확인할 수 있다.

SYS> ed
Wrote file afiedt.buf

1 begin
2 for i in 1001..10000 loop
3 insert into hr.sales00 (order#) values (i);
4 end loop;
5 commit;
6* end;
SYS> /

PL/SQL procedure successfully completed.

--Viewing Tablespace Information(사용현황조회)

SYS> select * from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
EXAMPLE 8952 4194302 .213432414
SALES_TBS 64 12800 .5
SYSAUX 32632 4194302 .778007878
SYSTEM 60976 4194302 1.45378182
TEMP 0 4194302 0
UNDOTBS1 296 4194302 .007057193
USERS 840 4194302 .02002717

7 rows selected.

SYS>select tablespace_name from DBA_TABLESPACES;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
INVENTORY
RMAN_TBS
UTEST02
UTEST03
UTEST04

TABLESPACE_NAME
------------------------------
UTEST05

12 rows selected.

-- tablespace 공간 변경 3가지 방법

SYS> --1. add datafile
SYS> alter tablespace inventory
2 add datafile '/u01/oradata/orcl/inventory02.dbf' size 10m;

Tablespace altered.

SYS> --2. resize
SYS> alter database datafile '/u01/oradata/orcl/inventory02.dbf' resize 20m;
Database altered.

SYS> --3. autoextend
SYS> alter database datafile '/u01/oradata/orcl/inventory02.dbf'
2 autoextend on next 1m maxsize 50m;

Database altered.

--tablespace 이동(datafile)

1. System TBS (offline 설정 안됨)

1) shutdown immediate
2) cp를 통해 복사(이동) - 물리적
3) startup mount
4) alter database rename file '원래' to '새로운'; - 논리적
5) alter database open;
6) 이전 file 삭제

2. Non-System TBS

1) 해당 TBS. offline 설정
2) cp를 통해 복사(이동) - 물리적
3) alter tablespace XXXX rename datafile '원래' to '새로운' ; - 논리적
4) 해당 TBS online설정
5) 이전 file삭제

-- tablespace삭제하기

1. TBS가 비어있는 경우
SQL> drop tablespace XXXXX;

2. TBS에 Object가 존재하는 경우 포함 삭제
SQL> drop tablespace XXXX including contents;

3. OSfile 동반삭제
SQL> drop tablespace XXXX including contents and datafiles;

4. 다른 TBS의 Object와 참조무결성 제약조건에 연결되어 있는 경우.
SQL> drop tablespace XXXX including contents and datafiles cascade constraints;

--close backup

ocp@orcl : /home/oracle> ls
20100823-3.sql Desktop insert.sql lob_04_04.sql sqlnet.log
afiedt.buf dict.sql inst.sql lock_trace.sql test1.sql
backup emp10000.sql jazn.sh lock_trace_user.sql v_scan.sql
const.sql hidden.sql lab_01_07.sql orcl_start.sh xbh.sql
creuser.sql holding.sql labs practice01_02.sql
ocp@orcl : /home/oracle> cd backup
ocp@orcl : /home/oracle/backup> ls
pfile_backup
ocp@orcl : /home/oracle/backup> mkdir close open rman rman_con archive
ocp@orcl : /home/oracle/backup> ls
archive close open pfile_backup rman rman_con
ocp@orcl : /home/oracle/backup> cd close/
ocp@orcl : /home/oracle/backup/close> cp /u01/oradata/orcl/* ./
cp: omitting directory `/u01/oradata/orcl/arch1'
ocp@orcl : /home/oracle/backup/close>

################################
############실습################
################################

1. WS1 Tablespace 생성 관리 실습

########################################
#####실습 1 데이터베이스의 저장구조##########
########################################

SYS>!t_scan.sql
/bin/bash: t_scan.sql: command not found

--자주 사용함으로 스크립트 생성

SYS>!vi t_scan.sql

SYS>!cat t_scan.sql
col tablespace_name format a15;
col file_name format a45;
select tablespace_name,status,contents,
extent_management,segment_space_management
from dba_tablespaces;

SYS>@t_scan

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
--------------- --------- --------- ---------- ------
SYSTEM onLINE PERMANENT LOCAL MANUAL
UNDOTBS1 onLINE UNDO LOCAL MANUAL
SYSAUX onLINE PERMANENT LOCAL AUTO
TEMP onLINE TEMPORARY LOCAL MANUAL
USERS onLINE PERMANENT LOCAL AUTO
EXAMPLE onLINE PERMANENT LOCAL AUTO
INVENTORY onLINE PERMANENT LOCAL AUTO
INSA onLINE PERMANENT LOCAL AUTO
RMAN_TBS onLINE PERMANENT LOCAL AUTO

9 rows selected.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa03.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /home/oracle/backup/insa02.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

SYS>select tablespace_name,bytes,file_name from dba_temp_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
TEMP 41943040 /u01/oradata/orcl/temp01.dbf

########################################
실습 2 사용자용 테이블스페이스 생성#####
########################################

SYS>drop tablespace insa including contents and datafiles cascade constraints;

Tablespace dropped.

SYS>create tablespace insa
2 datafile '/u01/oradata/orcl/insa01.dbf' size 1M
3 segment space management auto;

Tablespace created.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 1048576 /u01/oradata/orcl/insa01.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

8 rows selected.

SYS>@t_scan.sql

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
--------------- --------- --------- ---------- ------
SYSTEM onLINE PERMANENT LOCAL MANUAL
UNDOTBS1 onLINE UNDO LOCAL MANUAL
SYSAUX onLINE PERMANENT LOCAL AUTO
TEMP onLINE TEMPORARY LOCAL MANUAL
USERS onLINE PERMANENT LOCAL AUTO
EXAMPLE onLINE PERMANENT LOCAL AUTO
INVENTORY onLINE PERMANENT LOCAL AUTO
INSA onLINE PERMANENT LOCAL AUTO
RMAN_TBS onLINE PERMANENT LOCAL AUTO

9 rows selected.

##########################################
실습 3 : 사용자용 테이블스페이스 확장#####
##########################################

SYS>alter database datafile
2 '/u01/oradata/orcl/insa01.dbf' resize 2M;

Database altered.

SYS>select tablespace_name, bytes/1024/1024,
2 file_name from dba_data_files;

TABLESPACE_NAME BYTES/1024/1024 FILE_NAME
--------------- --------------- ---------------------------------------------
USERS 6.25 /u01/oradata/orcl/users01.dbf
SYSAUX 260 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 35 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 490 /u01/oradata/orcl/system01.dbf
EXAMPLE 100 /u01/oradata/orcl/example01.dbf
INSA 2 /u01/oradata/orcl/insa01.dbf
INVENTORY 100 /u01/oradata/orcl/inventory
RMAN_TBS 100 /u01/oradata/orcl/rman01.dbf

8 rows selected.

SYS>alter tablespace insa
2 add datafile '/u01/oradata/orcl/insa02.dbf' size 2M;

Tablespace altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

9 rows selected.

SYS>alter tablespace insa add datafile
2 '/u01/oradata/orcl/insa03.dbf' size 2M
3 Autoextend on Next 1M Maxsize 10M;

Tablespace altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

##########################################
실습 4 : 사용자용(일반) 데이터 파일 이동##
##########################################

SYS>alter tablespace insa offline;

Tablespace altered.

SYS>!cp /u01/oradata/orcl/insa02.dbf /home/oracle/backup/

SYS>alter tablespace insa rename
2 datafile '/u01/oradata/orcl/insa02.dbf'
3 to '/home/oracle/backup/insa02.dbf';

Tablespace altered.

SYS>alter tablespace insa online;

Tablespace altered.

SYS>!rm /u01/oradata/orcl/insa02.dbf

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

#################################################
실습 5 : System 테이블스페이스 데이터 파일 이동##
#################################################

SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>!cp /u01/oradata/orcl/system01.dbf /home/oracle/backup/system01.dbf

SYS>startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.
SYS>alter database rename
2 file '/u01/oradata/orcl/system01.dbf'
3 to '/home/oracle/backup/system01.dbf';

Database altered.

SYS>!rm /u01/oradata/orcl/system01.dbf

SYS>alter database open;

Database altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /home/oracle/backup/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

==> System Tablespace를 다시 원래 경로로 바꾸기!

SYS>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>!cp /home/oracle/backup/system01.dbf /u01/oradata/orcl/system01.dbf

SYS>startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.

SYS>alter database rename
2 file '/home/oracle/backup/system01.dbf'
3 to '/u01/oradata/orcl/system01.dbf';

Database altered.

SYS>!rm /home/oracle/backup/system01.dbf

SYS>alter database open;

Database altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

#################################################
실습 6. 테이블 스페이스의 삭제###################
#################################################

SYS>drop tablespace insa
2 including contents and datafiles cascade constraints;

Tablespace dropped.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

7 rows selected.

2. 1552 WS1-1-ch05. Creation Tablespace LAB (OCM-1)

문제1. dictionary를 생성할 수 없음 local로 생성 가능.

문제2.

SYS>create tablespace utest02
2 datafile '/home/oracle/backup/tstest/utest02.dbf' size 1m reuse
3 autoextend on next 1m maxsize 10m
4 extent management local uniform size 100k;

Tablespace created.

문제3.

SYS>create tablespace utest03
2 datafile '/home/oracle/backup/tstest/utest03.dbf' size 1m reuse
3 extent management local uniform size 100k;

Tablespace created.

문제4.

SYS>create tablespace utest04
2 datafile '/home/oracle/backup/tstest/utest04.dbf' size 2m reuse
3 extent management local autoallocate
4 segment space management auto;

Tablespace created.

문제5.

SYS>create temporary tablespace utest05
2 tempfile '/home/oracle/backup/tstest/utest05.dbf' size 10m reuse
3 extent management local uniform size 1m;

Tablespace created.

--tablespace 생성 확인

SYS>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ocp@orcl : /home/oracle> cd backup/tstest
ocp@orcl : /home/oracle/backup/tstest> ls
utest02.dbf utest03.dbf utest04.dbf utest05.dbf