select a.*
, cast(a.used_gb*100.0 / a.max_gb as number(18,1)) as used_percent
from (
select tablespace_name
, cast(sum(bytes)/1024/1024/1024 as number(18,1)) as used_gb
, cast(sum(maxbytes)/1024/1024/1024 as number(18,1)) as max_gb
from dba_data_files
where tablespace_name like '%UNDO%'
group by tablespace_name
) a;
show parameter undo_retention;
alter system set undo_retention = 7200;
select * from dba_lobs
WHERE OWNER = USER
AND TABLE_NAME LIKE '%DOC'
ORDER BY TABLE_NAME;
ALTER TABLE EHR_HEALTHRECORD_DOC MODIFY LOB(DOCCONTENT)(retention);
select 'ALTER TABLE '|| table_name ||' MODIFY LOB('|| column_name ||')(retention);'
from dba_lobs
WHERE OWNER = USER
AND TABLE_NAME LIKE '%DOC'
ORDER BY TABLE_NAME
;