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

Adding temp datafile to default temp tablepsace

First we need to determine what is the current default temp tablespace and that can be done using


SQL> col PROPERTY_NAME for a40
SQL> col PROPERTY_VALUE for a30
SQL> col DESCRIPTION for a70
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE                 DESCRIPTION
---------------------------------------- ------------------------------ ---------------------------------------
DEFAULT_TEMP_TABLESPACE                  TEMP                           Name of default temporary tablespace

SQL>

to add the datafile to temp tablespace:

alter tablespace temp add tempfile '/u01/app/oracle/oradata/new_location/tempnew.dbf' size 700M reuse  autoextend on maxsize 700M;