9 Replies Latest reply: Dec 7, 2012 6:44 AM by EdStevens RSS

    Temporary tablespace usage

    user12006502
      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
          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
            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
              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
                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
                  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
                    By usage, I'm referring to the blocks currently allocated for sorting.
                    • 7. Re: Temporary tablespace usage
                      user12006502
                      I would query v$sort_segment
                      • 8. Re: Temporary tablespace usage
                        moreajays
                        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
                          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?