Home » RDBMS Server » Server Administration » Temp tablespace NOT shrinking in oracle 11.2.0.3 (Oracle 11.2.0.3 / Redhad 6.3)
Temp tablespace NOT shrinking in oracle 11.2.0.3 [message #625568] Fri, 10 October 2014 06:30 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Issue
Our Temp tablespce is not shrinking automatically... after use.
We manually tried to shrink/resize ... but doesn't work
How to reclimb this temp space?
What could be the reason
Everytime we drop & recreate temp tablesapce


1) Size of our temp tbs
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP2 716800 18 716782


2)shrinking
SQL> ALTER TABLESPACE BFIJIP_TEMP2 SHRINK SPACE;
Tablespace altered.

3)shrinking with keep option
SQL> ALTER TABLESPACE TEMP2 SHRINK TEMPFILE '+DATA/xxxx/tempfile/temp2.320.860345413' KEEP 200000M;
Tablespace altered.


4) Resizing also not working
SQL> ALTER DATABASE TEMPFILE '+DATA/xxxx/tempfile/temp2.320.860345413' RESIZE 700000M;
ALTER DATABASE TEMPFILE '+DATA/xxxx/tempfile/temp2.320.860345413' RESIZE 700000M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


5) Size of our temp tbs
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP2 716800 18 716782

Regards
sbmk
Re: Temp tablespace NOT shrinking in oracle 11.2.0.3 [message #625569 is a reply to message #625568] Fri, 10 October 2014 07:34 Go to previous messageGo to next message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
Shrinking the temp tablespace doesn't work, as you have found. I raised a TAR about this for a customer a few months ago. Oracle Support just gave me that syntax (which we all know already) and then gave me a bunch of excuses. you have to create a new tablespace, and switch everyone over to it.

If enough people raise TARs about this, perhaps they'll fix it.
Re: Temp tablespace NOT shrinking in oracle 11.2.0.3 [message #625572 is a reply to message #625568] Fri, 10 October 2014 08:37 Go to previous message
atlas.dba
Messages: 5
Registered: August 2014
Location: Morocco
Junior Member
To resize the temp TBS refer to this article:

http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml

Regards
Previous Topic: Impact of parallel index rebuild on partition index
Next Topic: Error login to the Oracle 11g (3 merged by MC)
Goto Forum:
  


Current Time: Sun Feb 25 19:53:27 CST 2018

Total time taken to generate the page: 0.01379 seconds