| TEMP tablespace locked? [message #62632] |
Wed, 04 August 2004 06:49  |
Lisa
Messages: 31
Registered: September 2000
|
Member |
|
|
Hi Experts,
Our temp tablespace on our production database was full and unable to release resources even
there was no application process going on but the background process plus the dbsnmp, the
latest processes were data loading , which had been finished or interrupted.
We expected to see the temp tablespace frees up but it didn’t happen. Since the Temp
tablespace was locally managed, we cannot use syntax 'alter tablespace ... default storage..'
to free space as we usually do for dictionary managed tablespaces. Eventually we set the
datafiles of the temp tablespace as auto extended for a tempoary solution.
Finally, we need to bounce the database. Sometimes works and sometimes don’t. and it
happened constantly. It happend on 8i before and I heard it was bug. But now we already
upgraded to 9i. Any idea what causing this? Any suggestions how to fix this?
Many thanks!
|
|
|
|
| Re: TEMP tablespace locked? [message #62641 is a reply to message #62632] |
Wed, 04 August 2004 22:59  |
Vivek Vijai
Messages: 67
Registered: April 2004
|
Member |
|
|
Hi,
This happens in 9i also. Firstly you should never make the Autoextend on for TEMP tablespace, regularly monitor its size, have more that 1 file in the temp tablespace,etc.
Now one of the better soln is to create a new temp tablespace and drop the old one.
As per requirements allocate proper size to the new temp datafiles and make autoextend off.
If you want steps for the same i can provide
Vivek
|
|
|
|
| Re: TEMP tablespace locked? [message #62643 is a reply to message #62641] |
Wed, 04 August 2004 23:19 |
Satheesh Babu.S
Messages: 35
Registered: July 2004
|
Member |
|
|
Temp tablespace tend to show full is espected behaviour. It will cleaned up only during shutdown or startup. Oracle automatically use the used segment in the temp tablespace when it doesn't find space to create a new segment.
Thanks and Regards,
Satheesh Babu.S
Bangalore.
|
|
|
|
| Re: TEMP tablespace locked? [message #62656 is a reply to message #62641] |
Fri, 06 August 2004 06:23 |
Lisa
Messages: 31
Registered: September 2000
|
Member |
|
|
Hi Vivek,
Thanks for the input! Could you please explain why 'never make the Autoextend on for TEMP tablespace'???
If you could provide steps, that would be great!
Thanks a lot for your help!
Lisa
|
|
|
|
| Re: TEMP tablespace locked? [message #62671 is a reply to message #62656] |
Mon, 09 August 2004 00:18 |
Vivek Vijai
Messages: 67
Registered: April 2004
|
Member |
|
|
Hi,
Temp tablespace has a tendency to grow, so its better to keep autoextend off and monitor the same regularly.
It will use the unused segments if required or else
so can increase the size/add a file.
Steps to drop the old and recreate new are:
1)Select * from database_properties;
(To know the default temp tablespace)
2)create temporary tablespace TEMP_NEW
tempfile '/<path>/temp_new.dbf' size ***** extent management local;
3)Shutdown immediate
4)Startup restrict
5)Alter database default temporary tablespace TEMP_NEW
6)Select * from database_properties
7)Shutdown
8) Startup
U can also do the same while db is online but preferable u shud follow the above proc.
Vivek
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
