Getting rid of runaway temporary tablespace – 8 Gb reclaimed
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!
-
Comments are closed
-
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 IndiaV$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 sessionWhen 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. -
#4 written by Jan Benjamins 6 years ago
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 -
#5 written by Jan Benjamins 6 years ago
-
-
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
-
-
-
#10 written by Patrick Sinke 6 years ago
- Comment Feed for this Post
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…?