This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 25, 2013 6:37 AM by Gerrit Haase RSS

Shrinking a Locally Managed Temporary Tablespace

Gerrit Haase Newbie
Currently Being Moderated
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
    user296828 Expert
    Currently Being Moderated
    Can you check the space shown by

    SELECT FROM dba_temp_free_space;*
  • 2. Re: Shrinking a Locally Managed Temporary Tablespace
    Gerrit Haase Newbie
    Currently Being Moderated
    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
    user296828 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    user296828 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    user296828 Expert
    Currently Being Moderated
    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
    user296828 Expert
    Currently Being Moderated
    Do not forget to mark the post as answered.
  • 13. Re: Shrinking a Locally Managed Temporary Tablespace
    Gerrit Haase Newbie
    Currently Being Moderated
    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
    user296828 Expert
    Currently Being Moderated
    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

Legend

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