Hi All,
Oracle 19c
I got the following error. UNDOTBS1 is full now. Although I restart the db it still remains. Why Oracle doesnt release the space? What do you recommend?
ora-30036 unable to extend segment by 8 in undo tablespace 'undotbs1'
-------------------------------------------------------------------------------------------------------
undo_management string AUTO
undo_retention integer 900
-------------------------------------------------------------------------------------------------------
SELECT object_name, suggested_action FROM dba_alert_history where trunc(creation_time) = trunc(sysdate);
UNDOTBS1 please contact support for recovery assistance
-------------------------------------------------------------------------------------------------------
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
UNEXPIRED 47220523008 5436
EXPIRED 32309248 80
ACTIVE 53650522112 1591
-------------------------------------------------------------------------------------------------------
select
b.tablespace_name, tbs_size SizeGb, a.free_space FreeGb
from (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name and a.tablespace_name = 'UNDOTBS1';
UNDOTBS1 93,9999847412109 0,01
-------------------------------------------------------------------------------------------------------
Thanks in advance