4

I referred to the below related question, but it was not helpful in my case:

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

I have 2 indexes in the same tablespace that I need to increase the size of.

I have 13 dbf files associated with that tablespace.

How can I make sure I increase the size of correct dbf file to fix the above error?

|improve this question
5

This error code, [ORA-01654], means the server has failed to allocate an extent of the required number of blocks for an index segment in the specified tablespace. As you have said you have got 13 datafiles in your tablespace, you are using small file tablespace. In order to get rid of this error, you can simply add data file using ALTER TABLESPACE ADD DATAFILE command to the specified tablespace.

First of all, you need to identify in which data file or files your index is being stored. Your index might have expanded into multiple data files. To identify the data files you can use the following SQL statement. 

select a.owner, a.index_name, b.file_name 
from dba_indexes a, dba_data_files b 
where a.tablespace_name=b.tablespace_name and a.index_name='Your index name'
|improve this answer
  • 1
    Why does the OP need to know which data file has his index? Can the OP simply grow the smallest data file in the relevant table space or just add a new data file to the relevant table space? – Gandolf989 Apr 12 '16 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.