前言
本文对于lob字段删除插入的重用规则进行测试。
PCTVERSION指定用于维护LOB的旧版本占总体LOB存储空间的最大百分比。默认值为10，这意味着在使用了整个LOB存储空间的10%之前，不会覆盖旧版本的LOB数据。
该参数不建议设置过大，因为会额外占用空间。
RETENTION参数已经是默认配置，推荐配置，其受UNDO_RETENTION参数的影响，并且Securefiles lob只支持RETENTION。
关于lob更详细的解释参考文章：Oracle LOB——基本概念
确认PCTVERSION参数作用
初始化环境
创建包含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
确认RETENTION参数作用
初始化环境，修改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
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
