ORA-12801: error signaled in parallel query server P029 [message #9166] |
Fri, 24 October 2003 03:05 |
Anirban
Messages: 3 Registered: April 2003
|
Junior Member |
|
|
Hi,
I am getting the following ORA error:
ORA-12801: error signaled in parallel query server P029
ORA-01652: unable to extend temp segment by 128 in tablespace TS_TEMP
I am inserting 200000 into a permanent table .In the select statement ,I join three tables each of them contains near about 500000 records.I use hash join and as well parallel(table,4,4).This query contains no group by and order by clause.My temp tablespace size is 16GB.What can be possible reason .It is very emergency.
|
|
|
|
|
Re: ORA-12801: error signaled in parallel query server P029 [message #9170 is a reply to message #9169] |
Fri, 24 October 2003 06:32 |
fly
Messages: 4 Registered: October 2003
|
Junior Member |
|
|
the join action also use sort segment.
You can increase sort_area_size and temp tablespace and try again.
if whatever you increase TEMP tablespace,the error still exists,you must apply patch.
You can search the number of the patch on metalink.oracle.com according the error.
|
|
|
Re: ORA-12801: error signaled in parallel query server P029 [message #9172 is a reply to message #9166] |
Fri, 24 October 2003 08:11 |
Thiru
Messages: 1089 Registered: May 2002
|
Senior Member |
|
|
Hash joins build hash table in the memory(the size allocated is determined by HASH_AREA_SIZE or PGA_AGGREGATE_TARGET). If it exceeds the memory allocated, then the partitions are written out to the temp space.
Did you force the hash join or did the optimizer pick this plan ? Make sure the hash table is built on the smaller of the tables involved. Did you analyze all of the tables involved. If for some cases,the larger table is hashed,then you will run out of temp space easily.
What type of temp tablespace are you using ? Is it Locally Managed with uniform extent sizes (1MB extents) ? IF its of PERMANENT type,release all of the space before starting the insert.
-Thiru
|
|
|