Oracle temporary tablespace

[TBTL]

Hi All, I have got a test server whose temporary tablespace size has exceeded 7 GB, I do not have much data in my base tablespaces, but still the temporary tablespace consumes huge amount of space. Can anyone help me reduce the size of it ?

 

[williey]

The temp tablespace is used by Oracle to temporarily store data when you run queries. It increases and reduces according to its needs.

If you have a runaway query, it will consume all the available temp space. Executions of functions and stored procedures will take up temp space too.

 

[SantaMufasa]

TBTL,

Oracle's "new-style" TEMPORARY tablespaces tend to run absolutely hog-wild on space consumption. That is why I still use Oracle's "traditional" TEMPORARY tablespaces. You can tell which type you have by doing a:

SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

If you see your temporary tablespace in the results, you are using traditional temporary tablespaces. If you don't see your temp tablespace listed there, then you are using "new-style".

If you are using "new-style", then the only successful process for resizing your temp tablespace is:

1) Plan to do this during low-usage (night) time.
2) Create a new temporary tablespace. (It will be a "place holder".)
3) Make the new temporary tablespace the default temporary tablespace.
4) Remove the old (runaway) temp tablespace.
5) Recreate another new temp tablespace with the old temp tablespace name.
6) Make the newer temp tablespace the default temporary tablespace.
7) Remove the "place holder" temporary tablespace.
8) Remove the disk file for the "place holder".

Here is prototypical code to do the above steps (presuming that your existing, runaway TEMP tablespace is named "TEMP" and the placeholder tablespace is "TEMP2"):

(Step 2) SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '<file_name>' SIZE 10M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Tablespace created.

(Step 3) SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

(Step 4) SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

(Step 5)  SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '<file_name of old TEMP ts file>' SIZE 10M REUSE
  3  AUTOEXTEND ON NEXT 10M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Tablespace created.

(Step 6) SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


(Step 7) SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

(Step 8) (execute o/s commands to remove "the placeholder" data file.)

You can redo this whenever your temp tablespace consumes too much disk space. Let us know your satisfaction with this method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[alexhu]

It increases and reduces according to its needs.

Just for the record - it never reduces on its own.

Alex

 

[SantaMufasa]

Amen, Alex!

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[taupirho]

The following from an ASKTOM discussion on temp tablespaces

i have a question about the temporary tablespace.
1.) if i'm running out space on temporary, how could i know which user or
transaction is using the temporary tablespace.

Temporary tablespaces should appear "full" after a while in a normally running database. Extents are allocated once and then managed by the system. Rather then doing the rather expensive operation of "space management" (data dictionary updates), the system will allocate an extent in TEMP and then keep it and manage it itself. This is normal and to be expected and is not an indication that
you do not have any temporary space.


AND ALSO


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.

 

[sem]

Tomreid, all the above is true, but...
When you have extra gigabytes of memory as well as extra terrabytes of disk space then most probably you may spent them to make Oracle 1% faster. But are you ready to pay that price? Once you had a task that allocated that 7Gb (maybe even due to some error like unnecessary sorting) are you ready to keep that amount untouched just to make another similar task in future a bit faster? Especially when your daily activities require only 1Gb!

Regards, Dima

http://seminihin.narod.ru

 

[TBTL]

Thank you all, i havent tried it yet, but i will definately in a day or two. I thank you all for the support given o reply to my question, i really appreciate it.

 

[TBTL]

Hi All,

The Tablespace in a UNDOTBS1 tablespace with UNDOTBS01.dbf as its datafile, i am not able to delete/drop the undo tablespace.