1 2 Previous Next 22 Replies Latest reply: Dec 27, 2013 11:29 AM by jgarry RSS

Tablespace Freespace

petra-K Expert
Currently Being Moderated

Hi All,

 

11.2.0.1

 

I got this script that produces "database freespace" report. I found this at google and it is very cool as it helps me identify when tablespace is running out of space or in "critical" condition.

Obvoiusly this is for 9i since it does not include TEMP. My problem is it does not include the TEMP tablespace if is currently running out of space on its current TS allocation. How do I include TEMP in this script?

 

Thanks,

pK

 

==========================

set pages 0

set lines 120

set feedback off

set term off

set echo off

set verify off

col total for 999999999999999

col used for 999999999999999

col free for 999999999999999

spool freespc.lst

select  sysdate,tbs.tablespace_name,

            tot.bytes total,

            tot.bytes-sum(nvl(fre.bytes,0)) used,

            sum(nvl(fre.bytes,0)) free,

            round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) pct,

            decode(

                greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),

                90, '', '*'

            ) pct_warn

from    dba_free_space fre,

           (select tablespace_name, sum(bytes) bytes

           from    dba_data_files

           group by tablespace_name) tot,

           dba_tablespaces tbs

where   tot.tablespace_name    = tbs.tablespace_name

and     fre.tablespace_name(+) = tbs.tablespace_name

group by tbs.tablespace_name, tot.bytes, tot.bytes

order by 6, 2  ;

spool off

==================

 

OUTPUT

=======

