ORA-01654: unable to extend index by 128 in tablespace

oracle

I am not a DBA but have to resolve this issue.

There are 2 permanent tablespaces in my Oracle 9i database. One for the tables and the other for the Indexes other than the Temporary and UNDO tablespaces

Last week I got the exception

ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace>.

I got the tablespace size increased considerably. But still I am getting this error.

I deleted a lot of data thinking that would free-up some space. But that’s of no use.

Will shrinking the tablespace or re-sizing the datafiles of the permanent tablespace resolve this issue?

Also, the statistics hasn’t been gathered since March. Will gathering the latest statistics help me in any way?

Best Answer

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.

Related Solutions

Encountering exception ORA-01555

Gaius is right, use DBA_TABLES for NUM_ROWS and DBA_SEGMENTS for size:

select owner,table_name,num_rows,last_analyzed from dba_tables;

The num_rows count is as of LAST_ANALYZED date, which should be close enough even without running DBMS_STATS.

For sizing:

select owner,segment_type,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_type;

or (depending what level of details you need):

select owner,segment_name,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_name;

For LOBs you'll need to join it to DBA_LOBS, for indexes to DBA_INDEXES, for tables to DBA_TABLES. A lot will depend on your specific requirement. Since you mentioned you are trying to figure out used space in tablespace it might be as simple as:

select sum(bytes)/1024/1024 size_mb
from dba_segments
where tablespace_name='YOUR_TBLSP_NAME';

Your solution would really work only with small DB as it would not be feasible to read all data on large DB.

I am hesitant to post how to address ORA-1555 as in this case this it NOT your primary issue but just for completeness - you are on 10g using automatic undo management so your DBA would have to increase undo_retention in your database (the ixora link is relevant to a DB without auto undo management).

ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

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

Related Solutions

Encountering exception ORA-01555

ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

Related Question