Unable to extend TEMP tablespace

[himridul]

Hi ,

This type of query has run previously in the database . But now this query is getting the error message . We have 4 CPU's . I can't increse TEMP tablespace(not authorized to do this) . Is there any other solution except to increse the TEMP tablespace ? Though I've mentioned the tablespace IM2_D for the table , still it's refering TEMP tablespace , most probably for HASH partition .

SQL> create table ph_extract_tmp1 parallel 32 nologging
PARTITION BY HASH (AOL_INDV_ID)
PARTITIONS 32 STORE IN (IM2_D)
as select /*+ PARALLEL(a,16) PARALLEL(b,16) */ a.*
from ph_extract_tmp1_org a, dup_ph_tmp1 b
where a.rowid = b.row_id
2 3 4 5 6 7 ;
create table ph_extract_tmp1 parallel 32 nologging
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P033
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP


Thanks in advance ....

 

[alexhu]

How about running it at a time when more TEMP space will be available (i.e quiet time of the system)?

Alex

 

[himridul]

quiet time of the system"

It's not possible to do this . Because this table needs to be created in the middle of a cycle . In 72 hours of total duration of the process , this table needs to be created after 24 hours(approx) .

 

[sem]

Can you discribe your original task? As I may suppose this is just a part, because for some reason another process filled that dup_ph_tmp1 table with rowids. Maybe we'll try to suggest another solution for the whole task and avoid this step at all. Would you also post an execution plan of that query?

Regards, Dima

http://seminihin.narod.ru