-
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
-
Undo tablespace
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|