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

What's your oracle release?

Try this:

Code:

---


col allocated for 999,999.999
col free      for 999,999.999
col used      for 999,999.999

select
    ( select sum(bytes)/1024/1024 from dba_data_files
      where tablespace_name like 'UND%' )  allocated,
    ( select sum(bytes)/1024/1024 from dba_free_space
      where tablespace_name like 'UND%')  free,
    ( select sum(bytes)/1024/1024 from dba_undo_extents
      where tablespace_name like 'UND%') USed
from dual
/

---

Hi Tamil,

Thanks for your reply.

By today I have checked the view DBA_FREE_SPACE, and it showing correct values


ADMIN@PROD>select
2 ( select sum(bytes)/1024/1024 from dba_data_files
3 where tablespace_name like 'UND%' ) allocated,
4 ( select sum(bytes)/1024/1024 from dba_free_space
5 where tablespace_name like 'UND%') free,
6 ( select sum(bytes)/1024/1024 from dba_undo_extents
7 where tablespace_name like 'UND%') USed
8 from dual;

ALLOCATED FREE USED
------------ ------------ ------------
46,500.000 37,289.500 9,210.125

But I have checked the same views,yesterday it show different values as above. Its their any time difference in updating the base tables of the view DBA_FREE_SPACE.

Oracle Version
=============
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

Hi,

could you please explain further the various status of the Undo

MB STATUS
---------- ---------
64 ACTIVE
4858.32031 EXPIRED
23716.0781 UNEXPIRED

In the case of delete, which is okay for deletion.
I have a challenge, my UNDOTBS usage suddenly went high and had about 1% space left which consistently has been of 95% free. I have tried to really track the very process utilising the entire space but none. When it was 1%, I had my RMAN backup running but when it was concluded, I could only see a difference of 5%.

What do you think could have gone wrong as there was no change in the application nor process