Q
Problem solve Get help with specific problems with your technologies, process and projects.

ORA-01654 even though maxextents defined as unlimited

I have a stored procedure running that has a query for inserting records into table A after fetching from table B. To date the code was running fine, but this morning it errored out with the ORA-01654 (Unable to extend index). All the indexes are defined with maxextents as unlimited. Please let me know the cause and the solution for this problem ASAP.

The reason you are getting this error is because your tablespace ran out of space where the index resides.

SELECT TABLESPACE NAME FROM USER_INDEXES 
WHERE INDEX_NAME = '&INDEX_NAME';  
-- provide the index_name that is failing to get extent allocation
Based on the tablespace named from the top, ask your DBA to:
ALTER TABLESPACE <tablespace_name_from_above_query> 
ADD DATAFILE 'full directory path within quotes with a file name' 
SIZE <a reasonable size in MBs denoted with the letter M>

This was last published in April 2005

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close