How to shrink temp tablespace in oracle

Question

How can we shrink temp tablespace in oracle  And why it is increasing so much like upto 25 GB since there is only one schema in the database for the application and data table space size is 2 GB and index table space size is 1 GB used

User · Answer

The options for managing tablespaces have got a lot better over the versions starting with 8i. This is especially true if you are using the appropriate types of file for a temporary tablespace (i.e. locally managed tempfiles).

So, it could be as simple as this command, which will shrink your tablespace to 128 meg...

alter tablespace <your_temp_ts> shrink space keep 128M;

The Oracle online documentation is pretty good. Find out more.

edit

It would appear the OP has an earlier version of the database. With earlier versions we have to resize individual datafiles. So, first of all, find the file names. One or other of these queries should do it...

select file_name from dba_data_files where tablespace_name = '<your_temp_ts>'
/

select file_name from dba_temp_files where tablespace_name = '<your_temp_ts>'
/

Then use that path in this command:

alter database datafile '/full/file/path/temp01.dbf'  resize 128m
/

User · Answer

You should have written what version of Oracle you use  You most likely use something else than Oracle 11g  that s why you can t shrink a temp tablespace   Alternatives   1  alter database tempfile   your file   resize 128M  which will probably fail 2  Drop and recreate the tablespace  If the temporary tablespace you want to shrink is your default temporary tablespace  you may have to first create a new temporary tablespace  set it as the default temporary tablespace then drop your old default temporary tablespace and recreate it  Afterwards drop the second temporary table created  3  For Oracle 9i and higher you could just drop the tempfile s  and add a new one s   Everything is described here in great detail     See this link  http   databaseguide blogspot com 2008 06 resizing-temporary-tablespace html It was already linked  but maybe you missed it  so here it is again

User · Answer

Temporary tablespaces are used for database sorting and joining operations and for storing global temporary tables  It may grow in size over a period of time and thus either we need to recreate temporary tablespace or shrink it to release the unused space   Steps to shrink TEMP Tablespace

User · Answer

Oh My Goodness  Look at the size of my temporary table space  Or    how to shrink temporary tablespaces in Oracle   Yes I ran a query to see how big my temporary tablespace is    SQL gt  SELECT tablespace name  file name  bytes 2  FROM dba temp files WHERE tablespace name like  TEMP     TABLESPACE NAME   FILE NAME                                 BYTES ----------------- -------------------------------- -------------- TEMP               the full path to temp01 dbf     13 917 200 000   The first question you have to ask is why the temporary tablespace is so large  You may know the answer to this off the top of your head   It may be due to a  large query that you just run with a sort that was a mistake  I have done that more than once    It may be due to some other exceptional circumstance   If that is the case then all you need to do to clean up is to shrink the temporary tablespace and move on in life   But what if you don t know   Before you decide to shrink you may need to do some investigation into the causes of the large tablespace   If this happens on a  regular basis then it is possible that your database just needs that much space   The dynamic performance view  V TEMPSEG USAGE   can be very useful in determining the cause    Maybe you just don t care about the cause and you just need to shrink it   This is your third day on the job   The data in the database is only 200MiB if data and the temporary tablespace is 13GiB - Just shrink it and move on   If it grows again then we will look into the cause   In the mean time I am out of space on that disk volume and I just need the space back   Let s take a look at shrinking it   It will depend a little on what version of Oracle you are running and how the temporary tablespace was set up  Oracle will do it s best to keep you from making any horrendous mistakes so we will just try the commands and if they don t work we will shrink in a new way   First let s try to shrink the datafile   If we can do that then we get back the space and we can worry about why it grew tomorrow   SQL gt  SQL gt  alter database tempfile   the full path to temp01 dbf  resize 256M   alter database tempfile   the full path to temp01 dbf  resize 256M      ERROR at line 1  ORA-03297  file contains used data beyond requested RESIZE value   Depending on the error message you may want to try this with different sizes that are smaller than the current site of the file   I have had limited success with this   Oracle will only shrink the file if the temporary tablespace is at the head of the file and if it is smaller than the size you specify   Some old Oracle documentation  they corrected this  said that you could issue the command and the error message would tell you what size you could shrink to   By the time I started working as a DBA this was not true   You just had to guess and re-run the command a bunch of times and see if it worked   Alright   That didn t work   How about this   SQL gt  alter tablespace YOUR TEMP TABLESPACE NAME shrink space keep 256M    If you are in 11g  Maybee in 10g too  this is it   If it works you may want to go back to the previous command and give it some more tries   But what if that fails   If the temporary tablespace is the default temporary that was set up when the database was installed then you may need to do a lot more work   At this point I usually re-evaluate if I really need that space back  After all disk space only costs  X XX a GiB   Usually I don t want to make changes like this during production hours   That means working at 2AM AGAIN    Not that I really object to working at 2AM - it is just that    Well I like to sleep too   And my wife likes to have me at home at 2AM     not roaming the downtown streets at 4AM trying to remember where I parked my car 3 hours earlier   I have heard of that  telecommuting  thing   I just worry that I will get half way through and then my internet connectivity will fail - then I have to rush downtown to fix it all before folks show up in the morning to use the database    Ok    Back to the serious stuff    If the temporary tablespace you want to shrink is your default temporary tablespace  you will have to first create a new temporary tablespace  set it as the default temporary tablespace then drop your old default temporary tablespace and recreate it  Afterwords drop the second temporary table created     SQL gt  CREATE TEMPORARY TABLESPACE temp2 2  TEMPFILE   the full path to temp2 01 dbf  SIZE 5M REUSE 3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited 4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M   Tablespace created   SQL gt  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2   Database altered   SQL gt  DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES   Tablespace dropped    SQL gt  CREATE TEMPORARY TABLESPACE temp 2  TEMPFILE   the full path to temp01 dbf  SIZE 256M REUSE 3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited 4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M   Tablespace created   SQL gt  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp   Database altered   SQL gt  DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES   Tablespace dropped    Hopefully one of these things will help

User · Answer

I don t bother with dropping the alternate temp in case i need to reclaim storage again in the future      from temp group set default to stand-alone temp wait awhile  then resize members of temp group set default back to temp group wait awhile  resize stand alone temp  there s no rush to do the last step

User · Answer

alter database datafile   C  ORA SERVER ORADATA AXAPTA AX DATA ORA  resize 40M    If it doesn t help    Create new tablespace Switch to new temporary tablespace Wait until old tablespace will not be used Delete old tablespace

User · Answer

It will be increasing because you have a need for temporary storage space  possibly due to a cartesian product or a large sort operation   The dynamic performance view V TEMPSEG USAGE will help diagnose the cause

[oracle] How to shrink temp tablespace in oracle?

Examples related to oracle