Rebuild large Oracle 10g index with insufficient TEMP space?
What is TEMP used for in Oracle? As a scratch area for doing sorts that won't fit into main memory. So the issue is that you need to do a big sort in order to create your index, and you don't have a large enough PGA for it. So your options are: a larger PGA or a "temporary temporary" tablespace; simply create one big enough, make it your user's temporary tablespace (ALTER USER xxx TEMPORARY TABLESPACE temptemp;) build the index, then set your user back and drop it.