The answer to your question can be found in the SQL Language Reference (excerpt follows).
Creating Basic Tablespaces: Examples
This statement creates a tablespace
named tbs_01 with one data file:
CREATE TABLESPACE tbs_01 DATAFILE 'tbs_f2.dbf' SIZE 40M ONLINE;
It sound like you are new to Oracle databases. Oracle provides a wealth of documentation at http://docs.oracle.com/en/database. Specifically, I recommend reading their excellent introduction to the database called the Concepts Guide.
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...
- 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 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.
Best Answer
You have a few options.
Create your new bigfile tablespaces, then either:
1) Move the tables one by one with:
Remember to move indexes too!
2) Export the database with Data Pump, drop the existing objects, then reimport using a
remap_tablespaceclause, for example:3) Use
DBMS_REDEFINITIONto move the objects. An example of this can be found in my answer to another question, here.Once done, drop all of the old objects and then the tablespaces.
If you can afford the downtime, datapump will be the easiest option.