TABLESPACETOTAL BYTESUSEDFREE% USEDCRITICAL
INDX26,214,4001,048,57625,165,8244
XDB20,971,5201,048,57619,922,9445
UNDOTBS1393,216,00042,205,184351,010,81610.73
TOOLS10,485,7601,245,1849,240,57611.88
DRSYS20,971,5202,752,51218,219,00813.13
ODM20,971,5207,340,03213,631,48835
SYSAUX1,080,033,280923,598,848156,434,43285.52
EXAMPLE151,388,160143,851,5207,536,64095.02*
USERS83,886,08079,757,3124,128,76895.08*
SYSTEM849,346,560842,006,5287,340,03299.14*
  • 1. Re: Tablespace Freespace
    Girish Sharma Guru
    Currently Being Moderated

    Because free space in temp tablespace does not matter.  If you wish for temp tablespace then :

     

    add following for temp files -

     

    select sum(bytes)/1024 kbytes_alloc, tablespace_name

    from sys.dba_temp_files

    group by tablespace_name

     


    the new query will be

     


    select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb

    from

    (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space

    from dba_free_space group by tablespace_name) a,

    (select tablespace_name, sum(bytes)/1024/1024 as tbs_size

    from dba_data_files group by tablespace_name

    UNION

    select tablespace_name, sum(bytes)/1024/1024 tbs_size

    from dba_temp_files

    group by tablespace_name ) b

    where a.tablespace_name(+)=b.tablespace_name;


    Source:https://community.oracle.com/thread/507636


    Regards

    Girish Sharma

  • 2. Re: Tablespace Freespace
    Anar Godjaev Expert
    Currently Being Moderated

    Hi,

     

    Check script:  https://community.oracle.com/thread/1667796?start=0&tstart=0

     

    SELECT tsu.tablespace_name,
           Ceil(tsu.used_mb)                            "size MB",
           Decode(Ceil(tsf.free_mb), NULL, 0,
                                     Ceil(tsf.free_mb)) "free MB",
           Decode(100 - Ceil(tsf.free_mb / tsu.used_mb * 100), NULL, 100,
                                                               100 - Ceil(
           tsf.free_mb / tsu.used_mb * 100))            "% used"
    FROM   (SELECT tablespace_name,
                   SUM(bytes) / 1024 / 1024 used_mb
            FROM   dba_data_files
            GROUP  BY tablespace_name
            UNION ALL
            SELECT tablespace_name
                   || '  **TEMP**',
                   SUM(bytes) / 1024 / 1024 used_mb
            FROM   dba_temp_files
            GROUP  BY tablespace_name) tsu,
           (SELECT tablespace_name,
                   SUM(bytes) / 1024 / 1024 free_mb
            FROM   dba_free_space
            GROUP  BY tablespace_name
            UNION ALL
            SELECT tablespace_name,
                   tfs.allocated_space / 1024 / 1024 free_mb
            FROM   dba_temp_free_space tfs) tsf
    WHERE  tsu.tablespace_name = tsf.tablespace_name(+)
    ORDER  BY 4

    Thank you

  • 3. Re: Tablespace Freespace
    Karan Kukreja Journeyer
    Currently Being Moderated

    Hi,

     

    You should not be worried too much about temp . Temp management is done automatically by Oracle. Are you getting the error :

     

    could not extend xxxx bytes of xyz object in tablespace temp ?

     

    regards

  • 4. Re: Tablespace Freespace
    petra-K Expert
    Currently Being Moderated

    Thanks all,

     

     

    "You should not be worried too much about temp . Temp management is done automatically by Oracle.

     

    How can I not worry? even if it is manage auto  but if you do not have free physical disk space, you will still get error, right?   I believe it need to be monitored right?

  • 5. Re: Tablespace Freespace
    Girish Sharma Guru
    Currently Being Moderated

    >How can I not worry? even if it is manage auto  but if you do not have free physical disk space, you will still get error, right?   I believe it need to be monitored right?

     

    Space Allocation in a Temporary Tablespace

    You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies the current sort users in those segments.

    When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just marked as free and available for reuse. The DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace. See "Viewing Space Usage for Temporary Tablespaces" for more information. You can manually shrink a locally managed temporary tablespace that has a large amount of unused space. See "Shrinking a Locally Managed Temporary Tablespace" for details.

     

    Managing Tablespaces

     

    So, you do not need to worry for free space in temp tablespace, never.

     

    Regards

    Girish Sharma

  • 6. Re: Tablespace Freespace
    petra-K Expert
    Currently Being Moderated

    HI Girish, Anar, Karan,

     

    I run the new query and I got this output below.

    Shall I assume that TEMP will always be 100%  used and 0 free? So I will not check the logical side but only the physical?

     

    Thanks,

    pK

     

     

    TABLESPACE_NAME

    size MB

    free MB

    % used

    UNDOTBS1

    375

    361

    3

    INDX

    25

    24

    4

    XDB

    20

    19

    5

    TOOLS

    10

    9

    11

    DRSYS

    20

    18

    13

    ODM

    20

    13

    35

    SYSAUX

    1030

    147

    85

    USERS

    80

    4

    95

    EXAMPLE

    145

    8

    95

    SYSTEM

    810

    7

    99

    TEMP **TEMP**

    63

    0

    100

  • 7. Re: Tablespace Freespace
    petra-K Expert
    Currently Being Moderated

    Hi Girish,

     

    "So, you do not need to worry for free space in temp tablespace, never.



    Is this true even if the physical disk where the TEMP tablespace resides in d:\ or /u01 is 100% full?

  • 8. Re: Tablespace Freespace
    Karan Kukreja Journeyer
    Currently Being Moderated

    As you would have read the post by Mr. Girish above , it clearly mentions that post sort operation , nothing is wiped off but marked as free and available for reuse.

     

    That's the reason its 100% full , well almost everytime.

     

     

    Regards

    Karan

  • 9. Re: Tablespace Freespace
    Girish Sharma Guru
    Currently Being Moderated

    >Shall I assume that TEMP will always be 100%  used and 0 free?

    Yes

    >So I will not check the logical side but only the physical?

    No. 

    You use the SHRINK SPACE clause of the ALTER TABLESPACE statement to shrink a temporary tablespace, or the SHRINK TEMPFILE clause of the ALTER TABLESPACE statement to shrink a specific temp file of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp file. The optional KEEP clause defines a minimum size for the tablespace or temp file.

     

    Managing Tablespaces

     

    Regards

    Girish Sharma

  • 10. Re: Tablespace Freespace
    Anar Godjaev Expert
    Currently Being Moderated

     

     

    TABLESPACE_NAME

    size MB

    free MB

    % used

    UNDOTBS1

    375

    361

    3

    INDX

    25

    24

    4

    XDB

    20

    19

    5

    TOOLS

    10

    9

    11

    DRSYS

    20

    18

    13

    ODM

    20

    13

    35

    SYSAUX

    1030

    147

    85

    USERS

    80

    4

    95

    EXAMPLE

    145

    8

    95

    SYSTEM

    810

    7

    99

    TEMP **TEMP**

    63

    0

    100

     

    Well, I don't see any problem there. Your temporary tablespace simply isn't required. This could be possible if you have enough memory to carry out the sort operation.

  • 11. Re: Tablespace Freespace
    jgarry Guru
    Currently Being Moderated

    Just because a database hasn't been used much in the past, doesn't mean it won't be in the future.  A db with less data than system probably isn't very interesting.

  • 12. Re: Tablespace Freespace
    petra-K Expert
    Currently Being Moderated

    Thanks JQ,

     

    Well it is just my test db , that is why.

  • 13. Re: Tablespace Freespace
    petra-K Expert
    Currently Being Moderated

    Hi Girish & All,

     

    >So I will not check the logical side but only the physical?

    No.

    You use the SHRINK SPACE clause of the ALTER TABLESPACE statement to shrink a temporary tablespace, or the SHRINK TEMPFILE clause of the ALTER TABLESPACEstatement to shrink a specific temp file of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp file. The optional KEEP clause defines a minimum size for the tablespace or temp file.



    I am really confused now, what if currently the database is processing or sorting so much big data and still using the TEMP and continually extending, How can I shrink the tempfile just to avoid the /u01 to get 100% full where the TEMP resides? Does this mean even if the database is currently using the TEMP and extending continually because it is sorting so much data, I can still shrink it?



    Thanks....

  • 14. Re: Tablespace Freespace
    sb92075 Guru
    Currently Being Moderated

    IMO, the TEMP tablespace datafile should NEVER have AUTOEXTEND  enabled.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points