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');
select message,more_info from dba_advisor_findings where task_name='SecureFileDefragmentation1';
Or use below procedure
Find the reclaimable space from lob:
====================================
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;
-- inline procedure to print out numbers nicely formatted
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is a ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;
-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, else we use the FREE_BLOCKS
-- API for the user managed segments
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p( 'Free Blocks', l_free_blks );
end if;e
-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
exec show_space( 'SYS_LOB0000081488C00033$$','VRD_OWNER','LOB');
We can also use below procedure as per Doc ID 386341.1 which i have also copied in my belwo article
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/09/how-to-determine-actual-size-of-lob.html
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin
dbms_space.unused_space('<owner>','<lob segment name>','LOB',
TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('SEGMENT_NAME = <LOB SEGMENT NAME>');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('TOTAL_BYTES = '||TOTAL_BYTES);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
dbms_output.put_line('UNUSED BYTES = '||UNUSED_BYTES);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||LAST_USED_EXTENT_FILE_ID);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);
dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK);
end;
/
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
-- i preferred this way
ALTER TABLE TABLE_NAME MOVE LOB (LOB_COLUMN_NAME) STORE AS (tablespace ybs1 ) parallel 8 update indexes ;
Restrictions :
The following statement will error with ORA-00997: illegal use of LONG datatype :
ALTER TABLE foo MOVE LOB(lobcol) STORE AS lobsegment (TABLESPACE new_tbsp STORAGE (new_storage));
The ALTER TABLE ... MOVE command cannot be used to move a table containing a LONG or LONG RAW column.
Updating Tablespace attribute after Lob Movement:
If Lob or its partitions are moved to another tablespace
Doc ID 1388957.1
alter table <table name> move partition <table partition name> lob (<lob column name>) store as (tablespace <lob tablespace name>) parallel 8 ;
ALTER TABLE "table-name" MODIFY DEFAULT ATTRIBUTES FOR PARTITION "partition-name" TABLESPACE "new tablespace";
ALTER TABLE "table-name" MODIFY DEFAULT ATTRIBUTES TABLESPACE "new tablespace";
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)
How to Move LOB Data to Another Tablespace When the Table Also Contains a LONG Column (Doc ID 453186.1)
How To Reclaim Wasted Space on The Segment (Table, Index and LOB) and Tablespace Levels ( Doc ID 1682748.1 )
How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)? ( Doc ID 1394613.1 )
How To Move Partitioned Tables to Another Tablespace When They Contain LOBs ( Doc ID 1388957.1 ) *****
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_REDEFINITION.html#GUID-2BA796C4-8B4D-49B4-8A35-4C6F789CD374
