[Oracle] 테이블스페이스 타입에 따른 생성방법 (temporary)

봄·2025년 9월 1일

오라클 관리

목록 보기

67/163

[이론1] 테이블스페이스 타입의 종류 3가지

permanent
: 영구히 데이터를 저장하기 위한 테이블스페이스

temporary
: 정렬작업을 수행하기 위한 공간이며
with 절에 의해 만들어지는 임시 테이블을 저장하기 위한 공간

undo
: 롤백을 하기 위한 롤백 데이터를 저장하기 위한 공간

[이론2] 임시 테이블 스페이스는 언제 생성해야하는가?

💡
1. 데이터베이스를 생성할 때 생성 필수
2. 대량의 정렬작업을 수행할 때 임시 테이블스페이스를 생성해야함

[실습1] dw 쪽에 테이블 스페이스 이름과 타입을 조회하시오

select tablespace_name, contents
 from dba_tablespaces;

[실습2] temp2 라는 이름으로 temporary 테이블 스페이스를 생성하는데 사이즈를 20m 로 해서 생성하시오

create temporary tablespace temp2
 tempfile '/u01/app/oracle/oradata/ORA19DW/temp02.dbf' size 20m;

💡 임시 테이블스페이스를 언제 생성해야 하는가?

큰 인덱스를 생성하는 것과 같이 대량의 정렬작업을 하기 전에 기존의 작은 임시 테이블스페이스를 사용하게 되면, 정렬작업이 실패하거나 실패할 것으로 예상될 때, 위와 같이 큰 임시 테이블스페이스를 생성하고 default 임시 테이블스페이스로 설정하고 정렬작업하면 됨

[실습3] temp2 테이블스페이스를 기본 임시 테이블 스페이스로 생성하시오

create temporary  tablespace temp2
 tempfile '/home/oracle/temp02.dbf' size 20m;
 
select tablespace_name, contents
from dba_tablespaces;

select *
 from database_properties
 where property_name ='DEFAULT_TEMP_TABLESPACE';
 
alter database default temporary tablespace temp2;

select *
 from database_properties
 where property_name ='DEFAULT_TEMP_TABLESPACE';
 
create index  .....

[실습4] 2m 짜리 임시 테이블스페이스를 temp3 이라는 이름으로 만들고 이 임시 테이블스페이스를 기본 임시 테이블 스페이스로 변경하시오

create temporary tablespace temp3
 tempfile '/u01/app/oracle/oradata/ORA19DW/temp03.dbf' size 2m;

select tablespace_name, contents
 from dba_tablespaces;
 
select *
 from database_properties
 where property_name='DEFAULT_TEMP_TABLESPACE';
 
alter database default temporary tablespace temp3;

select *
 from database_properties
 where property_name='DEFAULT_TEMP_TABLESPACE';

[실습5] scott 유져로 putty 로 접속해서 다음과 같이 인덱스를 생성하시오

drop table mcustsum purge;

create table mcustsum
as
select rownum custno
, '2025' || lpad(ceil(rownum/100000), 2, '0') salemm
, decode(mod(rownum, 12), 1, 'A', 'B') salegb
, round(dbms_random.value(1000,100000), -2) saleamt
from dual
connect by level <= 1200000;

[실습6] scott 유져에서 dw 쪽에 putty 로 접속해서 mcustsum에 custno 에 다음과 같이 인덱스를 생성하시오

-- 기존에 임시 테이블 스페이스들을 전부 삭제 
drop  tablespace temp including contents and datafiles;
 
drop tablespace temp2 including contents and datafiles;

-- 메모리에서 정렬작업을 하는것을 수동으로 변경하겠다.
alter session set workarea_size_policy=manual;

-- 메모리에서 정렬작업을 할 수 있는 사이즈를 1 바이트로 지정하겠다.
alter session set sort_area_size=1;

create index  mcustsum_idx01
 on  mcustsum(salemm);
 
 2행에 오류:
ORA-01652: 128(으)로 테이블스페이스 TEMP3에서 임시 세그먼트를 확장할 수 없습니다

[실습7] 사이즈 2m 로 임시 테이블 스페이스를 temp4 로 해서 생성하고 temp4 를 기본 임시 테이블 스페이스로 설정하시오

create temporary tablespace temp4
 tempfile '/u01/app/oracle/oradata/ORA19DW/temp04.dbf' size 2m;
 
select *
 from database_properties
 where property_name='DEFAULT_TEMP_TABLESPACE';
 
