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;

Connection to the repository failed. Verify that the repository connection information provided is correct

Unable to start Oracle Enterprise Manager and getting the following messages:


./Oracle/Middleware/oms11g/bin/emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server is not functioning because of the following reason:
Connection to the repository failed. Verify that the repository connection information provided is correct.

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>Successfully started server EMGC_OMS1 ...

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>new status of EMGC_OMS1:

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>RUNNING

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>wait until EMGC_OMS1 becomes RUNNING

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>RUNNING

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>status of node manager:

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>Currently connected to Node Manager to monitor the domain GCDomain.

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>1

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>Successfully disconnected from Node Manager.

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>_END_

[Thread-2] INFO  wls.OMSController run.1094 - <ERR>_END_

[Main Thread] INFO  wls.OMSController processCmd.988 - wlst process is finished

[Main Thread] INFO  wls.OMSController processCmd.990 - wlst process exited with code 0

[Main Thread] INFO  wls.OMSController processCmd.992 - wlst outstream joined

[Main Thread] INFO  wls.OMSController processCmd.994 - wlst errstream joined

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.138 - HTTP port in emgc property file: <PORT>

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.139 - HTTPS port in emgc property file: <PORT>

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.155 - Using http port.

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.195 - Attempting to connect to http://hostname.domain.com:<PORT>/em/console/logon/logon

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.200 - page status code is 503

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.202 - header is 3

[Main Thread] INFO  wls.OMSController statusOMS.950 - statusOMS finished with result: 3

[Main Thread] INFO  wls.OMSController startOMS.603 - startOMS finished with result: 0

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.138 - HTTP port in emgc property file: <PORT>

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.139 - HTTPS port in emgc property file: <PORT>

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.155 - Using http port.

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.195 - Attempting to connect to http://hostname.domain.com:<PORT>/em/console/logon/logon

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.200 - page status code is 503

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.202 - header is 3

[Main Thread] INFO  wls.OMSController statusOMS.950 - statusOMS finished with result: 3

[Main Thread] DEBUG wls.OMSController main.167 - Log location is /home/oracle/Oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log

[Main Thread] INFO  wls.OMSController stopOMS.664 - istopAll is false

[Main Thread] INFO  wls.OMSController stopOMS.665 - isForce is false

[Main Thread] INFO  wls.OMSController stopOMS.666 - is service is false

[Main Thread] INFO  wls.OMSController getEnvProps.348 - Setting trust store system properties: -Dweblogic.security.TrustKeyStore=DemoTrust -Dweblogic.security.SSL.ignoreHostnameVerification=true -Djava.security.egd=file:///dev/urandom

[Main Thread] INFO  wls.OMSController stopOMS.682 - Stopping WebTier...

[Main Thread] INFO  util.WebTierUtil execCmd.134 - Running the command: /home/oracle/Oracle/gc_inst/WebTierIH1/bin/opmnctl stopall

[Main Thread] INFO  util.WebTierUtil getProcessResult.196 - opmnctl cmd output: opmnctl stopall: stopping opmn and all managed processes...

[Main Thread] INFO  util.WebTierUtil getProcessResult.204 - opmnctl cmd error: 

[Main Thread] INFO  util.WebTierUtil getProcessResult.208 - opmnctl cmd exited with code 0

Fix of this is very simple:

You are encountering this issue because EM is not able to connect to repository and in my case the listener was not running. Once the listener is started the Enterprise Manager was up.

 ./Oracle/Middleware/oms11g/bin/emctl start oms

Oracle Enterprise Manager 11g Release 1 Grid Control  

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Starting WebTier...

WebTier Successfully Started

Starting Oracle Management Server...

Oracle Management Server Successfully Started

Oracle Management Server is Up