Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I referred to the below related question, but it was not helpful in my case:
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?
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'
