Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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...

share|improve this question
Unless your dates aggregate really well, COMPRESS might not buy you much. Unless there are several NUMBERS for each distinct DATE, index compression adds overhead in both storage and processing. Have you considered instead partitioning the index? – Adam Musch Apr 21 '11 at 14:21

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.

share|improve this answer

Sure you can. Explicitly set the sort_area_size for your session and a lot of PQ. Adding a few tempfiles might be easier.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.