alter database default temporary tablespace temp4;

select *
 from database_properties
 where property_name='DEFAULT_TEMP_TABLESPACE';

[실습8] scottdw 로 다시 새롭게 접속해서 아래의 테이블을 생성하고 아래의 인덱스를 생성하시오

-- 메모리에서 정렬작업을 하는것을 자동으로 변경하겠다.
alter session set workarea_size_policy=auto;

-- 메모리에서 정렬작업을 할 수 있는 사이즈를 1000 바이트로 지정하겠다.
alter session set sort_area_size=1000;

create table mcustsum2
as
select rownum custno
, '2025' || lpad(ceil(rownum/100000), 2, '0') salemm
, decode(mod(rownum, 12), 1, 'A', 'B') salegb
, round(dbms_random.value(1000,100000), -2) saleamt
from dual
connect by level <= 1200000;

-- 메모리에서 정렬작업을 하는것을 수동으로 변경하겠다.
alter session set workarea_size_policy=manual;

-- 메모리에서 정렬작업을 할 수 있는 사이즈를 1 바이트로 지정하겠다.
alter session set sort_area_size=1;

create index mcustsum2_indx1
 on mcustsum2(salemm);
 
 2행에 오류:
ORA-01652: 128(으)로 테이블스페이스 TEMP4에서 임시 세그먼트를 확장할 수 없습니다

[실습9] 다시 200mb 로 temporary 테이블 스페이스를 temp 라는 이름으로 생성하고 temp 테이블 스페이스를 기본 임시 테이블 스페이스로 설정하시오

create temporary tablespace temp
 tempfile '/u01/app/oracle/oradata/ORA19DW/temp.dbf' size 200m;
 
alter database default temporary tablespace temp;

select *
 from database_properties
 where property_name='DEFAULT_TEMP_TABLESPACE';
 
 
-- 자동으로 관리하도록 설정
alter session set workarea_size_policy=auto;

[실습10] 다시 temp5 라는 이름으로 사이즈 2m 로 해서 임시 테이블 스페이스를 생성하고 temp5 를 기본 임시테이블 스페이스로 지정하시오

create temporary tablespace temp5
 tempfile '/u01/app/oracle/oradata/ORA19DW/temp5.dbf' size 2m;
 
alter database default temporary tablespace temp5;

select *
 from database_properties
 where property_name='DEFAULT_TEMP_TABLESPACE';

[실습11] scottdw 로 접속해서 메모리에서 정렬작업을 일으키는 공간을 1바이트로 변경하고 order by 를 이용해서 과도한 정렬작업을 일으키시오

[oracle@ora19c ~]$ scottdw

SQL*Plus: Release 19.0.0.0.0 - Production on 월 9월 1 12:01:33 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

마지막 성공한 로그인 시간: 월 9월  01 2025 11:33:25 +09:00

다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SCOTT @ ora19dw > alter session set workarea_size_policy=manual;

세션이 변경되었습니다.

SCOTT @ ora19dw > alter session set sort_area_size=1;

세션이 변경되었습니다.


SCOTT @ ora19dw > select *
  2               from mcustsum2
  3               order by custno desc;
             from mcustsum
                  *
2행에 오류:
ORA-01652: 128(으)로 테이블스페이스 TEMP5에서 임시 세그먼트를 확장할 수
없습니다


SCOTT @ ora19dw >

[문제1] 위의 order by 절이 성공할 수 있도록 원래 temp 테이블스페이스로 기본 임시 테이블스페이스를 돌려놓고 다시 scottdw 로 접속해서 정렬 메모리 사이즈를 1바이트로 설정하고 order by 를 해보시오

오렌지에서

create temporary tablespace temp
 tempfile '/u01/app/oracle/oradata/ORA19DW/temp.dbf' size 2m;
 
alter database default temporary tablespace temp;

select *
 from database_properties
 where property_name='DEFAULT_TEMP_TABLESPACE';

putty에서

SCOTT @ ora19dw > select *
  2               from mcustsum2
  3               order by custno desc;
             from mcustsum
                  *
                  
1200000 행이 선택되었습니다.

SCOTT @ ora19dw >

[이론3] 임시 테이블스페이스의 공간을 사용하는 SQL

💡
1. 정렬을 일으키는 SQL
2. WITH 절
3. 임시 테이블

[이론4] 정렬을 일으키는 SQL

