Creating an index in a temporary tablespace

Deleting data will not make any difference as Oracle will have kept the space ready to be re-filled. The top space usage in a table is known as the high water mark. Tom Kyte has a great post about it.

You reduce the high water mark by rebuilding the table:

alter table my_table_name move;

If you're still getting the error after increasing the size of the tablespace there are a couple of possible reasons...

1. The index is too big even for the newly increased tablespace. How big is the table and what are the combined size of the columns you're indexing?
2. You're not specifying a tablespace when creating the index and it's not using the one you think, whatever the default tablespace for your schema is.

The answer to 1 to is add more space.

To find out your default tablespace:

select * from dba_users where username = 'MY_USER'

If this is not your index tablespace you have to specify it when creating the index.

create index i_my_table 
    on my_table ( my_column ) 
       tablespace my_index_ts
       < other options >

Yes, if you're in 9i it's definitely worth collecting statistics as it doesn't collect them automatically when enough changes have been made to the table - it does in later versions. Use DBMS_STATS.GATHER_TABLE_STATS() and only do this after rebuilding the table.

Unfortunately in Oracle you can't reduce the size of a tablespace. To use this option you'd have to re-create the tablespace, but smaller, and move everything to it.

I understand your frustration with this behavior. There seem to be internal obstacles to implementing this and could lead to larger indexes than are necessary.

• With a unique index supporting a primary key, constraint validation is done before the unique index is modified and one look-up can verify whether the entry can be inserted or not.¹

• With a non-unique index supporting a primary key, constraint validation is done after changes are made to the non-unique index and multiple look-ups are required to verify whether the entry can be inserted or not.¹

A primary key using the leading column of a unique index would require non-unique index constraint logic on a unique index. This would require changes and/or additions to the logic.

If id is unique/primary then (id,val) is inherently unique, and normally you would not need/want a unique constraint/index on (id,val).

You might want a unique index on (id,val) if there were a query referencing (id,val) and you wanted to prevent a table look-up. If this is your situation, you may have to decided which is the lesser tradeoff between allowing the table look-up, increasing the primary key to (id,val) or having two unique indexes. For most situations I suspect the table look-up would be preferable.