View Full Version: Oracle Temp Tablespace

LawsonTalk > System Admin > Oracle Temp Tablespace



Title: Oracle Temp Tablespace
Description: Reducing the size of the temp tablespace


LawsonsNbr1Fan - May 1, 2006 12:58 PM (GMT)
Our Temp tablespace is 2.5 Gig right now which isn't that large, but we would still like to reclaim some of that space if possible. I'm new to Oracle so I'm not sure what to do with it. Is there a way to reduce or reclaim the space taken up by the temp table in Oracle 9i for Lawson?

mthedford - May 1, 2006 07:18 PM (GMT)
Is it Dictionary or Local managed? Do you have it set to automatically grow when needed? Next, do you have any very large tables such as GLTRANS, PRDISTRIB, etc... When you recreate the indexes, it uses the temp tablespace during the index rebuild process and may need all of that space to finish. If you don't have it set to expand when needed, the index or anything else for that matter will fail when it reaches it's limit.

Currently, we have 51+ million records in GLTRANS and it expanded our temp tablespace out to 5.1gig the last time I reorged the database about 8 months ago when we were in the 45 mil range.

There are some steps you can take if you really have to shrink it down. You will need to determine how much is free space, if any users have extents allocated that haven't been released back to free space and see if they can be freed. Once this is done, then you can shrink down the tablespace.

IMO - it's better to have the space needed allocated, than to need the space and not have it to allocate.




Hosted for free by InvisionFree