1 2 Previous Next 18 Replies Latest reply: Apr 25, 2013 8:37 AM by Gerrit Haase RSS

    Shrinking a Locally Managed Temporary Tablespace

    Gerrit Haase
      So, even thoguh the documentation is pretty clear about how to use this feature, I cannot get it to do what I expect it to do for me.

      And that would be shrinking the tempfile ;)

      Now lets face it, I have a large tempfile and want to resize it without restarting the database:

      C:\Users\Administrator>sqlplus / as sysdba

      SQL*Plus: Release 11.2.0.2.0 Production on Di Nov 20 05:49:59 2012

      Copyright (c) 1982, 2010, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

      SQL> select file_name
      , ceil(bytes / 1024 / 1024) "size MB"
      from dba_temp_files
      /

      FILE_NAME size MB
      ---------------------------------------------------------------------- ------------
      R:\MXVC01\TEMP01.DBF 31,231

      SQL> select su.username
      , ses.sid
      , ses.serial#
      , su.tablespace
      , ceil((su.blocks * dt.block_size) / 1048576) MB
      from v$sort_usage su
      , dba_tablespaces dt
      , v$session ses
      where su.tablespace = dt.tablespace_name
      and su.session_addr = ses.saddr
      /

      USERNAME SID SERIAL# TABLESPACE MB
      --------------- ---------- ---------- ------------------------------- --------
      VPXADMIN 15 15 TEMP 14
      VPXADMIN 17 5 TEMP 1,203
      VPXADMIN 17 5 TEMP 1
      VPXADMIN 18 3 TEMP 7
      VPXADMIN 19 3 TEMP 1
      VPXADMIN 144 3 TEMP 1
      VUMADMIN 156 2597 TEMP 1

      7 rows selected.

      Or this one:

      SQL> select tablespace_size/1024/1024 "tablespace_size mb"
      , allocated_space/1024/1024 "allocated_space mb"
      , free_space/1024/1024 "free_space mb"
      from dba_temp_free_space
      /

      tablespace_size mb allocated_space mb free_space mb
      ------------------ ------------------ -------------
      31230,9922 1228,99219 30002



      Documetation from here: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces007.htm#ADMIN12353


      +"Shrinking a Locally Managed Temporary Tablespace+
      +Large sort operations performed by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space. After the sort operation completes, the extra space is not released; it is just marked as free and available for reuse. Therefore, a single large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete. For this reason, the database enables you to shrink locally managed temporary tablespaces and release unused space.+

      +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 tempfile of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or tempfile. The optional KEEP clause defines a minimum size for the tablespace or tempfile.+

      +Shrinking is an online operation, which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected.+

      +The following example shrinks the locally managed temporary tablespace lmtmp1 to a size of 20M.+

      +ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;+

      +The following example shrinks the tempfile lmtemp02.dbf of the locally managed temporary tablespace lmtmp2. Because the KEEP clause is omitted, the database attempts to shrink the tempfile to the minimum possible size.+

      +ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';"+



      OK, lets do it:

      SQL> alter tablespace temp shrink tempfile 'R:\MXVC01\TEMP01.DBF';
      alter tablespace temp shrink tempfile 'R:\MXVC01\TEMP01.DBF'
      *
      ERROR at line 1:
      ORA-03214: File Size specified is smaller than minimum required


      It seems there is a bug? Should I report it, or is it the expected behaviour?

      Now lets try this one:

      SQL> alter tablespace temp shrink tempfile 'R:\MXVC01\TEMP01.DBF' keep 2048M;

      Tablespace altered.

      SQL> select file_name
      , ceil(bytes / 1024 / 1024) "size MB"
      from dba_temp_files
      /

      FILE_NAME size MB
      ---------------------------------------------------------------------- ------------
      R:\MXVC01\TEMP01.DBF 31,231

      So .... this lasts about *10 minutes*, and nothing changes?

      It seems there is a bug? Should I report it, or is it the expected behaviour?


      Could someone enlighten me, what this SHRINK is actually doing?
      Is it worth to report this as bug, if not a software bug it is at least a documentation bug because it doesn't mention under which conditions it is working?


      P.S.: OMG the posting looks terrible, who's the one to blame for this forum software where it is not possible to use fixed size fonts, or format paragraphs as code, or what about the fact that the forum software is using default SQLPlus output as META for some graphical lines?

      Isn't this the forum for Oracle Database users?

      Edited by: Gerrit Haase on 20.11.2012 13:44
        • 1. Re: Shrinking a Locally Managed Temporary Tablespace
          12cdb
          Can you check the space shown by

          SELECT FROM dba_temp_free_space;*
          • 2. Re: Shrinking a Locally Managed Temporary Tablespace
            Gerrit Haase
            I did it, see above.

            Is it maybe an Enterprise feature?

            Edit:
            SQL> SELECT * FROM dba_temp_free_space;

            TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
            ---------------------------------------- --------------- --------------- ----------
            TEMP 3,2748E+10 1298128896 3,1451E+10
            • 3. Re: Shrinking a Locally Managed Temporary Tablespace
              12cdb
              NO, NO, You didn't

              I said, you to run this query

              SELECT * FROM dba_temp_free_space;
              • 4. Re: Shrinking a Locally Managed Temporary Tablespace
                Gerrit Haase
                Well, no need to shout.

                I am sure you saw my edit already, here again, and the output is pretty much the same as my query at the top?

                Yours:

                SQL> SELECT * FROM dba_temp_free_space;

                TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
                ---------------------------------------- --------------- --------------- ----------
                TEMP 3,2748E+10 1298128896 3,1451E+10


                Mine:

                SQL> select tablespace_size/1024/1024 "tablespace_size mb"
                2 , allocated_space/1024/1024 "allocated_space mb"
                3 , free_space/1024/1024 "free_space mb"
                4 from dba_temp_free_space
                5 /

                tablespace_size mb allocated_space mb free_space mb
                ------------------ ------------------ -------------
                31230,9922 1238,99219 29993


                So, would you please enlighten me and explain why yours is different to my initial query?
                • 5. Re: Shrinking a Locally Managed Temporary Tablespace
                  Helios-GunesEROL
                  Hi;
                  ORA-03214: File Size specified is smaller than minimum required
                  Please review:
                  ORA-03214: File Size specified is smaller than minimum required

                  Regard
                  Helios
                  • 6. Re: Shrinking a Locally Managed Temporary Tablespace
                    Gerrit Haase
                    So the error ORA-03214 tells me that the minimum size is already the current size and it is not possible to shrink the tempfile anymore?

                    Or did you mean the other part where the poster says it is a bug?

                    Question is still the same, what is the shrink tempfile command good for, if it doesn't relocate contents in the tempfile and reset the HWM and therefore can't resize the tempfile anyways?
                    • 7. Re: Shrinking a Locally Managed Temporary Tablespace
                      12cdb
                      SQL> define
                      DEFINE ORELEASE = "1102000300" (CHAR)

                      SQL> select TABLESPACE_SIZE/power(2,20), ALLOCATED_SPACE/power(2,20), FREE_SPACE/power(2,20) from dba_temp_free_space ;

                      TABLESPACE_SIZE/POWER(2,20) ALLOCATED_SPACE/POWER(2,20) FREE_SPACE/POWER(2,20)
                      ------------------------------------------------------------------------------------------------------------------------------------------------
                      15358 15358 15357

                      SQL> ALTER TABLESPACE temp SHRINK SPACE;

                      Tablespace altered.

                      SQL> select TABLESPACE_SIZE/power(2,20), ALLOCATED_SPACE/power(2,20), FREE_SPACE/power(2,20) from dba_temp_free_space ;

                      TABLESPACE_SIZE/POWER(2,20) ALLOCATED_SPACE/POWER(2,20) FREE_SPACE/POWER(2,20)
                      ------------------------------------------------------------------------------------------------------------------------------------------------
                      1.9921875 .9921875 1

                      Look at the temp file size at OS level

                      2.0M Nov 20 13:39 /myownpath/temp01.dbf


                      there is no bug, you were looking at the wrong place for free space.
                      • 8. Re: Shrinking a Locally Managed Temporary Tablespace
                        Gerrit Haase
                        So, you are kidding with me? No? Who are you?

                        How can I block users here? Is there a moderator present at this forum?

                        Maybe you read my initial post again?
                        I didn't look at the wrong place.

                        I reported you for general abuse.


                        SQL> define
                        DEFINE _DATE           = "20.11.12" (CHAR)
                        DEFINE CONNECTIDENTIFIER = "MXVC01" (CHAR)
                        DEFINE _USER           = "SYS" (CHAR)
                        DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
                        DEFINE SQLPLUSRELEASE = "1102000200" (CHAR)
                        DEFINE _EDITOR         = "Notepad" (CHAR)
                        DEFINE OVERSION = "Oracle Database 11g Release 11.2.0.2.0 - 64bit Production" (CHAR)
                        DEFINE ORELEASE = "1102000200" (CHAR)

                        SQL> SELECT * FROM dba_temp_free_space;

                        TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
                        ---------------------------------------- --------------- --------------- ----------
                        TEMP 3,2748E+10 1306517504 3,1443E+10

                        SQL> select TABLESPACE_SIZE/power(2,20), ALLOCATED_SPACE/power(2,20), FREE_SPACE/power(2,20) from dba_temp_free_space ;

                        TABLESPACE_SIZE/POWER(2,20) ALLOCATED_SPACE/POWER(2,20) FREE_SPACE/POWER(2,20)
                        --------------------------- --------------------------- ----------------------
                        31230,9922 1245,99219 29986

                        SQL> ALTER TABLESPACE temp SHRINK SPACE;

                        Tablespace altered.

                        SQL> select TABLESPACE_SIZE/power(2,20), ALLOCATED_SPACE/power(2,20), FREE_SPACE/power(2,20) from dba_temp_free_space ;

                        TABLESPACE_SIZE/POWER(2,20) ALLOCATED_SPACE/POWER(2,20) FREE_SPACE/POWER(2,20)
                        --------------------------- --------------------------- ----------------------
                        31230,9922 1244,99219 *29986*


                        R:\mxvc01>dir temp
                        Volume in drive R is Disk_R
                        Volume Serial Number is 248B-61D4

                        Directory of R:\mxvc01

                        20.11.2012 08:09 32.748.077.056 TEMP01.DBF
                        1 File(s) 32.748.077.056 bytes
                        0 Dir(s) 8.259.297.280 bytes free

                        SQL> alter tablespace temp shrink tempfile 'R:\mxvc01\TEMP01.DBF';
                        alter tablespace temp shrink tempfile 'R:\mxvc01\TEMP01.DBF'
                        *
                        ERROR at line 1:
                        ORA-03214: File Size specified is smaller than minimum required


                        *It clearly says that there is 29986 MB Space FREE and the above shrink space changes nothing and so does shrink tempfile:*

                        SQL> alter tablespace temp shrink tempfile 'R:\mxvc01\TEMP01.DBF';
                        alter tablespace temp shrink tempfile 'R:\mxvc01\TEMP01.DBF'
                        *
                        ERROR at line 1:
                        ORA-03214: File Size specified is smaller than minimum required

                        SQL> alter tablespace temp shrink tempfile 'R:\mxvc01\TEMP01.DBF' KEEP 20M;

                        Tablespace altered.

                        R:\mxvc01>dir temp
                        Volume in drive R is Disk_R
                        Volume Serial Number is 248B-61D4

                        Directory of R:\mxvc01

                        20.11.2012 08:24 32.748.077.056 TEMP01.DBF
                        1 File(s) 32.748.077.056 bytes
                        0 Dir(s) 8.259.280.896 bytes free

                        *... nothing changes, the tempfile isn't smaller now ...*
                        • 9. Re: Shrinking a Locally Managed Temporary Tablespace
                          Helios-GunesEROL
                          Hi;

                          You can follow note      How to Resize a Datafile [ID 1029252.6] and can see how you can resize your datafile.

                          If you can not shrink your datafile as you expect than you can create new one and drop other. Please review:
                          How to Shrink the Datafile of Temporary Tablespace [ID 273276.1]
                          DB 11.1: Temporary Tablespaces [Video] [ID 1099324.1]
                          Resizing (or Recreating) the Temporary Tablespace [ID 409183.1]

                          Regard
                          Helios
                          • 10. Re: Shrinking a Locally Managed Temporary Tablespace
                            Gerrit Haase
                            Thanks Helios,

                            5 points ;)

                            However the feature isn't really working for me, so I consider to report this as a bug.
                            • 11. Re: Shrinking a Locally Managed Temporary Tablespace
                              12cdb
                              Don't abuse, if you can't read the differences. Do not get excited?

                              What do you think about yourself at first place? Advice for you, have some patient will help you.

                              The difference is your test is run on Windows where as I have commands on Linux.

                              If you really get frustrated, why do not you search for bugs in Metalink.

                              You should believe in yourself and search for bug in Metalink.
                              • 12. Re: Shrinking a Locally Managed Temporary Tablespace
                                12cdb
                                Do not forget to mark the post as answered.
                                • 13. Re: Shrinking a Locally Managed Temporary Tablespace
                                  Gerrit Haase
                                  11g,

                                  I confident enough about myself, so I think I did already enough research at MOS at first, enough research before posting here, but there are no relevant information at MOS afaics.
                                  What I found there are all these notes also mentioned in the post by Helios and I found the doc about the new shrink feature (How To Shrink A Temporary Tablespace in 11G ? [ID 452697.1]) which also doesn't say how it works exactly on different platforms.

                                  If this SHRINK feature doesn't work on Windows, or there are differences in the behaviour, the docs should mention this, so I would be able to know what to expect, don't you think?

                                  What do you think about yourself, at first you didn't read my initial post, where I showed that I already selected the dba_temp_free_space view, and then after a little conversation you tell me that I did look at the wrong place?

                                  So I concluded, instead of admitting that you overlooked this in my initial post you started bullying me.


                                  And to answer your 2nd follow-up: Nope, I won't mark the question as answered, because my question was, if this is a bug or a feature and whether it is worth reporting this, and you didn't answer this, and you didn't earn points for your replies.
                                  • 14. Re: Shrinking a Locally Managed Temporary Tablespace
                                    12cdb
                                    It is open forum, why did you think that someone has started bullying you? I think no one should, it is just the way you think. Also your attempt here trying to justify something a bug or feature will not go anywhere better raising a SR with oracle and listen
                                    1 2 Previous Next