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
FROM dba_temp_files WHERE tablespace_name = 'TEMP'
/
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------
BYTES
----------
TEMP
C:\ORACLE\ORADATA\ORCL\TEMP01.DBF
8148484096
ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF' DROP INCLUDING DATAFILES;
ALTER TABLESPACE temp ADD TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF'
SIZE 512m AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;
Thanks Jeff!
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…?
Follow up:
so it is good to have a full temporary tablespace, as long as it doesn’t fill up your harddisk…
http://asktom.oracle.com/~tkyte/Misc/free.html
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.
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
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
So what would you recommend to remedy the oversized Temporary Tablespace?
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
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
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
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!