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.
When you delete data, oracle save delete data because perhaps transaction don't ends with commit and oracle need to restore delete data.
This copy of data is stored in rollback segments. In Oracle settings you have max extends for this segments.
Solution
- You can extend segments
- You can delete data by 'slices':
Sample spliting delete data:
DELETE FROM table SC
WHERE SC.evaluationMonth= 'Some month'
and SC.evaluationDay= '1';
COMMIT;
DELETE FROM table SC
WHERE SC.evaluationMonth= 'Some month'
and SC.evaluationDay= '2';
COMMIT;
...
DELETE FROM table SC
WHERE SC.evaluationMonth= 'Some month'
and SC.evaluationDay= '31';
COMMIT;
(you can do a loop, of course )
Best Answer
Autoextensible datafiles still have a size limit.
Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:
1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:
Datafiles have a hard limit for the maximum size, it is
2^22 * block_size
for smallfile tablespaces, and2^32 * block_size
for bigfile tablespaces.If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:
2) add a new datafile to the tablespace, for example: