DBA_FREE_SPACE - UNDO tablespace
Hi,
Free space are not getting reflecting in the view DBA_FREE_SPACE for the tablespace UNDO.
TOTAL SPACE ALLOCATED FOR UNDO TABLESPACE
=========================================
ADMIN@PROD>SELECT SUM(BYTES)/1024/1024 "MB" FROM DBA_DATA_FILES
2 WHERE TABLESPACE_NAME ='UNDODATA';
MB
----------
46500
Free space calculation based on DBA_FREE_SPACE view
====================================================
1 SELECT SUM(BYTES)/1024/1024 "MB" FROM DBA_FREE_SPACE
2* WHERE TABLESPACE_NAME ='UNDODATA'
ADMIN@PROD>/
MB
----------
.875
verifying the same on the view DBA_UNDO_EXTENTS
===============================================
ADMIN@PROD>SELECT SUM(BYTES)/1024/1024 "MB" FROM DBA_UNDO_EXTENTS;
MB
----------
28638.3984
1 row selected.
ADMIN@PROD>SELECT SUM(BYTES)/1024/1024 "MB" ,STATUS FROM DBA_UNDO_EXTENTS
2 GROUP BY STATUS;
MB STATUS
---------- ---------
64 ACTIVE
4858.32031 EXPIRED
23716.0781 UNEXPIRED
3 rows selected.
ADMIN@PROD>SHO PARAMETER UNDO
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 23244
undo_suppress_errors boolean FALSE
undo_tablespace string UNDODATA
QUESTION:-
=============
1. Based on DBA_UNDO_EXTENTS totalspace used on UNDO tablespace is 28638 MB, hence freespace avail
(totalspace - usedspace)
46500 - 28638 = 17862 MB ( Freespace in UNDO tablespace).
But when verifying the DBA_FREE_SPACE,the above value is not reflecting and its showing only .875 MB free.
Could you please give background of this.
Thanks,
Adhi