| Temp tablespace NOT shrinking in oracle 11.2.0.3 [message #625568] |
Fri, 10 October 2014 06:30  |
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  |
John Watson
Messages: 8965
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
