Creating a large index failed :
ORA-1652: unable to extend temp segment by ...
or
ORA-01114: IO error writing block to file ...
Try to fix the problem shrinking the tempfile.
The following example shrinks the temporary tablespace temp1 to a size of 128m :
alter tablespace temp1 shrink space keep 128m;
The "shrink" clause is a feature of oracle version 11g.
Previous releases of oracle 11g do not include the "shrink" clause.
We try to resize and expand the tempfile.
The following example resizes the temporary tablespace to a size of 128m :
alter database tempfile '/oracle/data/tempfile.dbf' resize 128m;
The following example expands the temporary tablespace to a size of 8g :
alter database tempfile '/oracle/data/tempfile.dbf' resize 8g;
If the above method does not work, try to add a new tempfile.
The following example add a new tempfile to the tablespace temp1 :
alter tablespace temp1 add tempfile '/oracle/data/newtemp.dbf' size ... ;
Useful statements :
select * from v$tempfile;
select * from v$tempseg_usage;
select * from v$temp_space_header;