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.
UNDO is the area that stores the change vectors required to restore the data to the state that it was in at the beginning of the transaction.
The only cures for this are to reduce the amount of data that you're changing (by changing less data or reducing the transaction size), or to increase the available undo size.
Reducing the transaction size, which you'd do by committing more frequently, is problematic because you still have to deal with rolling back transactions that you've committed, and because it would also promote snapshot too old errors when the database cannot reconstruct from undo the data required for consistency with the start of a query.
So, as commenters say, the answer really is to have the DBA's adjust the size of UNDO available. Strangely, they can be reluctant to do so, or at least to do so adequately. If you currently have 256MB of UNDO and you ask for 2GB, they might see this as some huge amount that might in some way be undesirable and offer you 1GB. Smile indulgently at them, because you already doubled the amount that you really wanted and because there is no such thing as oversizing your UNDO tablespace.
Best Answer
Error message
ORA-01652means: you don't have enough free temp space (enough free memory inside temp tablespace) or local temp segment not being able to extent space even though there is space in other instances.To troubleshoot this issue: execute the following commands:
then take the result from first SQL command to help you get free space inside that tablespace
If you get the result 0 thats mean the temp
tablespacedoesn't have enough free space, otherwise you need to check segment (mostly happened for RAC) by executing the following command:check total_blocks and used block and adjust the segment as needed.