| ORA-01652 - Temporary Tablespace problems!! [message #191303] |
Tue, 05 September 2006 10:52  |
Tizza
Messages: 1
Registered: September 2006
|
Junior Member |
|
|
Hi All
im having a problem with a temporary tablespace. Basically, while i am using a user level export, i get an error:
ORA-01652: unable to extend temp segment by xxx in tablespace xxxxxxxxxxxxx
i am using oracle 9i version Release 2 (9.2.0.1.0) for Windows
I have tried the following:
SQL> Shutdown Immediate
and
SQL> Startup
But the export still fails. It is my understanding that the temp tablespace filling up is normal as it keeps forming extents, but once the db is shutdown, it should clear the extents! This doesnt seem to happen.
Currently, the temp tablespace has auto extended itself to 20gb in size!!!!!!!! it was 10mb!
plz let me know your thoughts!
Cheers~~
|
|
|
|
|
|
| Re: ORA-01652 - Temporary Tablespace problems!! [message #191395 is a reply to message #191303] |
Wed, 06 September 2006 02:34  |
wwllzpz
Messages: 11
Registered: December 2005
Location: shanghai china
|
Junior Member |
|
|
hi,
restartup DB can not reduce temporary tablespace. it is managed automatically by DB.if want to reduce it ,you can build a new temporary tablespace and drop the old one.
your temp tablespace is 20gb in size, it is so astonish.
i think you need to tune your application. maybe a lot of sql statment with function that require sorts ,such as order by ,group by ,union, distinct and rollup.write sort runs to the temporary tablespace when the input size is larger than the work area in the PGA. The sort runs in the temporary tablespace are subsequently read and merged to provide the final result.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
