ORA-01652 unable to extend temp segment
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