This discussion is archived
9 Replies Latest reply: Dec 7, 2012 4:44 AM by EdStevens RSS

Temporary tablespace usage

user12006502 Newbie
Currently Being Moderated
I'm running Oracle 10.2.0.5 on Linux.


What's a good query on Temporary tablespace usage which also takes into account the MAX tempfile size?

Thank you
  • 1. Re: Temporary tablespace usage
    sb92075 Guru
    Currently Being Moderated
    user12006502 wrote:
    I'm running Oracle 10.2.0.5 on Linux.


    What's a good query on Temporary tablespace usage which also takes into account the MAX tempfile size?

    Thank you
    what if TEMP file has autoextend enabled?

    Handle:     user12006502
    Status Level:     Newbie
    Registered:     Feb 12, 2010
    Total Posts:     45
    Total Questions:     17 (12 unresolved)

    why so MANY unanswered questions?
  • 2. Re: Temporary tablespace usage
    user12006502 Newbie
    Currently Being Moderated
    yea, if a file's smaller than the maxsize, it would then have autoextend on.

    I did have questions where I didn't have a resolve, but I was given help towards a solution. I think I've resolved most of them since, but most of them weren't simple questions or problems I was facing during the time. I will go back, review them, give out points, and close them out accordingly
  • 3. Re: Temporary tablespace usage
    sb92075 Guru
    Currently Being Moderated
    query view below
    SQL> desc dba_temp_files
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     FILE_NAME                                          VARCHAR2(513)
     FILE_ID                                            NUMBER
     TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
     BYTES                                              NUMBER
     BLOCKS                                             NUMBER
     STATUS                                             VARCHAR2(7)
     RELATIVE_FNO                                       NUMBER
     AUTOEXTENSIBLE                                     VARCHAR2(3)
     MAXBYTES                                           NUMBER
     MAXBLOCKS                                          NUMBER
     INCREMENT_BY                                       NUMBER
     USER_BYTES                                         NUMBER
     USER_BLOCKS                                        NUMBER
  • 4. Re: Temporary tablespace usage
    user12006502 Newbie
    Currently Being Moderated
    The view on its own only takes into account the temp file size and maxsize, not the current usage of the temporary space against the maxsize of the tablespace.
  • 5. Re: Temporary tablespace usage
    sb92075 Guru
    Currently Being Moderated
    user12006502 wrote:
    The view on its own only takes into account the temp file size and maxsize, not the current usage of the temporary space against the maxsize of the tablespace.
    post SQL & results that you deem correct to report "current usage" of the temporary space.
  • 6. Re: Temporary tablespace usage
    user12006502 Newbie
    Currently Being Moderated
    By usage, I'm referring to the blocks currently allocated for sorting.
  • 7. Re: Temporary tablespace usage
    user12006502 Newbie
    Currently Being Moderated
    I would query v$sort_segment
  • 8. Re: Temporary tablespace usage
    moreajays Pro
    Currently Being Moderated
    Hi,


    select * from v$temp_space_header;

    11g --
    select TABLESPACE_NAME,round(sum(FREE_SPACE)/1024/1024,0) "FREE_MB" from dba_temp_free_space group by tablespace_name;

    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 9. Re: Temporary tablespace usage
    EdStevens Guru
    Currently Being Moderated
    user12006502 wrote:
    By usage, I'm referring to the blocks currently allocated for sorting.
    Ah, a number that will be obsolete before you've even read it.

    What is your real business objective? What problem are you trying to solve? What decision will be based on that obsolete number once you have it?

Legend

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