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 really good (unsurprisingly) post here 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...
- 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?
- You're not specifying a tablespace when creating the index and it's using a different one, 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.
