OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 언두 테이블스페이스에 일반 테이블 생성
언두 테이블스페이스는 트랜잭션 롤백, 읽기 일관성, 복구 등의 용도로 사용하는 특별한 테이블스페이스임
그렇기 때문에 기본적으로 언두 테이블스페이스에는 일반 테이블을 생성할 수 없음
하지만 히든 파라미터인 _undotbs_regular_tables 파라미터를 true 로 변경하면 언두 테이블스페이스에도 일반 테이블을 생성할 수 있음
평소에는 사용할 일이 없지만 히든 파라미터를 찾아보다 신기해서 한번 테스트해봄
참고로 테스트시 리두 로그스위치의 영향을 안받기 위해 redo size는 2gb로 진행함
테스트
사전 설정
테이블 생성 및 데이터 삽입 속도 테스트
테이블스페이스 삭제시 에러
테스트
사전 설정
redo 크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /oradata1/ORACLE19/redo01.log NO CURRENT 2048
1 2 /oradata1/ORACLE19/redo02.log YES INACTIVE 2048
1 3 /oradata1/ORACLE19/redo03.log YES UNUSED 2048
|
모두 2gb임
테이블스페이스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, online_status from dba_data_files;
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
SYSTEM 1 /oradata1/ORACLE19/system01.dbf 1 0 NO AVAILABLE SYSTEM
SYSAUX 3 /oradata1/ORACLE19/sysaux01.dbf 1 0 NO AVAILABLE ONLINE
IMSITS 5 /oradata1/ORACLE19/imsits01.dbf 2 0 NO AVAILABLE ONLINE
USERS 7 /oradata1/ORACLE19/users01.dbf 1 0 NO AVAILABLE ONLINE
UNDOTBS1 4 /oradata1/ORACLE19/undotbs01.dbf 1 0 NO AVAILABLE ONLINE
|
언두 데이터파일 크기 증가(UNDOTBS1)
|
1
2
3
|
SQL> alter database datafile 4 resize 3g;
Database altered.
|
일반 데이터파일 크기 증가(USERS)
|
1
2
3
|
SQL> alter database datafile 7 resize 3g;
Database altered.
|
테이블스페이스 사용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL>
col "TOTAL SIZE" format 999,999,999,999
col "USED SIZE" format 999,999,999,999
col "FREE SIZE" format 999,999,999,999
col "MAX FREE" format 999,999,999,999
col "TS Name" for a20
col "Used rate" for a10
set lines 200 pages 1000
select t.tn "TS Name",
round(t.sizes,2) "TOTAL SIZE",
(round(t.sizes,2) - round(f.sizes,2))"USED SIZE",
round(f.sizes,2) "FREE SIZE",
round(f.msizes,2) "Max Free",
round(((t.sizes - f.sizes) /t.sizes) * 100,2)||'%' "Used rate"
from ( select tablespace_name tn, SUM(bytes) Sizes
from dba_data_files
group by tablespace_name) t,
(select tablespace_name tn, SUM(bytes) sizes,
MAX(bytes) msizes
from dba_free_space
group by tablespace_name) f
WHERE t.tn=f.tn
order by "Used rate" desc;
TS Name TOTAL SIZE USED SIZE FREE SIZE Max Free Used rate
-------------------- ---------------- ---------------- ---------------- ---------------- ----------
SYSAUX 1,073,741,824 972,619,776 101,122,048 78,249,984 90.58%
SYSTEM 1,073,741,824 966,000,640 107,741,184 106,954,752 89.97%
UNDOTBS1 3,221,225,472 75,890,688 3,145,334,784 2,410,676,224 2.36%
USERS 3,221,225,472 15,400,960 3,205,824,512 3,205,496,832 .48%
IMSITS 2,147,483,648 1,376,256 2,146,107,392 2,146,107,392 .06%
|
현재 UNDOTBS1는 2%, USERS는 0.48% 사용중임
_undotbs_regular_tables 히든 파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col desc for a70
col name for a40
col current_value for a15
col default_value for a15
col default_t_f for a15
select
ksppinm "name",
ksppstvl "current_value",
b.ksppstdfl "default_value",
b.ksppstdf "default_t_f",
ksppdesc "desc"
from sys.x$ksppi a, sys.x$ksppcv b
where 1=1
and a.indx=b.indx
AND SUBSTR(a.KSPPINM, 1, 1) = '_'
and a.ksppinm in ('_undotbs_regular_tables');
name current_value default_value default_t_f desc
---------------------------------------- --------------- --------------- --------------- ----------------------------------------------------------------------
_undotbs_regular_tables FALSE FALSE TRUE Create regular tables in undo tablespace
|
기본값인 false임
이 파라미터를 변경하기전에는 언두 테이블스페이스 일반 테이블을 생성할 수 없음
|
1
2
3
4
5
|
SQL> create table undo_normal1 tablespace undotbs1 as select * from dba_objects;
create table undo_normal1 tablespace undotbs1 as select * from dba_objects
*
ERROR at line 1:
ORA-30022: Cannot create segments in undo tablespace
|
이렇게 에러가 발생함
히든 파라미터 true로 변경
|
1
2
3
|
SQL> alter system set "_undotbs_regular_tables" = true;
System altered.
|
변경된 파라미터 확인
|
1
2
3
4
5
|
SQL> show parameter _undotbs_regular_tables
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_undotbs_regular_tables boolean TRUE
|
정상적으로 변경됨
테이블 생성 및 데이터 삽입 속도 테스트
시간 측정을 위해 timing on
|
1
|
SQL> set timing on
|
언두 테이블스페이스에 테이블 생성
|
1
2
3
4
5
|
SQL> create table undots_tbl1 tablespace undotbs1 as select * from dba_objects;
Table created.
Elapsed: 00:00:00.23
|
0.23초 소요됨
일반 테이블스페이스에 테이블 생성
|
1
2
3
4
5
|
SQL> create table userts_tbl1 tablespace users as select * from dba_objects;
Table created.
Elapsed: 00:00:00.30
|
0.3초 소요됨
큰 차이 없음
언두 테이블스페이스에 더 대량으로 insert 수행(총 7회 insert)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
SQL> insert into undots_tbl1 select * from undots_tbl1;
73122 rows created.
Elapsed: 00:00:00.07
SQL> /
146244 rows created.
Elapsed: 00:00:00.12
SQL> /
292488 rows created.
Elapsed: 00:00:00.24
SQL> /
584976 rows created.
Elapsed: 00:00:00.49
SQL> /
1169952 rows created.
Elapsed: 00:00:01.00
SQL> /
2339904 rows created.
Elapsed: 00:00:02.05
SQL> /
4679808 rows created.
Elapsed: 00:00:06.50
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
|
insert가 완료됨(insert 중 log switch는 발생하지 않았음)
로그 스위치 수회 진행
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
|
일반 테이블스페이스에 더 대량으로 insert 수행(총 7회 insert)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
SQL> insert into userts_tbl1 select * from userts_tbl1;
73123 rows created.
Elapsed: 00:00:00.10
SQL> /
146246 rows created.
Elapsed: 00:00:00.13
SQL> /
292492 rows created.
Elapsed: 00:00:00.25
SQL> /
584984 rows created.
Elapsed: 00:00:00.50
SQL> /
1169968 rows created.
Elapsed: 00:00:00.98
SQL> /
2339936 rows created.
Elapsed: 00:00:02.48
SQL> /
4679872 rows created.
Elapsed: 00:00:06.48
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
|
insert가 완료됨(insert 중 log switch는 발생하지 않았음)
insert 시간 결과 표
| insert 회차 | insert 한 row 수 | insert 소요 시간(undotbs_tbl1) |
insert 소요 시간(userts_tbl1) |
| 1 | 73,123 | 0.07 | 0.10 |
| 2 | 146,246 | 0.12 | 0.13 |
| 3 | 292,492 | 0.24 | 0.25 |
| 4 | 584,984 | 0.49 | 0.50 |
| 5 | 1,169,968 | 1.00 | 0.98 |
| 6 | 2,339,936 | 2.05 | 2.48 |
| 7 | 4,679,872 | 6.50 | 6.48 |
insert 속도는 큰 차이가 없음
insert 시간 결과 그래프
insert 속도는 큰 차이가 없음
테이블 크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col segment_name for a20
select tablespace_name, segment_name, bytes/1024/1024 mb
from dba_segments
where segment_name in ('UNDOTS_TBL1', 'USERTS_TBL1');
TABLESPACE_NAME SEGMENT_NAME MB
-------------------- -------------------- ----------
USERS USERTS_TBL1 1472
UNDOTBS1 UNDOTS_TBL1 1472
|
extent 크기별 count 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
set lines 200 pages 1000
col segment_name for a20
col tablespace_name for a15
col extent_id for 999
col bytes for 9999999
select tablespace_name, segment_name, blocks, bytes/1024 extent_size_kb, count(*)
from dba_extents
where segment_name in ('UNDOTS_TBL1', 'USERTS_TBL1')
group by tablespace_name, segment_name, blocks, bytes/1024
order by tablespace_name, extent_size_kb;
TABLESPACE_NAME SEGMENT_NAME BLOCKS EXTENT_SIZE_KB COUNT(*)
--------------- -------------------- ---------- -------------- ----------
UNDOTBS1 UNDOTS_TBL1 8 64 16
UNDOTBS1 UNDOTS_TBL1 128 1024 63
UNDOTBS1 UNDOTS_TBL1 1024 8192 120
UNDOTBS1 UNDOTS_TBL1 8192 65536 7
USERS USERTS_TBL1 8 64 16
USERS USERTS_TBL1 128 1024 63
USERS USERTS_TBL1 1024 8192 120
USERS USERTS_TBL1 8192 65536 7
8 rows selected.
|
UNDOTBS1 테이블스페이스와 USERS 테이블스페이스 모두 동일하게 extent를 할당받음
테이블스페이스 삭제시 에러
신규 undotbs 생성
|
1
2
3
|
SQL> create undo tablespace undotbs2 datafile '/oradata1/ORACLE19/undotbs02.dbf' size 3g autoextend off;
Tablespace created.
|
default undo tablespace 를 UNDOTBS2 로 변경
|
1
2
3
|
SQL> alter system set undo_tablespace='UNDOTBS2';
System altered.
|
기존 undo tablespace UNDOTBS1를 삭제
|
1
2
3
4
5
|
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
|
에러 발생함
undotbs1에 생성된 일반 테이블을 제거해야 정상적으로 삭제가 가능함
|
1
2
3
|
SQL> drop table undots_tbl1 purge;
Table dropped.
|
기존 undo tablespace UNDOTBS1 삭제 재시도
|
1
2
3
|
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
|
정상적으로 삭제됨
결론 :
일반적으론 언두 테이블스페이스에 일반 테이블을 생성할수 없지만,
_undotbs_regular_tables 파라미터를 true로 변경하면 언두 테이블스페이스에 일반 테이블을 생성할 수 있음
일반 테이블스페이스와 비교했을때 insert 속도에는 큰 차이가 없었음
만약 테스트용으로 이 파라미터를 true로 변경하고 언두 테이블스페이스에 일반 테이블을 생성한 경우 추후 언두 테이블스페이스를 지우려할때 에러가 발생함
먼저 일반 테이블을 삭제한 뒤 언두 테이블스페이스를 삭제해야함
참조 :
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c bigfile과 smallfile 테이블스페이스의 extent 할당 단위 확인 (0) | 2025.03.03 |
|---|---|
| 오라클 19c rac 환경에서 undo 사용률 확인시 주의사항 (0) | 2025.02.18 |
| 오라클 23ai 신기능 xplan의 SQL Analysis Report (0) | 2025.02.03 |
| 오라클 23ai 신기능 xplan의 Hint Report 개선 (0) | 2025.01.27 |
| 오라클 AHF Fleet Insights 구성 가이드 (0) | 2025.01.12 |