本文对于lob字段删除插入的重用规则进行测试。
pctversion指定用于维护lob的旧版本占总体lob存储空间的最大百分比。默认值为10，这意味着在使用了整个lob存储空间的10%之前，不会覆盖旧版本的lob数据。
该参数不建议设置过大，因为会额外占用空间。
retention参数已经是默认配置，推荐配置，其受undo_retention参数的影响，并且securefiles lob只支持retention。
关于lob更详细的解释参考文章：oracle lob——基本概念
初始化环境
创建包含blob字段表，pctversion为10，初始化区1m，每次扩展1m
conn dhw/dhw
create tablespace tts_name datafile '/u01/app/oracle/oradata/utf8db/db01.dbf' size 60m reuse autoextend off;
create table "dhw"."blob_test"
( "id" number,
"data" blob
) segment creation immediate
tablespace "tts_name"
lob ("data") store as lob_test_col ( tablespace "tts_name" disable storage in row chunk 8192 pctversion 10 storage(initial 1048576 next 1048576 ))
create or replace directory test as '/tmp';
[oracle@linux8 tmp]$ dd if=/dev/zero of=/tmp/test1m.dat bs=1m count=1
1 0 records in
1 0 records out
1048576 bytes (1.0 mb, 1.0 mib) copied, 0.000447859 s, 2.3 gb/s
[oracle@linux8 tmp]$ ls -l test1m.dat
-rw-r--r-- 1 oracle oinstall 1048576 jan 9 15:30 test1m.dat
[oracle@linux8 tmp]$
sql> select table_name,column_name,pctversion,retention,securefile,retention_type,retention_value from dba_lobs where table_name='blob_test';
table_name column_name pctversion retention sec retenti retention_value
------------------------------ ------------------------------ ---------- ---------- --- ------- ---------------
blob_test data 10 no no
sql> select * from dba_extents where tablespace_name='tts_name';
owner segment_name partition_ segment_type tablespace extent_id file_id block_id bytes blocks relative_fno
------ ------------------------------ ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
dhw lob_test_col lobsegment tts_name 0 5 256 1048576 128 5
dhw sys_il0000088014c00002$$ lobindex tts_name 0 5 136 65536 8 5
dhw blob_test table tts_name 0 5 128 65536 8 5
插入10行数据
插入10行（每行1m）数据后，lob字段数据占用10个1m的extent，extent 0为段头
declare
src_file bfile := bfilename('test', 'test1m.dat');
dst_file blob;
lgh_file binary_integer;
cur_id number(10);
begin
for i in 1 .. 10 ----->>> insert 1 to 10
loop
insert into blob_test
(id, data)
values
(lob_test_seq.nextval, empty_blob())
returning id into cur_id;
-- lock record
select data into dst_file from blob_test where id = cur_id for update;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
end loop;
end;
/
owner segment_name partition_ segment_type tablespace extent_id file_id block_id bytes blocks relative_fno
------ ------------------------------ ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
dhw lob_test_col lobsegment tts_name 0 5 256 1048576 128 5
dhw lob_test_col lobsegment tts_name 1 5 384 1048576 128 5
dhw lob_test_col lobsegment tts_name 2 5 512 1048576 128 5
dhw lob_test_col lobsegment tts_name 3 5 640 1048576 128 5
dhw lob_test_col lobsegment tts_name 4 5 768 1048576 128 5
dhw lob_test_col lobsegment tts_name 5 5 896 1048576 128 5
dhw lob_test_col lobsegment tts_name 6 5 1024 1048576 128 5
dhw lob_test_col lobsegment tts_name 7 5 1152 1048576 128 5
dhw lob_test_col lobsegment tts_name 8 5 1280 1048576 128 5
dhw lob_test_col lobsegment tts_name 9 5 1408 1048576 128 5
dhw lob_test_col lobsegment tts_name 10 5 1536 1048576 128 5
dhw sys_il0000088014c00002$$ lobindex tts_name 0 5 136 65536 8 5
dhw blob_test table tts_name 0 5 128 65536 8 5
13 rows selected.
select df.tablespace_name "tablespace",
totalusedspace "used mb",
(df.totalspace - tu.totalusedspace) "free mb",
df.totalspace "total mb",
round(100 * ((df.totalspace - tu.totalusedspace) / df.totalspace)) "pct. free"
from (select tablespace_name, round(sum(bytes) / 1048576) totalspace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes) / (1024 * 1024)) totalusedspace,
tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name and tu.tablespace_name='tts_name';
tablespace used mb free mb total mb pct. free
---------------------- ----------- ----------- ----------- ----------
tts_name 11 49 60 82
sql> select * from blob_test;
id data
---------- ------------------------------------------------------------------------------------------------------------------------
524 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
525 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
526 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
527 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
528 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
529 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
530 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
531 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
532 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
533 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
10 rows selected.
删除数据后再次插入
15:58:29 sql> delete from blob_test;
10 rows deleted.
15:59:29 sql> commit ;
commit complete.
先插入9行，空间占用不变
declare
src_file bfile := bfilename('test', 'test1m.dat');
dst_file blob;
lgh_file binary_integer;
cur_id number(10);
begin
for i in 1 .. 9 ----->>> insert 1 to 9
loop
insert into blob_test
(id, data)
values
(lob_test_seq.nextval, empty_blob())
returning id into cur_id;
-- lock record
select data into dst_file from blob_test where id = cur_id for update;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
end loop;
end;
/
tablespace used mb free mb total mb pct. free
---------------------- ----------- ----------- ----------- ----------
tts_name 11 49 60 82
再次插入1行，新分配了一个extent，因为pctversion为10，最后1行（10%）的数据旧版本被保留。
declare
src_file bfile := bfilename('test', 'test1m.dat');
dst_file blob;
lgh_file binary_integer;
cur_id number(10);
begin
for i in 1 .. 1 ----->>> insert 1 to 1
loop
insert into blob_test
(id, data)
values
(lob_test_seq.nextval, empty_blob())
returning id into cur_id;
-- lock record
select data into dst_file from blob_test where id = cur_id for update;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
end loop;
end;
/
tablespace used mb free mb total mb pct. free
---------------------- ----------- ----------- ----------- ----------
tts_name 12 48 60 80
owner segment_name partition_ segment_type tablespace extent_id file_id block_id bytes blocks relative_fno
------ ------------------------------ ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
dhw lob_test_col lobsegment tts_name 0 5 256 1048576 128 5
dhw lob_test_col lobsegment tts_name 1 5 384 1048576 128 5
dhw lob_test_col lobsegment tts_name 2 5 512 1048576 128 5
dhw lob_test_col lobsegment tts_name 3 5 640 1048576 128 5
dhw lob_test_col lobsegment tts_name 4 5 768 1048576 128 5
dhw lob_test_col lobsegment tts_name 5 5 896 1048576 128 5
dhw lob_test_col lobsegment tts_name 6 5 1024 1048576 128 5
dhw lob_test_col lobsegment tts_name 7 5 1152 1048576 128 5
dhw lob_test_col lobsegment tts_name 8 5 1280 1048576 128 5
dhw lob_test_col lobsegment tts_name 9 5 1408 1048576 128 5
dhw lob_test_col lobsegment tts_name 10 5 1536 1048576 128 5
dhw lob_test_col lobsegment tts_name 11 5 1664 1048576 128 5
dhw sys_il0000088014c00002$$ lobindex tts_name 0 5 136 65536 8 5
dhw blob_test table tts_name 0 5 128 65536 8 5
14 rows selected.
确认覆盖的数据
覆盖的数据是前9行，闪回查询报ora-01555，第10行数据闪回查询正常
16:06:12 sql> select * from blob_test as of timestamp to_timestamp('2023-01-09 15:58:27','yyyy-mm-dd hh24:mi:ss') ;
error:
ora-01555: snapshot too old: rollback segment number with name "" too small
ora-22924: snapshot too old
16:06:19 sql> select * from blob_test as of timestamp to_timestamp('2023-01-09 15:58:27','yyyy-mm-dd hh24:mi:ss') where id=533;
id data
---------- ------------------------------------------------------------------------------------------------------------------------
533 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
16:07:04 sql> select * from blob_test as of timestamp to_timestamp('2023-01-09 15:58:27','yyyy-mm-dd hh24:mi:ss') where id=532;
error:
ora-01555: snapshot too old: rollback segment number with name "" too small
ora-22924: snapshot too old
16:07:13 sql> select * from blob_test as of timestamp to_timestamp('2023-01-09 15:58:27','yyyy-mm-dd hh24:mi:ss') where id=524;
error:
ora-01555: snapshot too old: rollback segment number with name "" too small
ora-22924: snapshot too old
初始化环境，修改lob为retention
sql> alter table blob_test modify lob (data) (retention );
table altered.
sql> truncate table blob_test;
table truncated.
sql> alter database datafile '/u01/app/oracle/oradata/utf8db/db01.dbf' resize 15m;
sql> select table_name,column_name,pctversion,retention,securefile,retention_type,retention_value from dba_lobs where table_name='blob_test';
table_name column_name pctversion retention sec retenti retention_value
------------------------------ ------------------------------ ---------- ---------- --- ------- ---------------
blob_test data 900 no yes
sql> show parameter undo
name type value
------------------------------------ ----------- ------------------------------
undo_management string auto
undo_retention integer 900
undo_tablespace string undotbs1
插入10行数据
declare
src_file bfile := bfilename('test', 'test1m.dat');
dst_file blob;
lgh_file binary_integer;
cur_id number(10);
begin
for i in 1 .. 10 ----->>> insert 1 to 10
loop
insert into blob_test
(id, data)
values
(lob_test_seq.nextval, empty_blob())
returning id into cur_id;
-- lock record
select data into dst_file from blob_test where id = cur_id for update;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
end loop;
end;
/
tablespace used mb free mb total mb pct. free
---------------------- ----------- ----------- ----------- ----------
tts_name 11 4 15 27
17:08:19 sql> select * from blob_test;
id data
---------- ------------------------------------------------------------------------------------------------------------------------
608 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
609 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
610 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
611 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
612 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
613 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
614 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
615 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
616 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
617 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
10 rows selected
删除数据后再次插入
17:09:27 sql> delete from blob_test;
10 rows deleted.
17:09:27 sql> commit ;
在表空间数据文件自动扩展没开启的情况下，表空间剩余空间不足时，插入会覆盖
declare
src_file bfile := bfilename('test', 'test1m.dat');
dst_file blob;
lgh_file binary_integer;
cur_id number(10);
begin
for i in 1 .. 10 ----->>> insert 1 to 10
loop
insert into blob_test
(id, data)
values
(lob_test_seq.nextval, empty_blob())
returning id into cur_id;
-- lock record
select data into dst_file from blob_test where id = cur_id for update;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
end loop;
end;
/
tablespace used mb free mb total mb pct. free
---------------------- ----------- ----------- ----------- ----------
tts_name 13 2 15 13
在表空间数据文件自动扩展开启情况下，会自动扩容表空间，旧版本数据保留根据undo_retention配置
17:16:25 sql> alter database datafile '/u01/app/oracle/oradata/utf8db/db01.dbf' autoextend on ;
database altered.
17:16:32 sql> select * from blob_test;
id data
---------- ------------------------------------------------------------------------------------------------------------------------
618 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
619 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
620 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
621 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
622 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
623 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
624 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
625 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
626 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
627 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
10 rows selected.
17:16:56 sql> delete from blob_test;
10 rows deleted.
17:17:02 sql> commit ;
commit complete.
17:17:03 sql>
插入数据，旧版本数据都在
declare
src_file bfile := bfilename('test', 'test1m.dat');
dst_file blob;
lgh_file binary_integer;
cur_id number(10);
begin
for i in 1 .. 10 ----->>> insert 1 to 10
loop
insert into blob_test
(id, data)
values
(lob_test_seq.nextval, empty_blob())
returning id into cur_id;
-- lock record
select data into dst_file from blob_test where id = cur_id for update
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
end loop;
end;
/
tablespace used mb free mb total mb pct. free
---------------------- ----------- ----------- ----------- ----------
tts_name 23 2 25 8
闪回查询可以查到删除前全部数据
17:17:47 sql> select * from blob_test as of timestamp to_timestamp('2023-01-09 17:16:32','yyyy-mm-dd hh24:mi:ss') ;
id data
---------- ------------------------------------------------------------------------------------------------------------------------
618 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
619 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
620 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
621 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
622 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
623 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
624 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
625 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
626 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
627 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
10 rows selected.
17:19:59 sql> select * from blob_test;
id data
---------- ------------------------------------------------------------------------------------------------------------------------
628 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
629 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
630 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
631 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
632 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
633 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
634 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
635 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
636 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
637 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
10 rows selected.
修改undo保留期，测试释放lob旧版本，修改参数后 ，dba_lobs视图没有变化，但是已经生效，旧版本undo被覆盖，数据占用没有增长（生产环境谨慎修改undo_retention）
17:21:06 sql> alter system set undo_retention=30;
system altered.
17:21:33 sql> select table_name,column_name,pctversion,retention,securefile,retention_type,retention_value from dba_lobs where table_name='blob_test';
table_name column_name pctversion retention sec retenti retention_value
------------------------------ ------------------------------ ---------- ---------- --- ------- ---------------
blob_test data 900 no yes
17:22:17 sql> delete from blob_test;
10 rows deleted.
17:22:29 sql> commit ;
commit complete.
declare
src_file bfile := bfilename('test', 'test1m.dat');
dst_file blob;
lgh_file binary_integer;
cur_id number(10);
begin
for i in 1 .. 10 ----->>> insert 1 to 10
loop
insert into blob_test
(id, data)
values
(lob_test_seq.nextval, empty_blob())
returning id into cur_id;
-- lock record
select data into dst_file from blob_test where id = cur_id for update;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
end loop;
end;
/
tablespace used mb free mb total mb pct. free
---------------------- ----------- ----------- ----------- ----------
tts_name 23 2 25 8
最后修改时间：2023-01-12 09:42:59
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。