LOB 발표 (by nav012) [2013.11.16]
h1.LOB 기본
h3.A. 종류
h5.1. 저장 위치 별 분류
- Internal - CLOB/NCLOB/BLOB/SECUREFILE
- External - BFILES
h5.2. 유형 별 분류
- CLOB
XML이나 일반 문장과 같은 문자 정보를 대량 저장
DB 케릭터셋 변환 적용 - 검색 시 DB 케릭터셋 <-> Client 캐릭터셋 변환
- NCLOB
CLOB 과 유사하나 DB 의 네셔널 케릭터 셋으로 저장
- BLOB
이미지, 워드 문서 등의 정보를 대량 저장
케릭터셋 변환 적용되지 않음
- BFILES
운영체제 파일에 대한 포인터
파일의 내용에 대해 읽기 모드로만 접근 가능
데이터 저장 위해 Oracle Directory Object 사용
읽기 일관성 보장 되지 않음
세션 별 Open 할 수 있는 파일은 session_max_open_files 에 따름(Default 10) - Securefiles
기존 LOB 을 대체하기 위한 11G 이상 버전에서 지원되는 객체
중복제거/압축/암호화/저널링 등 지원
h3.B. 구조
- LOB Table 생성 시 Table Segment 와 무관하게 Index/Segment 생성
- 일반적으로 Table row 에 저장되는 것은 LOB Pointer
- LOB Segment 는 Chunk 단위로 저장
h5.1. 동작 방식
- 테이블의 LOB 은 실제로 LOB Index 만 지정
- LOB Index 는 LOB 조각 모두 지정
- Table LOB Locator 에서 포인터 참조 후 인덱스 경유하여 Random Access
h5.2. 읽기 일관성 구현
- 일관성 이미지를 LOB Segment 자체에 저장
- Transaction 이 LOB 을 변경하면 과거 데이터를 남겨두고 새 데이터 저장 위해 Chunk 할당
- 롤백 시 LOB Index Pointer 만 과거 이미지로 변경
- Undo Tablespace 의 이미지는 Lob Locator 와 Lob Index 를 위해서만 사용
h1.LOB 옵션
h3.A. 종류
h3.B. 상세
h5.1. STORE AS
... lob (COL_NAME) store as basicfile | securefile "SEG_NAME" (...);
- SECUREFILE 인지 BASICFILE (구형 LOB) 인지 결정
- Default: BASICFILE
h5.2. TABLESPACE
- 테이블 실제 저장 가능한 Tablespace 와 분리하여 관리 가능
일반 Tablespace 와 다른 Uniform extent 저장
백업/복구 및 효율적 공간 관리 - LOB Segment, LOB Index 는 분리 저장 불가(8i R3 이상부터)
h5.3. IN/OUT Row
... lob (COL_NAME) store as basicfile "SEG_NAME" (enable | disable storage in row);
- IN Row
LOB 데이터를 Table Row 에 저장
실제 읽기 일관성 및 Caching 등의 방식은 Table 속성에 따름 - OUT Row
LOB 데이터를 LOB Segment 에 저장
Table row 에는 20 byte 의 Locator 저장
Row 별 최소 사이즈는 1 block (1 Chunk 의 최소 사이즈)
- 4000 byte 이상의 데이터는?
IN/OUT Row 옵션과 관계 없이 LOB Segment 에 저장
In Row 에는 36byte 정도의 Control data 가 저장 --> 실제 입력 가능 사이즈는 3964byte
Multibyte 케릭터셋(ex UTF8) 은 3964 / 2 인 1982 byte까지만 저장 가능
- 관리 포인트
IN ROW 저장 시 별도 Access / Disk 탐색 과정 없으므로 성능 향상 가능하나
1 ROW 사이즈가 길어지면 Block chaining 이 발생 가능
- Default: ENABLE STORAGE IN ROW (생성 이후 변경 불가)
h5.4. CHUNK
... lob (COL_NAME) store as basicfile "SEG_NAME" (chunk 8192);
- Bytes 단위 명시 가능하나, DB Block Size 의 배수 중 Chunk 지정 사이즈 이상의 최소값으로 지정
Block size 8192 이고 Chunk 를 20000 으로 지정 했을 시 실제 Chunk 사이즈는 24576
Min 은 db_block_size / Max 는 32767
- 각 LOB 의 값은 실제 사이즈와 무관하게 최소 하나의 Chunk 사용 함
- Chunk Size 가 작을 때
LOB Segment 할당 단위 세분화로 공간 효율적 사용 가능
LOB Index 가 복잡해짐 - Chunk Size 가 Block Size 보다 클 때
Multiblock Read I/O 가능
- Default: db_block_size
h5.5. RETENTION / PCTVERSION
... lob (COL_NAME) store as basicfile "SEG_NAME" (retention | pctversion 50);
- LOB Segment 의 UNDO 이미지 관리
- 두 가지 방법 중 택 1 가능
RETENTION
일관성 읽기 이미지 관리 정책이 undo_retention 정책에 따르도록 함
PCTVISION
일관성 읽기를 위한 별도의 LOB Segment 공간을 전체 공간의 n% 까지 확장(를 위해 노력)
- 너무 작을 경우 ORA-01555 / ORA-22924 에러 발생 가능
22924, 00000, "snapshot too old"
// *Cause: The version of the LOB value needed for the consistent read was
// already overwritten by another writer.
// *Action: Use a larger version pool.
h5.5-2. Securefile Retention
... lob (COL_NAME) store as securefile "SEG_NAME" ( ... retention auto) ;
... lob (COL_NAME) store as securefile "SEG_NAME" ( ... retention min 900) ;
... lob (COL_NAME) store as securefile "SEG_NAME" ( ... retention none) ;
... lob (COL_NAME) store as securefile "SEG_NAME" ( ... retention max (storage maxsize 400M)) ;
- MAX / MIN / AUTO / NONE 4가지 타입
MAX: storage 옵션의 max 사이즈에 도달 할 때 까지 old 이미지를 유지
MIN: 최소 보존 기간을 undo_retention 처럼 지정할 수 있음
AUTO: DB 가 알아서 관리
NONE: retention 보존 기간이 없으므로 필요 시 마다 재 사용 가능함
h5.6. CACHE
... lob (COL_NAME) store as basicfile "SEG_NAME" (cache | nocache | cache reads);
- LOB Segment 에 I/O 발생 시 Buffer Cache 를 경유할지 결정
- CACHE READ 옵션은 Read 시에만 캐실
- 대기 이벤트
CACHE : db file sequential read/write, latch: cache buffer chains ..
NOCACHE: direct path read/write(lob)
Cache 설정 시 유의 - Table Block 과는 다르게 Buffer 의 most-recently-used end 에 캐싱
- CACHE_SIZE_THRESHOLD 에도 영향을 받지 않음(8i 이하 parameter)
--> 다른 버퍼 블록을 Age out 시킬 수 있으므로 사용에 주의 필요 - Redo 생성에도 영향
노캐시에서 블록은 direct path 모드로 I/O 되므로 전체 이미지(chunk) 가 redo 에 기록
캐시 모드에서는 블록 변경사항만 redo 에 기록
DISABLE STORAGE IN ROW + NOCACHE + CHUNK 32767 일 경우
LOB 실제 변경 사이즈에 무관하게 32KB 전체를 redo 에 기록 - Defaule: NOCACHE
h5.7. LOGGING | NOLOGGING
- 다들 알고 있는 그것
h1.LOB 성능 및 활용
h3.A.성능비교
h5.1. Update 시 성능비교
- In line Row --> In line Row
- In line Row --> Out line Row
- OUT line Row --> OUT line Row
- OUT line Row --> In line Row
SQL> create table lob_update_test (a clob);
-- Table segment, Lob segment Extent 확장
insert into lob_update_test select substr(a, 1, 1981) from lob_raw;
insert into lob_update_test select substr(a, 1, 1983) from lob_raw;
delete lob_update_test;
-- 테스트 데이터 삽입
insert into lob_update_test select substr(a, 1, 1981) from lob_raw;
-- 1981 -> 1982자: in row update
SQL> update lob_update_test set a=a||'a';
-- 1982 -> 1983자: in -> out row update
SQL> update lob_update_test set a=a||'a';
-- 1983 -> 1984자: out -> out row update
SQL> update lob_update_test set a=a||'a';
-- 1984 -> 1981자: out -> in row update
SQL> update lob_update_test set a=substr(a, 1, 1981); -- out --> in row update
-- 1981 -> 1982자: in row update
SQL> update lob_update_test set a=a||'a';
8192 rows updated.
Elapsed: 00:00:12.58
-- 1982 -> 1983자: in -> out row update
SQL> update lob_update_test set a=a||'a';
8192 rows updated.
Elapsed: 00:00:20.37
-- 1983 -> 1984자: out -> out row update
SQL> update lob_update_test set a=a||'a';
8192 rows updated.
Elapsed: 00:00:24.15
-- 1984 -> 1981자: out -> in row update
SQL> update lob_update_test set a=substr(a, 1, 1981);
8192 rows updated.
Elapsed: 00:00:13.21
- In -> Out / Out -> In Row 변경 시 차이는 없음
- 실제 기록하는 Segment 가 어디인지에 의해 더 큰 차이 발생
h3.B. 트레이스 분석
- LOB Segment 에 대한 Direct path read block 은 어디에?
- CR block 은 왜 증가할까?
h5.1. LOB Segment 에 대한 Direct path read block 은 어디에?
SQL> create table lob_direct_test (a clob) lob(a) store as basicfile (disable storage in row);
Table created.
SQL> insert into lob_direct_test select a from lob_raw_small;
3000 rows created.
-- Buffer 에 캐싱
SQL> select * from lob_direct_test;
...
A
--------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
...
3000 rows selected.
SQL> alter session set events '10046 trace name context forever, level 12' ;
Session altered.
SQL> select * from lob_direct_test;
...
A
--------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
...
3000 rows selected.
-- 10046 trace --> tkprof 변환
********************************************************************************
SQL ID: gu15dbd8v74zk Plan Hash: 4217871914
select *
from
lob_direct_test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 17 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3001 0.02 0.07 0 3005 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3003 0.02 0.07 0 3022 0 3000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
3000 3000 3000 TABLE ACCESS FULL LOB_DIRECT_TEST (cr=3005 pr=0 pw=0 time=12426 us cost=5 size=6006000 card=3000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3002 0.00 0.00
SQL*Net message from client 3002 0.74 0.84
********************************************************************************
- Default NOCACHE + DISABLE STORAGE IN ROW 에서 LOB 은 Direct path read/write 를 한다고 알려져 있으나 10046 Trace 에서는 Disk read 확인 불가
- Overall 통계 확인
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 17 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3001 0.02 0.07 0 3005 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3003 0.02 0.07 0 3022 0 3000
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6003 0.00 0.00
SQL*Net message from client 6003 15.44 16.43
Disk file operations I/O 2 0.00 0.00
direct path read 6000 0.00 0.05
log file sync 1 0.12 0.12
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 1 2 1
Fetch 1 0.00 0.00 0 16 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 17 2 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
2 user SQL statements in session.
1 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file: lob_direct_test.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
1 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
24195 lines in trace file.
1 elapsed seconds in trace file.
- direct path read 는 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS 에만 확인 가능
- 10046 trace 원본 확인
=====================
PARSING IN CURSOR #140502644297648 len=29 dep=0 uid=84 oct=3 lid=84 tim=1384572185091261 hv=1370723314 ad='783d7a60' sqlid='gu15dbd8v74zk'
select * from lob_direct_test
END OF STMT
PARSE #140502644297648:c=3000,e=3710,p=0,cr=17,cu=0,mis=1,r=0,dep=0,og=1,plh=4217871914,tim=1384572185091260
EXEC #140502644297648:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4217871914,tim=1384572185091308
WAIT #140502644297648: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384572185091360
WAIT #140502644297648: nam='SQL*Net message from client' ela= 130 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384572185091496
WAIT #140502644297648: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384572185091554
FETCH #140502644297648:c=0,e=52,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=4217871914,tim=1384572185091570
WAIT #140502644297648: nam='SQL*Net message from client' ela= 61 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384572185091647
WAIT #0: nam='Disk file operations I/O' ela= 52 FileOperation=2 fileno=5 filetype=2 obj#=79315 tim=1384572185091921
WAIT #0: nam='direct path read' ela= 19 file number=5 first dba=435500 block cnt=1 obj#=79315 tim=1384572185091970
WAIT #0: nam='direct path read' ela= 5 file number=5 first dba=435500 block cnt=1 obj#=79315 tim=1384572185092022
.
.
.
WAIT #0: nam='SQL*Net message from client' ela= 39 driver id=1650815232 #bytes=1 p3=0 obj#=79315 tim=1384572186011006
FETCH #140502644297648:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4217871914,tim=1384572186011028
STAT #140502644297648 id=1 cnt=3000 pid=0 pos=1 obj=79314 op='TABLE ACCESS FULL LOB_DIRECT_TEST (cr=3005 pr=0 pw=0 time=12426 us cost=5 size=6006000 card=3000)'
WAIT #140502644297648: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=79315 tim=1384572186011095
- SQL 에 대한 커서는 140502644297648 이나 (PARSING IN CURSOR #140502644297648)
- Direct path read 는 커서 0 이 대기 (WAIT #0: nam='direct path read' ela= 19 file number=5 first dba=435500 block cnt=1 )
- LOB Segment 에 대한 오퍼레이션은 Internal cursor 가 수행 한다고 함 참조(http://ukja.tistory.com/256)
h5.1. Query block 은 왜 증가?
SQL> select segment_name, blocks from dba_segments where segment_name in (
2 'LOB_DIRECT_TEST','SYS_LOB0000079317C00001$$','SYS_IL0000079317C00001$$'
3 );
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
SYS_LOB0000079317C00001$$ 3072
SYS_IL0000079317C00001$$ 32
LOB_DIRECT_TEST 16
- LOB Segment (Direct path read) 를 제외 한 block 의 총 합은 50 미만이나
- 이 전 trace 에서 3022 개 블록 query
-- Test Table 생성
SQL> create table lob_qb_test2 (id number, a clob) lob(a) store as QBSEG2 (disable storage in row);
SQL> create table lob_qb_test3 (
val1 varchar(4000),
val2 varchar(4000),
val3 varchar(4000),
val4 varchar(4000),
val5 varchar(4000),
val6 varchar(4000),
val7 varchar(4000),
val8 varchar(4000),
a clob
) lob(a) store as QBSEG3 (disable storage in row);
-- Test 데이터 입력
SQL> insert into lob_qb_test2 select rownum id, a from lob_raw_small;
SQL> insert into lob_qb_test3 select a, a, a, a, a, a, a, a, a from lob_raw_small;
SQL> commit;
- Size 확인
SEGMENT_NAME BLOCKS
------------------------------- ----------
LOB_QB_TEST2 16 -- Table2
QBSEG2 3072 -- Lob Segment2
SYS_IL0000079299C00002$$ 32 -- Lob Index2
LOB_QB_TEST3 6144 -- Table3
QBSEG3 3072 -- Lob Segment3
SYS_IL0000079305C00009$$ 32 -- Lob Index3
SQL> alter session set events '10046 trace name context forever, level 12' ;
SQL> select /*+ full(t) noparallel(t) */ id from lob_qb_test2 t;
SQL> select /*+ full(t) noparallel(t) */ id, a from lob_qb_test2 t;
SQL> select /*+ full(t) noparallel(t) */ * from lob_qb_test3 t;
- TKPROF trace 확인
********************************************************************************
SQL ID: ct1cryakrby3n Plan Hash: 3454962022
select /*+ full(t) noparallel(t) */ id
from
lob_qb_test2 t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 17 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 201 0.00 0.00 0 216 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 203 0.00 0.00 0 233 0 3000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
3000 3000 3000 TABLE ACCESS FULL LOB_QB_TEST2 (cr=216 pr=0 pw=0 time=1434 us cost=5 size=39000 card=3000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 201 0.00 0.00
SQL*Net message from client 201 2.60 2.63
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 17 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 201 0.00 0.00 0 216 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 203 0.00 0.00 0 233 0 3000
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 202 0.00 0.00
SQL*Net message from client 202 12.28 14.92
log file sync 1 0.03 0.03
********************************************************************************
SQL ID: 538uyahv081gc Plan Hash: 3454962022
select /*+ full(t) noparallel(t) */ id, a
from
lob_qb_test2 t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 14 17 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3001 0.02 0.07 0 3003 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3003 0.03 0.13 14 3020 0 3000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
3000 3000 3000 TABLE ACCESS FULL LOB_QB_TEST2 (cr=3003 pr=0 pw=0 time=14151 us cost=5 size=603000 card=3000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.04 0.04
SQL*Net message to client 3002 0.00 0.00
SQL*Net message from client 3002 1.98 2.08
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 14 17 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3001 0.02 0.07 0 3003 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3003 0.03 0.13 14 3020 0 3000
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6003 0.00 0.00
SQL*Net message from client 6003 23.53 25.78
Disk file operations I/O 1 0.00 0.00
db file sequential read 22 0.04 0.05
direct path read 6000 0.01 0.08
log file sync 1 0.04 0.04
********************************************************************************
- 답은?
h3.C. 기타
- col+1, round, ceil, floor: 문자--> 숫자 묵시적 형 변환 불가
SQL> select ceil(c)+1 from lob_test_f;
,ceil(c)+1
*
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected NUMBER got CLOB
- reverse
SQL> select reverse(a) from lob_test1;
select reverse(a) from lob_test1
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got CLOB
- vsize
SQL> select vsize(a) from lob_test2;
select vsize(a) from lob_test2
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
- initcap, translate : 캐릭터셋과 무관하게 4000글자까지만 지원
SQL> select initcap(a) from lob_test1;
select initcap(a) from lob_test1
*
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 12000, maximum: 4000)
SQL> select translate(a,'a','b') from lob_test1;
select translate(a,'a','b') from lob_test1
*
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 12000, maximum: 4000)