DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DBA_FREE_SPACE - UNDO tablespace

  1. #1
    Join Date
    Sep 2006
    Posts
    5

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
    /

  3. #3
    Join Date
    Sep 2006
    Posts
    5
    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

  4. #4
    Join Date
    Apr 2010
    Posts
    1

    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
  •  


Click Here to Expand Forum to Full Width