Rebuild large Oracle 10g index with insufficient TEMP space? One of my (Oracle 10g) tables has grown over time to about 840 million rows. An index (DATE, NUMBER(38)) on it occupies 38GB at present. I'd like to change the index (I think it could make good use of COMPRESS, for example) but it fails with errors relating to TEMP getting full - it's 4GB at present.

Is there a way to build an index that will be larger than TEMP? I realise I could export the table, truncate it, create new and interesting indices and then reload, but that goes way beyond my capacity for pain...
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.