官方文档: Oracle Database Database SecureFiles and Large Objects Developer's Guide, 19c
环境:
CDB名称: kmg
PDB名称: PM
用户名: 也叫PM
db_*k_cache_size
-- 默认是0, 不能创建对应"块"大小的表空间
-- 更改对应"块"缓存
alter system set db_32k_cache_size=32M scope=both;
创建表空间
create tablespace tbs_32k_01
datafile '/u01/app/oracle/oradata/KMG/PM/tbs_32k_01.dbf'
size 50M
autoextend on
blocksize 32k
extent management local
uniform size 2M;
create tablespace tbs_32k_02
datafile '/u01/app/oracle/oradata/KMG/PM/tbs_32k_02.dbf'
size 50M
autoextend on
blocksize 32k
extent management local
uniform size 2M;
建表
普通表
-- 把LOB字段存储到另一个表空间里:
create table t_papers(
id number(3,0),
author varchar2(64),
title varchar2(128),
release_date date,
contnt clob)
tablespace users
lob(contnt) store as
(tablespace tbs_32k_01 chunk 32k storage(initial 2M next 2M) disable storage in row);
分区表
-- 分区存储, 但是将大字段存到另一个表空间里:
create table t_papers(
id number(3,0),
author varchar2(64),
title varchar2(128),
release_date date,
contnt clob)
lob(contnt) store as
(tablespace tbs_32k_01 chunk 32k storage(initial 2M next 2M) disable storage in row)
partition by range (id)
( partition pprs_range_p1 values less than (100) tablespace tbs_01,
partition pprs_range_p2 values less than (200) tablespace tbs_02,
partition pprs_range_p3 values less than (300) tablespace tbs_03,
partition pprs_range_other values less than (maxvalue) tablespace tbs_04);
进一步分区
注意! 这不是子分区!
-- 分区存储, 且大字段也分别存储到不同的表空间里, 并且指定详细的存储方式
-- 指定到TBS_32K_01里的数据是`不去重`, `不压缩`
-- 指定到TBS_32K_02里的数据是`去重`, `高压缩`
-- 注意: 这不是子分区!
create table t_papers(
id number(3,0),
author varchar2(64),
title varchar2(128),
release_date date,
contnt clob)
partition by range (id)
(
partition pprs_range_p1 values less than (500) tablespace tbs_01
lob(contnt) store as securefile (tablespace tbs_32k_01 chunk 32k storage(initial 2m next 2m) disable storage in row keep_duplicates nocompress),
partition pprs_range_other values less than (maxvalue) tablespace tbs_02
lob(contnt) store as securefile (tablespace tbs_32k_02 chunk 32k storage(initial 2m next 2m) disable storage in row deduplicate compress high)
);
验证
-- 生成数据: 共20条, 两个表空间分别10条。
-- clob字段长度: 10^6
TRUNCATE TABLE T_PAPERS;
DECLARE
V_TXT CLOB;
BEGIN
FOR I IN 490..499 LOOP
V_TXT:='';
FOR J IN 1..2500 LOOP
V_TXT:=V_TXT||DBMS_RANDOM.STRING('P',4000);
END LOOP;
INSERT INTO T_PAPERS(ID,CONTNT) VALUES(I,V_TXT);
INSERT INTO T_PAPERS(ID,CONTNT) VALUES(I+10,V_TXT);
END LOOP;
COMMIT;
END;
/
-- 查看
SELECT ID FROM T_PAPERS PARTITION(PPRS_RANGE_P1);
SELECT ID FROM T_PAPERS PARTITION(PPRS_RANGE_OTHER);
-- (SYS执行)查看clob字段占用大小
select dl.owner,
dl.table_name,
dl.column_name,
dl.tablespace_name table_tbs_name,
ds.tablespace_name column_tbs_name,
dl.chunk,
ds.segment_name,
ds.BYTES / 1024 / 1024 size_mb
from dba_lobs dl
join dba_segments ds
on (dl.segment_name = ds.segment_name)
where dl.owner = 'PM'
and dl.table_name = 'T_PAPERS'
order by size_mb desc;
-- 结果:
OWNER TABLE_NAME COLUMN_NAME TABLE_TBS_NAME COLUMN_TBS_NAME CHUNK SEGMENT_NAME SIZE_MB
----- ---------- ----------- -------------- --------------- ------ ------ ------------------ -------
PM T_PAPERS CONTNT USERS TBS_32K_01 32768 SYS_LOB0000074123C00005$$ 222
PM T_PAPERS CONTNT USERS TBS_32K_02 32768 SYS_LOB0000074123C00005$$ 120
-- 两个表空间(32k)里存的相同的数据, 但是占用的空间相差很大.