OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle Database 19.31.0.0
방법 : 오라클 19c CLOB 컬럼 STORAGE IN ROW 옵션별 저장 위치 확인 테스트
이전 게시글에서는 CLOB 컬럼 STORAGE IN ROW 옵션별 용량을 확인해보였음
참고 : 오라클 11gR2 CLOB 컬럼 STORAGE IN ROW 옵션별 용량 확인 테스트 ( https://positivemh.tistory.com/1418 )
본문에서는 CLOB 컬럼 STORAGE IN ROW 옵션별 저장 위치를 확인해봄
일반적으로 CLOB 컬럼에 들어가는 데이터가 4000byte가 넘어가면 LOB 세그먼트에 들어가는것으로 알고 있음
참고: https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/LOB-storage-with-applications.html#GUID-D3BD47D3-DD6A-4DC9-9BAF-5DBB60918087
하지만 이 기준은 BasicFile과 SecureFile 별로 조금 다름
BASICFILE의 경우 1982byte 보다 큰 1983byte 이상인 길이의 문자를 CLOB 컬럼에 삽입하게 되면 LOB 세그먼트에 별도로 저장됨(Out-of-line에 저장)
SECUREFILE의 경우 1984byte 보다 큰 1985byte 이상인 길이의 문자를 CLOB 컬럼에 삽입하게 되면 LOB 세그먼트에 별도로 저장됨(Out-of-line에 저장)
참고 : CLOB Is Stored Out-of-line When Insert CLOB Data Greater Than 1982 Bytes(KB139645)
본문에서는 CLOB 컬럼을 만들고 RPAD 함수를 이용해 데이터를 각각 1982byte, 1983byte, 1985byte(SECUREFILE) 삽입해보고 블록 덤프를 확인해서 실제로 어디에 저장되는지 확인해봄
BASICFILE과 SECUREFILE을 각각 나눠서 확인해봄
(참고로 11gR2까지는 BASICFILE, SECUREFILE 모두 1982byte까지 테이블 row에 저장되고 12c 이상부터는 SECUREFILE은 1984byte까지 테이블 row에 저장됨, 참고 : https://hrjeong.tistory.com/294)
테스트
1. BASICFILE + 1982byte 데이터
2. SECUREFILE + 1982byte 데이터
3. BASICFILE + 1983byte 데이터
4. SECUREFILE + 1985byte 데이터
테스트
1. BASICFILE + 1982byte 데이터
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DROP TABLE basic_1982 PURGE;
CREATE TABLE basic_1982
(
id NUMBER,
c_data CLOB,
ins_date DATE DEFAULT SYSDATE
)
TABLESPACE USERS
LOB (c_data) STORE AS BASICFILE basic_1982_lob
(
TABLESPACE USERS
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
);
|
dba_lobs로 정보 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_name for a15
col column_name for a10
col segment_name for a20
col index_name for a25
col securefile for a5
SELECT table_name, column_name, segment_name, index_name, securefile, chunk, in_row, pctversion, retention, cache, logging
FROM dba_lobs
WHERE table_name = 'BASIC_1982';
TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME SECUR CHUNK IN_ PCTVERSION RETENTION CACHE LOGGING
--------------- ---------- -------------------- ------------------------- ----- ---------- --- ---------- ---------- ---------- -------
BASIC_1982 C_DATA BASIC_1982_LOB SYS_IL0000059185C00002$$ NO 8192 YES 10 NO YES
|
현재 SECUREFILE이 아니고 CHUNK 크기는 8192(8k)임, IN_ROW가 Yes임
ENABLE STORAGE IN ROW 상태에서 LOB 값과 control information을 합친 크기가 약 4000 bytes 이하이면 테이블 row 내부에 저장될 수 있음
샘플 데이터 삽입(문자열 1982byte 길이 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
INSERT INTO basic_1982 (id, c_data)
values (1, TO_CLOB(RPAD('A', 1982, 'A')));
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
|
1건 삽입됨
clob 컬럼 실제 길이 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
SELECT COUNT(*) AS row_cnt,
MAX(DBMS_LOB.GETLENGTH(c_data)) AS len,
MAX(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1))) AS lenb
FROM basic_1982;
ROW_CNT LEN LENB
---------- ---------- ----------
1 1982 1982
|
1982byte CLOB임
논리 용량 vs CHUNK 기준 예상 용량
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
SELECT COUNT(*) AS row_cnt,
ROUND(SUM(DBMS_LOB.GETLENGTH(c_data)) / 1024, 2) AS logical_kb,
ROUND(SUM(CEIL(DBMS_LOB.GETLENGTH(c_data) / 8192) * 8192)
/ 1024, 2) AS chunk_est_kb
FROM basic_1982;
ROW_CNT LOGICAL_KB CHUNK_EST_KB
---------- ---------- ------------
1 1.94 8
|
1개 row의 논리 용량은 1.94kb, CHUNK 기준 예상 용량은 8kb임
실제 세그먼트 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col segment_name for a30
WITH lob_info AS
(
SELECT segment_name,
index_name
FROM user_lobs
WHERE table_name = 'BASIC_1982'
)
SELECT s.segment_name,
s.segment_type,
ROUND(s.bytes / 1024, 2) AS kb
FROM dba_segments s
WHERE s.segment_name = 'BASIC_1982'
OR s.segment_name IN (SELECT segment_name FROM lob_info)
OR s.segment_name IN (SELECT index_name FROM lob_info)
ORDER BY s.bytes DESC;
SEGMENT_NAME SEGMENT_TYPE KB
------------------------------ ------------------ ----------
BASIC_1982 TABLE 64
BASIC_1982_LOB LOBSEGMENT 64
SYS_IL0000059185C00002$$ LOBINDEX 64
|
LOB 세그먼트와 일반 세그먼트가 둘다 64kb여서 어디에 저장되었는지 아직 알수 없음
익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
col segment_name for a25
SELECT segment_name,
file_id,
block_id,
blocks
FROM dba_extents
WHERE segment_name in('BASIC_1982','BASIC_1982_LOB','SYS_IL0000059185C00002$$')
ORDER BY extent_id;
SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS
------------------------- ---------- ---------- ----------
BASIC_1982 4 759224 8
SYS_IL0000059185C00002$$ 4 814928 8
BASIC_1982_LOB 4 814920 8
|
각각 블록을 8개씩 할당 받아서 8 block * 8 kb(기본블록크기) => 64kb가 된것임
테스트 row의 물리 위치 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select rowid,
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(rowid, USER, 'BASIC_1982') AS file#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block#,
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS row#
from basic_1982;
ROWID FILE# BLOCK# ROW#
------------------ ---------- ---------- ----------
AAAOcxAAEAAC5W9AAA 4 759229 0
|
4번 파일에 759229번째 블록에 있음
해당 블록 덤프
|
1
2
3
4
5
6
7
8
9
|
SQL>
alter session set tracefile_identifier = 'basic_1982_block_dump';
Session altered.
예시) alter system dump datafile <file#> block <block#>;
alter system dump datafile 4 block 759229;
System altered.
|
덤프 경로 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select value
from v$diag_info
where name = 'Default Trace File';
VALUE
---------------------------------------------------------------------------------
/app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_457439_basic_1982_block_dump.trc
|
덤프 확인
|
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
|
$ vi /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_457439_basic_1982_block_dump.trc
...
data_block_dump,data header at 0xbf6a0064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0xbf6a0064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xfe7
avsp=0xfd3
tosp=0xfd3
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xfe7
block_row_dump:
tab 0, row 0, @0xfe7
tl: 4017 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [4000]
00 70 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 2f 1c 5b 0f 8c 09 00 00
00 00 00 0f 7c 00 00 00 00 00 01 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00
41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00
41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00
...
|
덤프에 00 41 00 41...이 반복되고 있음(0x41(A) 값이 반복 되고 있음)
테스트1 결론 :
실제 CLOB 문자 데이터 1982자
=> 내부 저장 시 00 41 형태로 약 2byte씩 저장됨
=> 1982 * 2 = 3964 byte
LOB control information 약 36 byte
=> 3964 + 36 = 4000 byte
col 1: [4000]의 00 41이 나오기 전까지의 앞 36개(36byte)
00 70 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 2f 1c 5b 0f 8c 09 00 00 00 00 00 0f 7c 00 00 00 00 00 01
블록 덤프에서 테스트 row를 확인해보면 row 전체 길이(tl)는 4017 byte이고 CLOB 컬럼에 해당하는 col 1의 길이는 4000 byte로 표시됨
또한 col 1 내부에는 00 41 값이 반복적으로 저장되어 있는데 이는 삽입한 'A' 문자열이 테이블 row 내부에 실제로 저장되어 있음을 의미함
즉 1982byte 길이의 CLOB 데이터는 LOB 세그먼트에 저장되지 않고 테이블 row 내부에 저장된 것으로 확인됨
2. SECUREFILE + 1982byte 데이터
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DROP TABLE secure_1982 PURGE;
CREATE TABLE secure_1982
(
id NUMBER,
c_data CLOB,
ins_date DATE DEFAULT SYSDATE
)
TABLESPACE USERS
LOB (c_data) STORE AS SECUREFILE secure_1982_lob
(
TABLESPACE USERS
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
);
|
dba_lobs로 정보 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_name for a15
col column_name for a10
col segment_name for a20
col index_name for a25
col securefile for a5
SELECT table_name, column_name, segment_name, index_name, securefile, chunk, in_row, pctversion, retention, cache, logging
FROM dba_lobs
WHERE table_name = 'SECURE_1982';
TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME SECUR CHUNK IN_ PCTVERSION RETENTION CACHE LOGGING
--------------- ---------- -------------------- ------------------------- ----- ---------- --- ---------- ---------- ---------- -------
SECURE_1982 C_DATA SECURE_1982_LOB SYS_IL0000059191C00002$$ YES 8192 YES NO YES
|
현재 SECUREFILE이면서 CHUNK 크기는 8192(8k)임, IN_ROW가 Yes임
ENABLE STORAGE IN ROW 상태에서 LOB 값과 control information을 합친 크기가 약 4000 bytes 이하이면 테이블 row 내부에 저장될 수 있음
샘플 데이터 삽입(문자열 1982byte 길이 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
INSERT INTO secure_1982 (id, c_data)
values (1, TO_CLOB(RPAD('A', 1982, 'A')));
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
|
1건 삽입됨
clob 컬럼 실제 길이 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
SELECT COUNT(*) AS row_cnt,
MAX(DBMS_LOB.GETLENGTH(c_data)) AS len,
MAX(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1))) AS lenb
FROM secure_1982;
ROW_CNT LEN LENB
---------- ---------- ----------
1 1982 1982
|
1982byte CLOB임
논리 용량 vs CHUNK 기준 예상 용량
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
SELECT COUNT(*) AS row_cnt,
ROUND(SUM(DBMS_LOB.GETLENGTH(c_data)) / 1024, 2) AS logical_kb,
ROUND(SUM(CEIL(DBMS_LOB.GETLENGTH(c_data) / 8192) * 8192)
/ 1024, 2) AS chunk_est_kb
FROM secure_1982;
ROW_CNT LOGICAL_KB CHUNK_EST_KB
---------- ---------- ------------
1 1.94 8
|
1개 row의 논리 용량은 1.94kb, CHUNK 기준 예상 용량은 8kb임
실제 세그먼트 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col segment_name for a30
WITH lob_info AS
(
SELECT segment_name,
index_name
FROM user_lobs
WHERE table_name = 'SECURE_1982'
)
SELECT s.segment_name,
s.segment_type,
ROUND(s.bytes / 1024, 2) AS kb
FROM dba_segments s
WHERE s.segment_name = 'SECURE_1982'
OR s.segment_name IN (SELECT segment_name FROM lob_info)
OR s.segment_name IN (SELECT index_name FROM lob_info)
ORDER BY s.bytes DESC;
SEGMENT_NAME SEGMENT_TYPE KB
------------------------------ ------------------ ----------
SECURE_1982_LOB LOBSEGMENT 128
SECURE_1982 TABLE 64
SYS_IL0000059191C00002$$ LOBINDEX 64
|
LOB 세그먼트는 128kb를 할당받았고 일반 세그먼트는 64kb를 할당받음
익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
col segment_name for a25
SELECT segment_name,
file_id,
block_id,
blocks
FROM dba_extents
WHERE segment_name in('SECURE_1982','SECURE_1982_LOB','SYS_IL0000059191C00002$$')
ORDER BY extent_id;
SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS
------------------------- ---------- ---------- ----------
SECURE_1982 4 814936 8
SYS_IL0000059191C00002$$ 4 814960 8
SECURE_1982_LOB 4 814944 16
|
SECURE_1982, SYS_IL0000059191C00002$$는 블록을 8개씩 할당 받아서 8 block * 8 kb(기본블록크기) => 64kb 이고
SECURE_1982_LOB는 블록을 16개 할당 받아서 16 block * 8 kb(기본블록크기) => 128kb가 된것임
테스트 row의 물리 위치 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select rowid,
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(rowid, USER, 'SECURE_1982') AS file#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block#,
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS row#
from secure_1982;
ROWID FILE# BLOCK# ROW#
------------------ ---------- ---------- ----------
AAAOc3AAEAADG9dAAA 4 814941 0
|
4번 파일에 814941번째 블록에 있음
해당 블록 덤프
|
1
2
3
4
5
6
7
8
9
|
SQL>
alter session set tracefile_identifier = 'secure_1982_block_dump';
Session altered.
예시) alter system dump datafile <file#> block <block#>;
alter system dump datafile 4 block 814941;
System altered.
|
덤프 경로 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select value
from v$diag_info
where name = 'Default Trace File';
VALUE
---------------------------------------------------------------------------------
/app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_496314_secure_1982_block_dump.trc
|
덤프 확인
|
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
|
$ vi /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_496314_secure_1982_block_dump.trc
...
data_block_dump,data header at 0xd4d66064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0xd4d66064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xfec
avsp=0xfd8
tosp=0xfd8
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xfec
block_row_dump:
tab 0, row 0, @0xfec
tl: 4012 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [3995]
00 70 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 00 2f 1c f1 0f 87 48 90 0f
81 01 00 0f 7c 01 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00
41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00
41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00
41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
...
|
덤프에 00 41 00 41...이 반복되고 있음(0x41(A) 값이 반복 되고 있음)
테스트2 결론 :
실제 CLOB 문자 데이터 1982자
=> 내부 저장 시 00 41 형태로 약 2byte씩 저장
=> 1982 * 2 = 3964 byte
LOB control information 약 31 byte
=> 3964 + 31 = 3995 byte
col 1: [3995]의 00 41이 나오기 전까지의 앞 31개(31byte)
00 70 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 00 2f 1c f1 0f 87 48 90 0f 81 01 00 0f 7c 01
SecureFile 방식으로 생성한 CLOB 컬럼에 1982byte 데이터를 삽입한 뒤 테이블 row 블록을 덤프해보면 row 전체 길이(tl)는 4012 byte이고 CLOB 컬럼에 해당하는 col 1의 길이는 3995 byte로 표시됨
또한 col 1 내부에는 00 41 값이 반복적으로 저장되어 있는데 이는 삽입한 'A' 문자열이 테이블 row 내부에 실제로 저장되어 있음을 의미함
따라서 SecureFile CLOB 역시 1982byte 데이터는 LOB 세그먼트가 아닌 테이블 row 내부에 저장된 것으로 확인됨
3. BASICFILE + 1983byte 데이터
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DROP TABLE basic_1983 PURGE;
CREATE TABLE basic_1983
(
id NUMBER,
c_data CLOB,
ins_date DATE DEFAULT SYSDATE
)
TABLESPACE USERS
LOB (c_data) STORE AS BASICFILE basic_1983_lob
(
TABLESPACE USERS
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
);
|
dba_lobs로 정보 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_name for a15
col column_name for a10
col segment_name for a20
col index_name for a25
col securefile for a5
SELECT table_name, column_name, segment_name, index_name, securefile, chunk, in_row, pctversion, retention, cache, logging
FROM dba_lobs
WHERE table_name = 'BASIC_1983';
TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME SECUR CHUNK IN_ PCTVERSION RETENTION CACHE LOGGING
--------------- ---------- -------------------- ------------------------- ----- ---------- --- ---------- ---------- ---------- -------
BASIC_1983 C_DATA BASIC_1983_LOB SYS_IL0000059200C00002$$ NO 8192 YES 10 NO YES
|
현재 SECUREFILE이 아니고 CHUNK 크기는 8192(8k)임, IN_ROW가 Yes임
ENABLE STORAGE IN ROW 상태에서 LOB 값과 control information을 합친 크기가 약 4000 bytes 이하이면 테이블 row 내부에 저장될 수 있음
샘플 데이터 삽입(문자열 1983byte 길이 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
INSERT INTO basic_1983 (id, c_data)
values (1, TO_CLOB(RPAD('A', 1983, 'A')));
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
|
1건 삽입됨
clob 컬럼 실제 길이 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
SELECT COUNT(*) AS row_cnt,
MAX(DBMS_LOB.GETLENGTH(c_data)) AS len,
MAX(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1))) AS lenb
FROM basic_1983;
ROW_CNT LEN LENB
---------- ---------- ----------
1 1983 1983
|
1983byte CLOB임
논리 용량 vs CHUNK 기준 예상 용량
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
SELECT COUNT(*) AS row_cnt,
ROUND(SUM(DBMS_LOB.GETLENGTH(c_data)) / 1024, 2) AS logical_kb,
ROUND(SUM(CEIL(DBMS_LOB.GETLENGTH(c_data) / 8192) * 8192)
/ 1024, 2) AS chunk_est_kb
FROM basic_1983;
ROW_CNT LOGICAL_KB CHUNK_EST_KB
---------- ---------- ------------
1 1.94 8
|
1개 row의 논리 용량은 1.94kb, CHUNK 기준 예상 용량은 8kb임
실제 세그먼트 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col segment_name for a30
WITH lob_info AS
(
SELECT segment_name,
index_name
FROM user_lobs
WHERE table_name = 'BASIC_1983'
)
SELECT s.segment_name,
s.segment_type,
ROUND(s.bytes / 1024, 2) AS kb
FROM dba_segments s
WHERE s.segment_name = 'BASIC_1983'
OR s.segment_name IN (SELECT segment_name FROM lob_info)
OR s.segment_name IN (SELECT index_name FROM lob_info)
ORDER BY s.bytes DESC;
SEGMENT_NAME SEGMENT_TYPE KB
------------------------------ ------------------ ----------
SYS_IL0000059200C00002$$ LOBINDEX 64
BASIC_1983 TABLE 64
BASIC_1983_LOB LOBSEGMENT 64
|
LOB 세그먼트와 일반 세그먼트가 둘다 64kb여서 어디에 저장되었는지 아직 알수 없음
익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
col segment_name for a25
SELECT segment_name,
file_id,
block_id,
blocks
FROM dba_extents
WHERE segment_name in('BASIC_1983','BASIC_1983_LOB','SYS_IL0000059200C00002$$')
ORDER BY extent_id;
SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS
------------------------- ---------- ---------- ----------
BASIC_1983 4 814968 8
SYS_IL0000059200C00002$$ 4 815032 8
BASIC_1983_LOB 4 815024 8
|
각각 블록을 8개씩 할당 받아서 8 block * 8 kb(기본블록크기) => 64kb가 된것임
테스트 row의 물리 위치 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select rowid,
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(rowid, USER, 'BASIC_1983') AS file#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block#,
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS row#
from basic_1983;
ROWID FILE# BLOCK# ROW#
------------------ ---------- ---------- ----------
AAAOdAAAEAADG99AAA 4 814973 0
|
4번 파일에 814973번째 블록에 있음
해당 블록 덤프
|
1
2
3
4
5
6
7
8
9
|
SQL>
alter session set tracefile_identifier = 'basic_1983_block_dump';
Session altered.
예시) alter system dump datafile <file#> block <block#>;
alter system dump datafile 4 block 814973;
System altered.
|
덤프 경로 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select value
from v$diag_info
where name = 'Default Trace File';
VALUE
---------------------------------------------------------------------------------
/app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_500143_basic_1983_block_dump.trc
|
덤프 확인
|
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
|
$ vi /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_500143_basic_1983_block_dump.trc
...
data_block_dump,data header at 0xd5b2a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0xd5b2a064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f61
avsp=0x1f4d
tosp=0x1f4d
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f61
block_row_dump:
tab 0, row 0, @0x1f61
tl: 55 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [40]
00 70 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 2f 1d 24 00 14 05 00 00
00 00 00 0f 7e 00 00 00 00 00 02 01 0c 6f b5
col 2: [ 7] 78 7e 07 06 0e 0a 06
end_of_block_dump
...
|
이전 테스트와 달리 col1에 40byte만 존재함(lob locator만 존재하는것)(col 1: [40] 부분)
col 1: [40]의 끝부분에 01 0c 6f b5값이 존재함
0x010c6fb5로 해석하면 file# 4, block# 815029를 가리키고 있음
해당 값의 데이터파일과 블록번호 확인
|
1
2
3
4
5
6
7
8
|
SQL>
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('010C6FB5', 'XXXXXXXX')) AS file_no,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('010C6FB5', 'XXXXXXXX')) AS block_no
FROM dual;
FILE_NO BLOCK_NO
---------- ----------
4 815029
|
해당 블록 덤프
|
1
2
3
4
5
6
7
8
9
|
SQL>
alter session set tracefile_identifier = 'lob_block_dump1';
Session altered.
예시) alter system dump datafile <file#> block <block#>;
alter system dump datafile 4 block 815029;
System altered.
|
덤프 경로 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select value
from v$diag_info
where name = 'Default Trace File';
VALUE
---------------------------------------------------------------------------------
/app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_509751_lob_block_dump1.trc
|
덤프 확인
|
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
|
$ vi /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_500143_basic_1982_block_dump2.trc
...
Start dump data blocks tsn: 4 file#:4 minblk 815029 maxblk 815029
Block dump from cache:
Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=17592245
Dump of buffer cache for pdb 0 tsn 4 rdba 0x10c6fb5 at level 2 done.
Block dump from disk:
buffer tsn: 4 rdba: 0x010c6fb5 (4/815029)
scn: 0x157a984 seq: 0x02 flg: 0x04 tail: 0xa9842802
frmt: 0x02 chkval: 0x0327 type: 0x28=PAGETABLE MANAGED LOB BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F7E241F6000 to 0x00007F7E241F8000
7F7E241F6000 0000A228 010C6FB5 0157A984 04020000 [(....o....W.....]
7F7E241F6010 00000327 0000E741 01000000 2F000000 ['...A........../]
7F7E241F6020 0000241D 00000001 00000000 00000000 [.$..............]
7F7E241F6030 010C6FB0 00000000 41004100 41004100 [.o.......A.A.A.A]
7F7E241F6040 41004100 41004100 41004100 41004100 [.A.A.A.A.A.A.A.A]
Repeat 246 times
7F7E241F6FB0 41004100 20004100 20002000 20002000 [.A.A.A. . . . . ]
7F7E241F6FC0 20002000 20002000 20002000 20002000 [. . . . . . . . ]
Repeat 258 times
7F7E241F7FF0 20002000 20002000 20002000 A9842802 [. . . . . . .(..]
Block scn: 0x157a984
Long field block dump:
Object Id 59201
LobId: 00010002F1D24 PageNo 0
Version: 0x0000.00000001 pdba: 17592240
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
...
End dump data blocks tsn: 4 file#: 4 minblk 815029 maxblk 815029
|
이 블록은 LOB 블록이고(type: 0x28=PAGETABLE MANAGED LOB BLOCK)
덤프에 00 41 00 41...이 반복되고 있음(0x41(A) 값이 반복 되고 있음)
테스트3 결론 :
BASIC_1983 테이블 row 블록
=> col 1: [40]
=> LOB locator만 저장
BASIC_1983_LOB LOB 블록
=> type: 0x28=PAGETABLE MANAGED LOB BLOCK
=> 00 41 00 41 ... 실제 CLOB 데이터 저장
테이블 row 블록만 확인했을 때는 CLOB 컬럼에 해당하는 col 1의 길이가 40byte로 표시되었고 실제 문자열 데이터는 보이지 않았음
이는 row 내부에는 실제 CLOB 데이터가 아니라 LOB locator 정보만 저장되었음을 의미함
이후 locator가 가리키는 LOB 블록을 덤프해보면 해당 블록은 type: 0x28=PAGETABLE MANAGED LOB BLOCK으로 표시됨
또한 Long field block dump 영역에서 00 41 값이 반복적으로 확인되는데 이는 삽입한 'A' 문자열 데이터가 LOB 블록에 실제로 저장되어 있음을 의미함
따라서 1983byte 길이의 CLOB 데이터는 테이블 row 내부가 아니라 LOB 세그먼트의 LOB 블록에 out-of-line 방식으로 저장된 것으로 확인됨
4. SECUREFILE + 1985byte 데이터
4번 테스트 전에
SECUREFILE + 1983byte 데이터를 넣었더니 row에 같이 저장되었음(col 1: [3997])
SECUREFILE + 1984byte 데이터를 넣었더니 row에 같이 저장되었음(col 1: [3999])
그래서 이 테스트에선 1985byte 데이터를 넣음
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DROP TABLE secure_1985 PURGE;
CREATE TABLE secure_1985
(
id NUMBER,
c_data CLOB,
ins_date DATE DEFAULT SYSDATE
)
TABLESPACE USERS
LOB (c_data) STORE AS SECUREFILE secure_1985_lob
(
TABLESPACE USERS
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
);
|
dba_lobs로 정보 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_name for a15
col column_name for a10
col segment_name for a20
col index_name for a25
col securefile for a5
SELECT table_name, column_name, segment_name, index_name, securefile, chunk, in_row, pctversion, retention, cache, logging
FROM dba_lobs
WHERE table_name = 'SECURE_1985';
TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME SECUR CHUNK IN_ PCTVERSION RETENTION CACHE LOGGING
--------------- ---------- -------------------- ------------------------- ----- ---------- --- ---------- ---------- ---------- -------
SECURE_1985 C_DATA SECURE_1985_LOB SYS_IL0000059218C00002$$ YES 8192 YES NO YES
|
현재 SECUREFILE이면서 CHUNK 크기는 8192(8k)임, IN_ROW가 Yes임
ENABLE STORAGE IN ROW 상태에서 LOB 값과 control information을 합친 크기가 약 4000 bytes 이하이면 테이블 row 내부에 저장될 수 있음
샘플 데이터 삽입(문자열 1985byte 길이 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
INSERT INTO secure_1985 (id, c_data)
values (1, TO_CLOB(RPAD('A', 1985, 'A')));
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
|
1건 삽입됨
clob 컬럼 실제 길이 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
SELECT COUNT(*) AS row_cnt,
MAX(DBMS_LOB.GETLENGTH(c_data)) AS len,
MAX(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1))) AS lenb
FROM secure_1985;
ROW_CNT LEN LENB
---------- ---------- ----------
1 1985 1985
|
1985byte CLOB임
논리 용량 vs CHUNK 기준 예상 용량
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
SELECT COUNT(*) AS row_cnt,
ROUND(SUM(DBMS_LOB.GETLENGTH(c_data)) / 1024, 2) AS logical_kb,
ROUND(SUM(CEIL(DBMS_LOB.GETLENGTH(c_data) / 8192) * 8192)
/ 1024, 2) AS chunk_est_kb
FROM secure_1985;
ROW_CNT LOGICAL_KB CHUNK_EST_KB
---------- ---------- ------------
1 1.94 8
|
1개 row의 논리 용량은 1.94kb, CHUNK 기준 예상 용량은 8kb임
실제 세그먼트 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col segment_name for a30
WITH lob_info AS
(
SELECT segment_name,
index_name
FROM user_lobs
WHERE table_name = 'SECURE_1985'
)
SELECT s.segment_name,
s.segment_type,
ROUND(s.bytes / 1024, 2) AS kb
FROM dba_segments s
WHERE s.segment_name = 'SECURE_1985'
OR s.segment_name IN (SELECT segment_name FROM lob_info)
OR s.segment_name IN (SELECT index_name FROM lob_info)
ORDER BY s.bytes DESC;
SEGMENT_NAME SEGMENT_TYPE KB
------------------------------ ------------------ ----------
SECURE_1985_LOB LOBSEGMENT 1216
SECURE_1985 TABLE 64
SYS_IL0000059218C00002$$ LOBINDEX 64
|
LOB 세그먼트는 1216kb를 할당받았고 일반 세그먼트는 64kb를 할당받음
익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
col segment_name for a25
SELECT segment_name,
file_id,
extent_id,
block_id,
blocks
FROM dba_extents
WHERE segment_name in('SECURE_1985','SECURE_1985_LOB','SYS_IL0000059218C00002$$')
ORDER BY extent_id;
SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS
------------------------- ---------- ---------- ---------- ----------
SECURE_1985 4 0 815072 8
SECURE_1985_LOB 4 0 815080 16
SYS_IL0000059218C00002$$ 4 0 815096 8
SECURE_1985_LOB 4 1 815328 8
SECURE_1985_LOB 4 2 820224 128
|
SECURE_1985, SYS_IL0000059218C00002$$는 블록을 8개씩 할당 받아서 8 block * 8 kb(기본블록크기) => 64kb 이고
SECURE_1985_LOB는 총 3개 익스텐트를 할당 받고 블록을 각각 16개, 8개, 128개 할당 받아서 (16 block + 8 block + 128 block) * 8 kb(기본블록크기) = 1216kb 인것임
SECUREFILE의 내부 extent 할당 및 관리 구조가 반영된 결과이기 때문에 모든 환경에서 1985byte 데이터 1건이 항상 약 1MB를 차지한다고 일반화하기보다는 테스트 환경에서 관찰된 할당 크기로 봐야함
테스트 row의 물리 위치 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select rowid,
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(rowid, USER, 'SECURE_1985') AS file#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block#,
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS row#
from secure_1985;
ROWID FILE# BLOCK# ROW#
------------------ ---------- ---------- ----------
AAAOdSAAEAADG/mAAA 4 815078 0
|
4번 파일에 815078번째 블록에 있음
해당 블록 덤프
|
1
2
3
4
5
6
7
8
9
|
SQL>
alter session set tracefile_identifier = 'secure_1985_block_dump';
Session altered.
예시) alter system dump datafile <file#> block <block#>;
alter system dump datafile 4 block 815078;
System altered.
|
덤프 경로 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select value
from v$diag_info
where name = 'Default Trace File';
VALUE
---------------------------------------------------------------------------------
/app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_718764_secure_1985_block_dump.trc
|
덤프 확인
|
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
|
$ vi /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_718764_secure_1985_block_dump.trc
...
data_block_dump,data header at 0xe562e064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0xe562e064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f63
avsp=0x1f4f
tosp=0x1f4f
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f63
block_row_dump:
tab 0, row 0, @0x1f63
tl: 53 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [38]
00 70 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 00 2f 1f 17 00 12 40 90 00
0c 21 00 0f 82 01 00 01 01 0c 6f f3 01
col 2: [ 7] 78 7e 07 06 12 30 27
end_of_block_dump
...
|
이전 테스트와 달리 col1에 38byte만 존재함(lob locator만 존재하는것)(col 1: [38] 부분)
col 1: [38]의 끝부분에 01 0c 6f f3값이 존재함(맨뒤 01은 제외)
0x010c6ff3로 해석하면 file# 4, block# 815091를 가리키고 있음
해당 값의 데이터파일과 블록번호 확인
|
1
2
3
4
5
6
7
8
|
SQL>
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('010c6ff3', 'XXXXXXXX')) AS file_no,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('010c6ff3', 'XXXXXXXX')) AS block_no
FROM dual;
FILE_NO BLOCK_NO
---------- ----------
4 815091
|
해당 블록 덤프
|
1
2
3
4
5
6
7
8
9
|
SQL>
alter session set tracefile_identifier = 'lob_block_dump2';
Session altered.
예시) alter system dump datafile <file#> block <block#>;
alter system dump datafile 4 block 815091;
System altered.
|
덤프 경로 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select value
from v$diag_info
where name = 'Default Trace File';
VALUE
---------------------------------------------------------------------------------
/app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_729387_lob_block_dump2.trc
|
덤프 확인
|
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
|
$ vi /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_729387_lob_block_dump2.trc
...
Block header dump: 0x010c6ff3
Object id on Block? Y
seg/obj: 0xe753 csc: 0x000000000157d891 itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.003.0000319c 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000
========
bdba [0x010c6ff3]
kdlich [0x7f2841bc904c 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x000000000157d891 [0x0000.8000.0157d891]
lid 000000010000002f1f17
spare 0x00000000
kdlidh [0x7f2841bc9064 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 3970
spr 0
data [0x7f2841bc9080 52 8060]
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41 00 41
...
End dump data blocks tsn: 4 file#: 4 minblk 815091 maxblk 815091
|
이 블록은 SecureFile LOB 블록이고 블록 헤더에서 typ: 5 - LOCAL LOBS로 표시됨
kdlich 영역의 flg0 값에서 typ=data로 표시되고, kdlidh 아래의 data 영역에서 00 41 값이 반복적으로 확인됨
이는 삽입한 'A' 문자열 데이터가 SecureFile LOB 블록에 실제로 저장되어 있음을 의미함
테스트4 결론 :
SECURE_1985 테이블 row 블록
=> col 1: [38]
=> row 내부에는 LOB locator만 저장
SECURE_1985_LOB LOB 블록
=> typ: 5 - LOCAL LOBS
=> kdlich의 typ=data 확인
=> data 영역에 00 41 00 41 ... 실제 CLOB 데이터 저장
테이블 row 블록만 확인했을 때는 CLOB 컬럼에 해당하는 col 1의 길이가 38byte로 표시되었고 실제 문자열 데이터는 보이지 않았음
이는 row 내부에는 실제 CLOB 데이터가 아니라 LOB locator 정보만 저장되었음을 의미함
이후 locator가 가리키는 LOB 블록을 덤프해보면 해당 블록은 typ: 5 - LOCAL LOBS로 표시됨
또한 kdlich 영역에서 typ=data가 확인되고, kdlidh 아래의 data 영역에서 00 41 값이 반복적으로 확인됨
이는 삽입한 'A' 문자열 데이터가 SecureFile LOB 블록에 실제로 저장되어 있음을 의미함
따라서 1985byte 길이의 CLOB 데이터는 테이블 row 내부가 아니라 LOB 세그먼트의 LOB 블록에 out-of-line 방식으로 저장된 것으로 확인됨
최종 결론 :
CLOB 컬럼에 ENABLE STORAGE IN ROW가 활성화되어 있더라도 모든 CLOB 데이터가 무조건 row 내부에 저장되는 것은 아님
이번 테스트에서는 BasicFile CLOB과 SecureFile CLOB에 각각 1982byte, 1983byte, 1985byte(SECUREFILE) 길이의 데이터를 삽입하고 블록 덤프를 통해 실제 저장 위치를 확인해봤음
1982byte 데이터를 삽입한 경우에는 BasicFile, SecureFile 모두 테이블 row 블록의 CLOB 컬럼 영역에서 실제 데이터가 확인됨
BasicFile은 col 1: [4000], SecureFile은 col 1: [3995]로 표시되었고 해당 영역 안에서 00 41 값이 반복적으로 확인됨
이는 삽입한 'A' 문자열이 LOB 세그먼트가 아닌 테이블 row 내부에 저장되었음을 의미함
반면 BASICFILE에 1983byte 데이터를 삽입한 경우에는 테이블 row 블록의 CLOB 컬럼 영역이 col 1: [40]으로 표시됨
이는 row 내부에 실제 CLOB 데이터가 저장된 것이 아니라 LOB locator만 저장되었음을 의미함
이후 locator가 가리키는 LOB 블록을 덤프해보면 type: 0x28=PAGETABLE MANAGED LOB BLOCK으로 표시되고 Long field block dump 영역에서 00 41 값이 반복적으로 확인됨
즉 실제 CLOB 데이터는 LOB 세그먼트의 LOB 블록에 저장된 것으로 확인됨
그리고 SECUREFILE에 1985byte 데이터를 삽입한 경우에는 테이블 row 블록의 CLOB 컬럼 영역이 col 1: [38]으로 표시됨
이는 row 내부에 실제 CLOB 데이터가 저장된 것이 아니라 LOB locator만 저장되었음을 의미함
이후 locator가 가리키는 LOB 블록을 덤프해보면 typ: 5 - LOCAL LOBS으로 표시되고 kdlidh 아래의 data 영역에서 00 41 값이 반복적으로 확인됨
즉 실제 CLOB 데이터는 LOB 세그먼트의 LOB 블록에 저장된 것으로 확인됨
그리고 SecureFile CLOB이 out-of-line으로 저장되면서 SECURE_1985_LOB 세그먼트가 총 1216KB까지 할당되었음
다만 이는 SecureFile의 내부 extent 할당 및 관리 구조가 반영된 결과이므로 모든 환경에서 1985byte 데이터 1건이 항상 약 1MB를 차지한다고 일반화하기보다는 테스트 환경에서 관찰된 할당 크기로 봐야함
19c 환경에서 ENABLE STORAGE IN ROW 상태의 CLOB은 LOB 값과 내부 control information을 합친 크기가 약 4000byte 이하이면 테이블 row 내부에 저장될 수 있음
하지만 이 기준을 초과하면 row 내부에는 LOB locator만 저장되고 실제 CLOB 데이터는 LOB 세그먼트에 out-of-line 방식으로 저장됨
이 기준이 BASICFILE은 1982byte이고, SECUREFILE은 1985byte임
참조 :
오라클 11gR2 CLOB 컬럼 STORAGE IN ROW 옵션별 용량 확인 테스트 ( https://positivemh.tistory.com/1418 )
How to Calculate Space Used by LOB Segments in the Database(KB114063)
CLOB Is Stored Out-of-line When Insert CLOB Data Greater Than 1982 Bytes(KB139645)
How to Compute the Size of a Table containing Outline CLOBs and BLOBs(KB149847)
How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?(KB164984)
CLOBs and NCLOBs character set storage in Oracle Release 8i, 9i, 10g and higher(KB772367)
https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/LOB-storage-with-applications.html#GUID-B82B3C24-1FAF-4661-96A0-28241FD2A052
https://docs.oracle.com/en/database/oracle/oracle-database/21/adlob/creating-new-LOB-column.html#GUID-6378560B-4305-496A-95A7-1FDFB6719FDC
https://hrjeong.tistory.com/294
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 11gR2 CLOB 컬럼 STORAGE IN ROW 옵션별 용량 확인 테스트 (0) | 2026.07.04 |
|---|---|
| 오라클 26ai 신기능 Select AI Agent 사용 테스트 (0) | 2026.06.27 |
| 오라클 19c 비파티션 테이블에서 파티션 테이블로 전환 및 성능 비교 (0) | 2026.06.25 |
| 오라클 19c Restart 환경 hostname 변경 가이드 (0) | 2026.06.21 |
| 오라클 19c Active Data Guard(ADG)와 Data Guard(DG) 차이점 및 DML Redirection 기능 (0) | 2026.06.16 |