ORA-01652 unable to extend temp segment

[KenEwald]

My question is how to avoid the error.

RAC environment, temp has lots of space on the data files to autoextend.

I'm creating a materialized view on a very large table (1+ billion rows) which is on a remote database. It runs for a very long time, then dies with ORA-01652 unable to extend temp segment.

I've been watching this:

SQL> list
1* select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment where tablespace_name = 'TEMP' order by inst_id

INST_ID TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------- ------------------------------- ------------ ----------- -----------
1 TEMP 625600 625600 0
2 TEMP 108736 256 108480

I'm investigating as an alternative, pre-creating the table. Does anyone know how the sync'ing works on this option works?

Ken

[KenEwald]

Scrap this thread, please...

ORA-01652 unable to extend temp segment by 64 in table space

Developer lopped off the tablespace name from the end of the error. I wrongly assumed it was in the TEMP tablespace.