Lob Space usage

How the Oracle DBA can find the size of a LOB segment, the space that is actually allocated to the LOB data and the unused space within the LOB segment, above the HWM.

-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/09/14
-- # Name: LOBSpace.sql
-- ################################################
-- #
-- # Compatible: Oracle 10g, 11g
-- #
-- ################################################
-- #
-- # LOB info and deallocation of their space
-- # 
-- ################################################


-- The size in bytes of the LOB segment:
select bytes from dba_segments where segment_name ='xxxxx' and owner ='yyyyy';


-- The space that is  allocated to the LOB data object
select sum(dbms_lob.getlength ()) from .;



-- The unused space within the LOB segment and above the HWM
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('','','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 = ');
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;
/ 


SEGMENT_NAME = 
-----------------------------------
TOTAL_BLOCKS = 844416
TOTAL_BYTES = 6917455872
UNUSED_BLOCKS = 0   <---- here nothing to deallocate
UNUSED BYTES = 0
LAST_USED_EXTENT_FILE_ID = 13
LAST_USED_EXTENT_BLOCK_ID = 3221385
LAST_USED_BLOCK = 8192
PL/SQL procedure successfully completed.


-- To deallocate space
ALTER TABLE . enable row movement;
ALTER TABLE . DEALLOCATE UNUSED;
alter table . modify lob () (deallocate unused);







Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.