Hoy vamos a otra de estas entradas para dummies que recopilan SQL utles, en este caso para tratar con los lobs.
Las variables de formateo del sqlplus para estas consultas serian

set linesize 180 pagesize 900
column SEGMENT_NAME format a40;
column TABLE_NAME format a60;
column TABLESPACE_NAME format a30;
column owner format a20;

Lista de los lobs mas grandes y lo que ocupan para el esquema

select  e.owner,l.tablespace_name,
l.table_name,
l.segment_name,sum(e.bytes/(1024*1024*1024)) Gb
from dba_extents e,dba_lobs  l
where
	e.owner = l.owner
	and 	e.segment_name = l.segment_name
	and 	e.OWNER='ESQUEMA'
	and 	e.segment_type = 'LOBSEGMENT'
     group by  
     e.owner,l.tablespace_name,
    l.table_name,
     l.segment_name 
     order by Gb desc ;

Obtener los datos (esquema,tabla y columna) de un LOB determinado

select OWNER,TABLE_NAME,COLUMN_NAME from dba_lobs
 where SEGMENT_NAME='SYS_LOBXXXXXX$$';

Bytes ocupados por un LOB

select sum(dbms_lob.getlength (COLUMNA))/1024/1024 Mb ESQUEMA.TABLA;
o bien 

select bytes/1024/1024 Mb

from dba_segments where segment_name ='SYS_LOBXXXXXX$$' ;
o esta mas completa 
set serveroutput on
declare
     l_segment_size_blocks NUMBER;
     l_segment_size_bytes NUMBER;
     l_used_blocks NUMBER;
     l_used_bytes NUMBER;
     l_expired_blocks NUMBER;
     l_expired_bytes NUMBER;
     l_unexpired_blocks NUMBER;
     l_unexpired_bytes NUMBER;
     l_unused_blocks NUMBER;
     l_unused_bytes NUMBER;
     l_non_data_blocks NUMBER;
     l_non_data_bytes NUMBER;
 BEGIN
	DBMS_SPACE.SPACE_USAGE(
   	  segment_owner =>'ESQUEMA',
	  segment_name => 'SYS_LOB0000227238C00034$$',
	  segment_type => 'LOB',
	  segment_size_blocks => l_segment_size_blocks,
	  segment_size_bytes => l_segment_size_bytes,
	  used_blocks => l_used_blocks,
	  used_bytes => l_used_bytes,
	  expired_blocks => l_expired_blocks,
	  expired_bytes => l_expired_bytes,
	  unexpired_blocks => l_unexpired_blocks,
	  unexpired_bytes => l_unexpired_bytes
           );
      l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
      l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);
	  l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
	  l_non_data_bytes :=  l_unused_bytes + l_expired_bytes + l_unexpired_bytes;
	  DBMS_OUTPUT.ENABLE;
	   DBMS_OUTPUT.PUT_LINE(' Segment Blocks/Bytes   = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
	  DBMS_OUTPUT.PUT_LINE(' Unused Blocks/Bytes    = '||l_unused_blocks||' / '||l_unused_bytes);
	  DBMS_OUTPUT.PUT_LINE(' Used Blocks/Bytes      = '||l_used_blocks||' / '||l_used_bytes);
	  DBMS_OUTPUT.PUT_LINE(' Expired Blocks/Bytes   = '||l_expired_blocks||' / '||l_expired_bytes);
	  DBMS_OUTPUT.PUT_LINE(' Unexpired Blocks/Bytes = '||l_unexpired_blocks||' / '||l_unexpired_bytes);
	  DBMS_OUTPUT.PUT_LINE('===========================================================================');
	  DBMS_OUTPUT.PUT_LINE(' NON Data Blocks/Bytes  = '||l_non_data_blocks||' / '||l_non_data_bytes);
 END;
 /

 Mover una tabla con  Lobs  ( para hacer shrink) 
Este metodo implica bloqueo durante el traslado 
ALTER TABLE ESQUEMA.TABLA MOVETABLESPACE NUEVOTABLESPACE;
Y para cada uno de los lobs de la tabla 

ALTER TABLE ESQUEMA.TABLA MOVE LOB(COLUMNA) STORE AS SECUREFILE (TABLESPACE NUEVOTABLESPACE);