💡
1. order by
2. create index
3. sort merge join
4. 그룹함수(max, min)
5. 데이터 분석함수(rank, dense_rank)
6. minus, intersect, minus ← 과거 버전 12c까지는 정렬되었음

위와 같은 정렬작업을 하다가 temp 테이블스페이스의 공간부족으로 에러가 나면 해결방법은?

temp file 추가
기존 temp file 사이즈를 resize

[실습12] 현재 temp 테이블 스페이스의 사이즈를 확인하고 이 사이즈의 현재의 2배로 늘리시오

select *
 from database_properties
 where property_name ='DEFAULT_TEMP_TABLESPACE'; 

alter  tablespace  temp
 add tempfile '/home/oracle/temp01b.dbf' size 100m;
 
select tablespace_name, sum(bytes/1024/1024)
 from  dba_temp_files
 where tablespace_name='TEMP'
 group by tablespace_name;
 
alter database tempfile  '/home/oracle/temp01.dbf' resize 300m;
 
select tablespace_name, sum(bytes/1024/1024)
 from  dba_temp_files
 where tablespace_name='TEMP'
 group by tablespace_name;

[문제2] 기존 temp 테이블 스페이스의 temp01.dbf 의 사이즈를 100m로 줄여지는게 가능한지 확인하시오

alter database tempfile  '/u01/app/oracle/oradata/ORA19DW/temp01.dbf' resize 100m;
 
select tablespace_name, sum(bytes/1024/1024)
 from  dba_temp_files
 where tablespace_name='TEMP'
 group by tablespace_name;

--> 가능

봄

이전 포스트

[Oracle] 데이터베이스의 논리적 · 물리적 구조

다음 포스트

[Oracle] 테이블스페이스 타입에 따른 생성방법 (temporary)

오라클 관리

[이론1] 테이블스페이스 타입의 종류 3가지

[이론2] 임시 테이블 스페이스는 언제 생성해야하는가?

[실습1] dw 쪽에 테이블 스페이스 이름과 타입을 조회하시오

[실습2] temp2 라는 이름으로 temporary 테이블 스페이스를 생성하는데 사이즈를 20m 로 해서 생성하시오

[실습3] temp2 테이블스페이스를 기본 임시 테이블 스페이스로 생성하시오

[실습4] 2m 짜리 임시 테이블스페이스를 temp3 이라는 이름으로 만들고 이 임시 테이블스페이스를 기본 임시 테이블 스페이스로 변경하시오

[실습5] scott 유져로 putty 로 접속해서 다음과 같이 인덱스를 생성하시오

[실습6] scott 유져에서 dw 쪽에 putty 로 접속해서 mcustsum에 custno 에 다음과 같이 인덱스를 생성하시오

[실습7] 사이즈 2m 로 임시 테이블 스페이스를 temp4 로 해서 생성하고 temp4 를 기본 임시 테이블 스페이스로 설정하시오

[실습8] scottdw 로 다시 새롭게 접속해서 아래의 테이블을 생성하고 아래의 인덱스를 생성하시오

[실습9] 다시 200mb 로 temporary 테이블 스페이스를 temp 라는 이름으로 생성하고 temp 테이블 스페이스를 기본 임시 테이블 스페이스로 설정하시오

[실습10] 다시 temp5 라는 이름으로 사이즈 2m 로 해서 임시 테이블 스페이스를 생성하고 temp5 를 기본 임시테이블 스페이스로 지정하시오

[실습11] scottdw 로 접속해서 메모리에서 정렬작업을 일으키는 공간을 1바이트로 변경하고 order by 를 이용해서 과도한 정렬작업을 일으키시오

[문제1] 위의 order by 절이 성공할 수 있도록 원래 temp 테이블스페이스로 기본 임시 테이블스페이스를 돌려놓고 다시 scottdw 로 접속해서 정렬 메모리 사이즈를 1바이트로 설정하고 order by 를 해보시오

[이론3] 임시 테이블스페이스의 공간을 사용하는 SQL

[이론4] 정렬을 일으키는 SQL

[실습12] 현재 temp 테이블 스페이스의 사이즈를 확인하고 이 사이즈의 현재의 2배로 늘리시오

[문제2] 기존 temp 테이블 스페이스의 temp01.dbf 의 사이즈를 100m로 줄여지는게 가능한지 확인하시오

[Oracle] 데이터베이스의 논리적 · 물리적 구조

[Oracle] 테이블스페이스 타입에 따른 생성방법 (undo)

0개의 댓글