Home » RDBMS Server » Performance Tuning » ORA-01652 - Temporary Tablespace problems!!
ORA-01652 - Temporary Tablespace problems!! [message #191303] Tue, 05 September 2006 10:52 Go to next message
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 ( for Windows

I have tried the following:

SQL> Shutdown Immediate
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!

Re: ORA-01652 - Temporary Tablespace problems!! [message #191311 is a reply to message #191303] Tue, 05 September 2006 11:27 Go to previous messageGo to next message
Messages: 25578
Registered: January 2009
Location: SoCal
Senior Member
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
Re: ORA-01652 - Temporary Tablespace problems!! [message #191395 is a reply to message #191303] Wed, 06 September 2006 02:34 Go to previous message
Messages: 11
Registered: December 2005
Location: shanghai china
Junior Member

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.
Previous Topic: Hash join hint not being used
Next Topic: column to look for in v$system_event
Goto Forum:

Current Time: Mon Aug 21 16:50:09 CDT 2017

Total time taken to generate the page: 0.03446 seconds