Getting rid of runaway temporary tablespace – 8 Gb reclaimed

10

Even though we keep telling ourselves that iron these days is cheap, I was having difficulties with my laptop’s hard disk. It was almost full – to the brim. I discovered a while back that my Oracle database files took up an alarming amount of diskspace, especially given the pathetically small volume of data I work with – only demo applications. The Temporary Tablespace’s datafile had grown to close to 8 Gb. That’s what autoextend can do for you….

 I am not much of a DBA, and to be frank I had no clue as to how to get rid of most of that 8 Gb. Google came to the rescue, and more specifically this article: Resizing Temporary Tablespace by Jeff Hunter. With his very clear explanation and straightforward instructions, I had fixed this problem that had been staring me in the face for quite some time now in no time at all:

....

SELECT tablespace_name, file_name, bytes<br />FROM dba_temp_files WHERE tablespace_name = 'TEMP'<br />/<br /> <br />TABLESPACE_NAME<br />------------------------------<br />FILE_NAME<br />------------------------------------<br />     BYTES<br />----------<br />TEMP<br />C:\ORACLE\ORADATA\ORCL\TEMP01.DBF<br />8148484096<br /><br />ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF' DROP INCLUDING DATAFILES;<br /><br />ALTER TABLESPACE temp ADD TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF' <br />SIZE 512m AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;<br />

Thanks Jeff!

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

10 Comments

  1. If I keep autoextend to off and set a maxsize will my temp tablespace will reclaim the the used space after reaching to maxsize i.e. after being 100% utilized or still I have to face the ora-01652 unable to extend the tempfile…?

  2. Set the MAXSIZE value (in your CREATE TEMPORARY TABLESPACE or ALTER DATABASE DATAFILE statement) to an appropriate size. Limit you autoextend…

    1)

    In other words, try to manage your total harddisk volume. As long as your harddisk hasn’t also has an autoextend feature AND/OR the Oracle database isn’t hardware aware and can (or want to…) automatically resize itself, you will have to MANAGE your temporary tablespace…

    In other other words, you will have to manage it (sorry for repeating).

    Using a DEMO database or a database created with the “wizard”, aka the database assistant…and it did assist you…nothing more nothing less…will not give you the perfect database layout for the purpose it will be created for (some stupid scripting from the old oracle 7 thinking days is still in there, so it won’t be perfect anyway).

    ….BUT, said that, the assistant is a good start to create the scripting used for creating the database.

    2)

    Via an alter database datafile ‘xxxx.dbf’ size xxx, one could try to resize the datafile to its HWM (high watermark).

    See for more info: http://asktom.oracle.com/pls/ask/f?p=4950:8:8472973305754934664::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:766625833673

    *)

    But don’t be afraid, a temporary tablespace is allowed to be full (the following explains it perfectly)…

    ——————————————————————————–
    Copied from http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:374218170986
    ——————————————————————————–

    temp segments still showing using
    November 10, 2005
    Reviewer: Ram from India

    V$sort_usage does not return any rows, still temp tablespace is almost full
    (98%). how does it happen ? how to free up temp tablespace ?

    Followup:

    that is perfect! excellent, that means future sorts will be most efficient as
    they won’t have the overhead of allocating extents again.

    The temporary tablespace should always appear “full” in a system that has been
    up and running – anything else would not be good.

    There is one temp segement
    This one segment is made up of extents
    This one segment is shared by all sessions
    Each extent will be used by ONE session

    When that session is done with it – that session will give the extent back to
    the “list of free extents for other sessions to use” in the temporary segment -
    but the temp segment will KEEP the extent so it need not reallocate it over and
    over and over again.

    So, everything is working perfectly and if you achieved your stated goal of
    “free up temp tablespace”, you would only negatively impact performance.

  3. Jan Benjamins on

    while awaiting moderation :), could you change my answer as followed to be more correct:

    actually, the procedure to do this in an active production environment is quite simple:
    - create a new temporary tablespace
    - change default temporary tablespace of all users using old temporary tablespace
    - drop old temporary tablespace when not in use anymore

  4. Jan Benjamins on

    actually, the procedure to do this in an active production environment is quite simple:
    - create a new temporary tablespace
    - change default temporary of all users (except sys,system etc)
    - drop old temporary tablespace when not in use anymore

  5. The example given by Jeff isn’t a great one. In a production environment you will lose “transactions”. Most of the time the space on the temporary tablespace can’t be freed because it contains data of connected sessions and you can’t shrink it because you probably would try to shrink it beyond the HIGHWATER MARK (HWM).

    Jeff in his example probably couldn’t free up the space after a bounce of the database because of or the HWM or the database was trying to recover from an “abort”.

    A DROP INCLUDING DATAFILES is a very aggressive way to get rid of your temporary tablespace and you leave you with a unwanted (corrupted) database situation. Never use it on normal tablespaces – if your not sure what you are doing OR don’t want to rebuild your demo environment

  6. Ehh Lucas, you should set the MAXSIZE parameter to a value which would suffice your harddisk needs, otherwise you will encounter the same problems in the near future.

    eg.

    ALTER DATABASE DATAFILE ‘C:\ORACLE\ORADATA\ORCL\TEMP01.DBF’ autoextend on next 250M maxsize 2048M

  7. This comment is not directly related to your blog entry. There is a javascript error on the page that displays the blog. Here is the code in error:

    for (var i = 0; !exists && !found && i

  8. Patrick Sinke on

    I’d take advantage of the opportunity and set the maxsize to one gig or so. Should be more than enough for any demo-database.
    Wasn’t there a rumour that in 11g you can shrink temp tablespaces? No luxury at all!