ORA-01631: max # extents (121) reached in table

Getting the message


ORA-12096: error in materialized view log on "HARVEY"."MV_HARVEY"
ORA-01631: max # extents (121) reached in table HARVEY.MLOG$_MV_HARVEY
ORA-02063: preceding 2 lines from DB_LINK_DATA

I was getting the error message when trying to insert a record using database link of DB_LINK_DATA.


This issue was resolved by updating the max_extents to unlimited on the target database (under DB_LINK_DATA). This was done by running the following command on the database under DB_LINK_DATA

SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME, PCT_FREE,PCT_USED,STATUS, MIN_EXTENTS, MAX_EXTENTS,NEXT_EXTENT from dba_tables where table_name ='MLOG$_ALL_OBJECT_ROLES';

OWNER      TABLE_NAME                     TABLE   PCT_FREE   PCT_USED STATUS   MIN_EXTENTS MAX_EXTENTS NEXT_EXTENT

---------- ------------------------------ ----- ---------- ---------- -------- ----------- ----------- -----------

HARVEY        MLOG$_MV_HARVEY         HARVEY           60         30 VALID              1  121      131072

alter table HARVEY.MLOG$_MV_HARVEY storage(maxextents unlimited);

SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME, PCT_FREE,PCT_USED,STATUS, MIN_EXTENTS, MAX_EXTENTS,NEXT_EXTENT from dba_tables where table_name ='MLOG$_ALL_OBJECT_ROLES';

OWNER      TABLE_NAME                     TABLE   PCT_FREE   PCT_USED STATUS   MIN_EXTENTS MAX_EXTENTS NEXT_EXTENT

---------- ------------------------------ ----- ---------- ---------- -------- ----------- ----------- -----------

HARVEY        MLOG$_MV_HARVEY         HARVEY           60         30 VALID              1  2147483645      131072