| temporary tablespace not being flushed automatically [message #57257] |
Sat, 31 May 2003 04:53  |
rameshkumar
Messages: 12
Registered: May 2003
|
Junior Member |
|
|
hi everybody,
i have a peculiar problem. in our database we have allocated 3 gb to temp tablespace. It was getting full and we have added another 500mb. after some days, it is also getting filled, and when ever queries r performed, it is throwing error "unable to extend temp segment .......".
How to encounter this problem of flushing the temporary tablespace without shutting down the database.
one solution is increasing the datafile.
But i want an alternative way of flushing the tablespace.
If any body has the solution, u r most welcome.
Thanks for the replies.
|
|
|
|
| Re: temporary tablespace not being flushed automatically [message #57263 is a reply to message #57257] |
Sat, 31 May 2003 08:53  |
Vikas Gupta
Messages: 115
Registered: February 2002
|
Senior Member |
|
|
Maybe because your temporary tablespace is defined as permanent.
select tablespace_name, contents from dba_tablespaces;
The contents of temporary tablespace should be "Temporary" and not "Permanent"
If it is "Permanent" change it to "Temporary" by using the following script.
Connect as Administrator as run the statement:
alter tablespace temporary_data temporary;
This should make the tablespace temporary.
If this is not the case, then maybe someone is runing a query that is not properly written. You will have to identify the query and correct it.
|
|
|
|
| Re: temporary tablespace not being flushed automatically [message #57300 is a reply to message #57263] |
Tue, 03 June 2003 10:10 |
Josean
Messages: 1
Registered: June 2003
|
Junior Member |
|
|
To flush the tmporary tablespace you can change the PCIINCREASE storage parameter like this:
alter tablespace temp default storage (pctincrease 0);
or you can ejecute this statement:
alter session set events 'immediate trace name DROP_SEGMENTS level 2147483648'
Regards
Josean
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
