Monday, January 7, 2019

ORA-01654 and ORA-03234 for index tablespace

When tables grow in size because of new data coming in, indexes also grow, and sometime indexes can grow even bigger as compare to table itself. There is a possibility that you see ORA-01654 while inserting/updating data because tablespace holding the index cannot be further extended and needs manual intervention to extend the tablespace so that index could further grow. Error would be returned to the application as well as reported in the alert log file.

ORA-01654: unable to extend index

ORA-1654: unable to extend index SCOTT.TESTIDX by 128 in tablespace MYIDX

ORA-03234: unable to extend index

ORA-3234 is also a similar error that may come when insertion and updation is done in a table with partitioned index, and index is not able to grow because index tablespace is full.
ORA-3234: unable to extend index SCOTT.MYINDEX subpartition MYINDX_88325_022014 by 128 in tablespace MYIDX
As explain above, both ORA-1654 and ORA-3234 surface because index on index partition cannot be extended because of lack of free space in the index tablespace. Obviously, the solution should be to extend the tablespace by adding a new datafiles or extending the existing datafiles.
For more explanation, read article about ORA-01653 that is about same type of error that comes during tables’ expansion.

No comments:

Post a Comment

Popular Posts - All Times