Most of time we are concerned with Growing space of Lob . Like reorg of Table we can also perform Reorg of Lob .
1453350.1 is master document that walks thro0ugh different approach of checking fragmentation and performing reorg in both scenario of Basic File and Secure file Lob .
Before Performing Reorg we need to check Free-able / Fragmented space
Note :
1) CLOBs are stored using a two-byte fixed width character set ([ed] if the session character is multi-byte variable length) ,
which means they may take much more space than you might be expecting, and (2) the getlength() function reports characters not bytes.
2) getlength() will report the decompressed length for every (logical) copy of a LOB value, so summing it across the table could be over-reporting quite dramatically.
3) pctversion is silently ignored by securefiles but used by basicfiles. On the other hand retention can be used for securefiles or basicfiles (assuming automatic undo management is enabled), but its usage with basicfiles doesn’t match its usage with securefiles. Moreover if you include both retention and pctversion in your table declaration Oracle raises error: ORA-32600: RETENTION and PCTVERSION cannot be used together for both basicfiles and securefiles (so Oracle is not quite ignoring pctversion for securefiles). (It may be worth mentioning that if you use export/import to upgrade your database you may find that this “spontaneously” changes a basicfile lob to a securefile lob.)
4) freepools is another parameter that is silently ignored for securefiles but can have a significant impact on the way that basicfiles reuse space from old copies of LOB values hence on the amount of allocated but unused space.
Lob RETENTION and Pctversion
In the olden days, we would use the PCTVERSION storage parameter for their LOB segments to reserve a percentage of storage space for read consistency of LOB segments.
Starting with Oracle 11g, you can now use the RETENTION parameter.
The RETENTION parameter will use the UNDO_RETENTION parameter for determining how long to keep LOB data for read-consistency purposes. But please be advised that the RETENTION parameter does not use the Undo tablespace, and the LOB segment in tablespace is used for read-consistency purposes.
When you change the UNDO_RETENTION parameter, the LOB segment is retention value is not modified. If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter. To change the LOB segment RETENTION value to match the new UNDO_RETENTION value, do the following:
ALTER TABLE my_table MODIFY LOB (lob_column) (PCTVERSION 20);
ALTER TABLE my_table MODIFY LOB (lob_column) (RETENTION);
By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used. You need to do this for all LOB segments that you intend to modify.
The RETENTION parameter is designed for use with Undo features of the database, such as Flashback Versions Query. When a LOB column has the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.
You Cannot Specify Both PCTVERSION and RETENTION for LOBs Creation.
Also You Cannot Specify RETENTION if the Database is Running in Manual Undo Mode.
PCTVERSION :
Specify the maximum percentage of overall LOB storage space to be used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.
RETENTION :
If the database is in automatic undo mode, then you can specify RETENTION instead of PCTVERSION to instruct Oracle to retain old versions of this LOB. This clause overrides any prior setting of PCTVERSION. NOTE: RETENTION will remain constant in DBA_LOBS even after subsequent changes to UNDO_RETENTION. However, LOB undo should take into affect changes to the parameter UNDO_RETENTION over time.
Checking Fragmented and Reclaimable space in Lob :
There are 3 approach we can use to check free-able space depending if its SecureFile or BasicFile Lob
1) Manual steps mentioned in doc 1453350.1 for basic file Lob
2) Using Segment Space Advisor: as mentioned below
2) Using dbms_space.space_usage for secure file Lob which is also documented in doc 1453350.1. eg is below
declare
segment_size_block NUMBER;
segment_size_byte NUMBER;
used_block NUMBER;
used_byte NUMBER;
expired_block NUMBER;
expired_byte NUMBER;
unexpired_block NUMBER;
unexpired_byte NUMBER;
begin
dbms_space.space_usage ('OWNER', '<lob_Segment name>', 'LOB', segment_size_block,
segment_size_byte, used_block, used_byte, expired_block, expired_byte,
unexpired_block, unexpired_byte, null);
dbms_output.put_line('segment_size_blocks = '||segment_size_block);
dbms_output.put_line('segment_size_bytes = '||segment_size_byte);
dbms_output.put_line('used_blocks = '||used_block);
dbms_output.put_line('used_bytes = '||used_byte);
dbms_output.put_line('expired_blocks = '||expired_block);
dbms_output.put_line('expired_bytes = '||expired_byte);
dbms_output.put_line('unexpired_blocks = '||unexpired_block);
dbms_output.put_line('unexpired_bytes = '||unexpired_byte);
end;
/
For Segment space advisor below is working example and options that can be used
Run the Segment Space Advisor:
DECLARE
seg_task_id number;
seg_task_name varchar2(100);
seg_task_desc varchar2(500);
BEGIN
seg_task_name := 'SecureFileDefragmentation1';
seg_task_desc := 'Manual Segment Advisor Run for table BLOGS';
dbms_advisor.create_task (
advisor_name := 'Segment Advisor',
task_id := seg_task_id,
task_name := seg_task_name,
task_desc := seg_task_desc);
END;
/
DECLARE
obj_id number;
BEGIN
dbms_advisor.create_object (
task_name := 'SecureFileDefragmentation1',
object_type := 'TABLE',
attr1 := 'JULIAN',
attr2 := 'BLOGS',
attr3 := NULL,
attr4 := NULL,
attr5 := NULL,
object_id := obj_id);
END;
/
BEGIN
dbms_advisor.set_task_parameter(
task_name := 'SecureFileDefragmentation1',
parameter := 'recommend_all',
value := 'TRUE');
END;
/
exec dbms_advisor.execute_task('SecureFileDefragmentation1');
SQL> select message,more_info from dba_advisor_findings where task_name='SecureFile
Performing Reorg of Lob :
3 approach can be used to Perform Defragmentation of Lob :
1) Shrink Command
2) Move command
3) export Import .
alter table <table name> move partition <table partition name>
lob (<lob column name>) store as <optional lob partition name> (tablespace <lob tablespace name>);
-or-
alter table <table name> move partition <table partition name>
lob (<lob column name>) store as (tablespace <lob tablespace name>);
Using Shrink option to reorg Of Lob :
Do not attempt to enable row movement for an index-organized table before specifying the shrink_clause. The ROWID of an index-organized table is its primary key, which never changes. Therefore, row movement is neither relevant nor valid for IOTs.
There are 2 important options/keywords with the shrink space syntax:
COMPACT: If you specify COMPACT, then Oracle only defragments the segment space and compacts the table rows for subsequent release. Meaning Oracle will recover space but will not amend the high water mark (HWM). So, Oracle does not release the space immediately.
CASCADE: If you specify CASCADE, then Oracle performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables. Meaning Oracle will recover space for the object and all dependent objects.
ALTER TABLE blogs ENABLE ROW MOVEMENT;
ALTER TABLE blogs SHRINK SPACE CASCADE Parallel 8;
ALTER TABLE blogs DISABLE ROW MOVEMENT;
-- Shrink a LOB segment (basicfile only until 21c).
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);
-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;
-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;
-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;
Using Move option to reorg Of Lob :
ALTER TABLE TABLE_NAME MOVE LOB (LOB_COLUMN_NAME) STORE AS (NOCOMPRESS);
or
ALTER TABLE TABLE_NAME MOVE LOB (LOB_COLUMN_NAME) STORE AS (tablespace ybs1 ) parallel 8 update indexes ;
Lob Options in Datapump :
impdp .. TRANSFORM=LOB_STORAGE:SECUREFILE|BASICFILE|DEFAULT|NO_CHANGE
DEFAULT – no lob storage clause is set for CREATE TABLE
NO_CHANGE – use settings from dump file
BASICFILE – creates LOBs as basicfile
SECUREFILE – creates LOBs as securefile
Can be set in PL/SQL using DBMS_DATAPUMP.METADATA_TRANSFORM
Compression for Lob :
A table compression doesn't affect the LOB compression. It means that if table is compressed, the LOB Segement will not be compressed automatically.
Compression level
MEDIUM and HIGH (Onwards 11gR1)
LOW (Onwards 11gR2)
create table tbl_lob_nocompress (id number,lob_data clob)
lob(lob_data) store as securefile (tablespace example nocompress)
create table tbl_lob_compress (id number,lob_data clob)
lob(lob_data) store as securefile (tablespace example compress
alter table tbl_lob_compress modify lob(lob_data)(compress high);
set linesize 150
col column_name format a10
select table_name,column_name,segment_name,securefile from user_lobs where table_name like 'TBL%';
show parameter db_secure
select securefile from user_lobs where table_name='TBL_LOB_TEST';
Views :
/* */
set lines 132 pages 50
col owner format a15 heading 'Owner'
col segment_name format a27 heading 'Segment|Name'
col tablespace_name format a15 heading 'Tablespace'
column extents format 9,999 heading 'Extents'
column bytes format 99,999 heading 'Meg'
col segment_type format a10 heading 'Segment|Type'
col column_name format a15 heading 'Column|Name'
col segment_name format a26 heading 'Segment|Name'
col index_name format a26 heading 'Index|Name'
col owner format a15 heading 'Owner'
col table_name format a22 heading 'Table|Name'
col in_row format a3 heading 'In|Row'
start title132 'Database Lob Column Data'
spool rep_out\&db\lob_seg
select owner,segment_name,segment_type,tablespace_name,extents,bytes/(1024*1024) bytes from dba_segments where owner not in ('SYS','SYSTEM','PRECISE','MAULT','PATROL','QDBA','OUTLN','XDB','WMSYS','MDSYS','CTXSYS','ODM','SYSMAN') and segment_type like 'LOB%'
/
/* */
select owner,table_name,column_name,segment_name,index_name,in_row from dba_lobs
where owner not in ('SYS','SYSTEM','PRECISE','MAULT','PATROL','QDBA','OUTLN','XDB','WMSYS','MDSYS','CTXSYS','ODM','SYSMAN')
/
spool off
ttitle off
Sample commands for Lob creation is placed below .
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/02/lob-sample-codes.html
Below script can be used to check used and free space in all SecureFile Lob
with function f_lob_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as
segment_size_blocks number; segment_size_bytes number;
used_blocks number; used_bytes number;
expired_blocks number; expired_bytes number;
unexpired_blocks number; unexpired_bytes number;
begin
dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,
segment_size_blocks, segment_size_bytes,
used_blocks, used_bytes,
expired_blocks, expired_bytes,
unexpired_blocks, unexpired_bytes,
p_partition_name);
return 'segment_size_blocks:'|| segment_size_blocks || ' used_blocks:' || used_blocks || ' expired_blocks:' ||expired_blocks ||' unexpired_blocks:' || unexpired_blocks;
end;
get_info as (
select f_lob_space_info(owner, segment_name,
decode(segment_type, 'LOBSEGMENT', 'LOB', segment_type),
partition_name) as lob_info,
segment_type, owner, segment_name, partition_name, bytes
from dba_segments
where segment_subtype='SECUREFILE'
and segment_type in ('LOBSEGMENT', 'LOB PARTITION')
),
parse_info as (
select to_number(regexp_substr(lob_info, 'segment_size_blocks:([0-9]+)',1,1,'i',1)) * t.block_size /1024/1024 as segment_size_mb,
to_number(regexp_substr(lob_info, 'used_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024 as used_size_mb,
to_number(regexp_substr(lob_info, 'expired_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024 as expired_size_mb,
g.owner, l.table_name, l.column_name, g.segment_name lob_name
from get_info g
join dba_lobs l on g.owner = l.owner and g.segment_name = l.segment_name
join dba_tablespaces t on t.tablespace_name = l.tablespace_name
)
select segment_size_mb - used_size_mb empty_size, parse_info.*
from parse_info
order by empty_size desc
/
with function f_lob_free_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as
segment_size_blocks number; segment_size_bytes number;
used_blocks number; used_bytes number;
expired_blocks number; expired_bytes number;
unexpired_blocks number; unexpired_bytes number;
begin
dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,
segment_size_blocks, segment_size_bytes,
used_blocks, used_bytes,
expired_blocks, expired_bytes,
unexpired_blocks, unexpired_bytes,
p_partition_name);
return segment_size_bytes - used_bytes - unexpired_bytes;
end;
get_info as (
select f_lob_free_space_info(s.owner, s.segment_name,
decode(s.segment_type, 'LOBSEGMENT', 'LOB', s.segment_type),
s.partition_name)/1024/1024 as potential_empty_space_in_mb,
s.segment_type, s.owner,
l.table_name, l.column_name, s.segment_name lob_name, s.partition_name
from dba_segments s
join dba_lobs l on s.owner = l.owner and s.segment_name = l.segment_name
where s.segment_subtype='SECUREFILE'
and s.segment_type in ('LOBSEGMENT', 'LOB PARTITION')
)
select *
from get_info
order by potential_empty_space_in_mb desc
/
Reference :
Tempory LOB segment in TEMP tablespace keep increaseing when customer uses CONNECTION POOL (Doc ID 2297060.1)
LOB Partition Segment Uses Excessive Space (Doc ID 2720886.1)
How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (Doc ID 1453350.1)
How To Move Or Rebuild A Lob Partition (Doc ID 761388.1)
LOB segment size is significantly increasing despite of small actual size of data in it (Doc ID 2326423.1)
Why is no space released after an ALTER TABLE ... SHRINK? (Doc ID 820043.1)
How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Doc ID 1451124.1)
How LOB columns can be compressed and storage savings can be gained by using Oracle 11g
Advanced Compression features.11g Advanced Compression - How to Check Space Occupied by LOB Compression (Doc ID 861344.1)
How To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary (Doc ID 422826.1)
