Oracle Database Lob Maintenance -- Checking Lob Fragmentation and Performing reorg of Lob

 

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 

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

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)