REM   List table and column information for LOBs for a specific user
REM   
REM   UNDO handling with LOBs is not designed for frequent updates
REM   Frequent updates are best handled with PCTVERSION at 100
REM   This means you must have a lot of space available in the LOB
REM     tablespace as all UNDO will be maintained over time.
REM  
REM   Using RETENTION does not work as expected
REM   It is set to UNDO_RETENTION at the time of the creation of the
REM   object.   It does not change over time as UNDO_RETENTION
REM   or auto-tuned undo retention changes.

set pages 999
set lines 110

spool lobdata.out

col column_name format a25 head "Column"
col table_name format a25 head "Table"
col tablespace_name format a25 head "Tablespace"
col pctversion format 999 head "PCTVersion %"
col segment_space_management format a30 head "Space|Mngmnt"
col retention format 999,999,999 head "Retention"

select l.table_name, l.column_name, l.tablespace_name, l.pctversion, l.retention, 
  t.segment_space_management
from dba_lobs l, dba_tablespaces t
where owner=upper('&USER')
and l.tablespace_name = t.tablespace_name
/

spool off