--- title: Oracle upgrade failures due to METHOD_OPT and XDBCONFIG description: Upgrading from Oracle 10g to 11.2.0.4 can fail due to METHOD_OPT casuing XDBCONFIG errors and XDB to become invalid. --- [Blog | Pythian ](https://www.pythian.com/blog) # [Oracle upgrade failures due to METHOD_OPT and XDBCONFIG](https://www.pythian.com/blog/errors-upgrading-oracle) Written by [Simon Pane](https://www.pythian.com/blog/author/simon-pane) | Nov 11, 2015 5:00:00 AM ## Background I recently experienced a problem when upgrading an old Oracle 10.2.0.4 database to 11.2.0.4 that had no matches in a My Oracle Support (MOS) or Google search. The problem presented itself initially when upgrading as the following error was reported by the upgrade script: > ``` > ERROR at line 1: > ORA-20001: invalid column name or duplicate columns/column groups/expressions > in method_opt > ORA-06512: at "SYS.UTL_RECOMP", line 865 > ORA-06512: at line 4 > > ```   Initially, the problem was reported in the upgrade log file for the ORACLE_OCM schema which is not critical. However, it later caused the XDB component to become invalid and consequently other components that depend on XDB to also become invalid. The error reported when trying to validate XDB was: > ``` > Warning: XDB now invalid, could not find xdbconfig > > ```   Even if not upgrading, this error could be encountered when trying to install or re-install the XDB component in an 11g database. XDB is a mandatory component as of Oracle 12c but is optional with 11g and below. Hence, it's possible to experience this same problem if you're trying to add the XDB component to an 11g database that didn't already have it.   ## "Warning: XDB now invalid, could not find xdbconfig" Several MOS documents already exist describing the error **"Warning: XDB now invalid, could not find xdbconfig"**. Those include: - Utlrp.sql results to "Warning: XDB Now Invalid, Could Not Find Xdbconfig" (Doc ID 1631290.1) - XDB Invalid after Utlrp during Activation of Extended Datatypes (Doc ID 1667689.1) - XDB Invalid After utl32k.sql during activation of extended datatypes (Doc ID 1667684.1) Unfortunately, none of those applied as either the cause or the solution to the problem I encountered. Either going through the XDB installation logs or simply manually running utlrp.sql shows that the xdbconfig is missing due to the **"ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt"** error. *For example:* > ``` > SQL> @?/rdbms/admin/utlrp > > TIMESTAMP > -------------------------------------------------------------------------------- > COMP_TIMESTAMP UTLRP_BGN 2015-11-09 11:36:22 > > DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid > DOC> objects in the database. Recompilation time is proportional to the > DOC> number of invalid objects in the database, so this command may take > DOC> a long time to execute on a database with a large number of invalid > DOC> objects. > DOC> > DOC> Use the following queries to track recompilation progress: > DOC> > DOC> 1. Query returning the number of invalid objects remaining. This > DOC> number should decrease with time. > DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); > DOC> > DOC> 2. Query returning the number of objects compiled so far. This number > DOC> should increase with time. > DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; > DOC> > DOC> This script automatically chooses serial or parallel recompilation > DOC> based on the number of CPUs available (parameter cpu_count) multiplied > DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). > DOC> On RAC, this number is added across all RAC nodes. > DOC> > DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel > DOC> recompilation. Jobs are created without instance affinity so that they > DOC> can migrate across RAC nodes. Use the following queries to verify > DOC> whether UTL_RECOMP jobs are being created and run correctly: > DOC> > DOC> 1. Query showing jobs created by UTL_RECOMP > DOC> SELECT job_name FROM dba_scheduler_jobs > DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; > DOC> > DOC> 2. Query showing UTL_RECOMP jobs that are running > DOC> SELECT job_name FROM dba_scheduler_running_jobs > DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; > DOC># > DECLARE > * > ERROR at line 1: > ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt > ORA-06512: at "SYS.UTL_RECOMP", line 865 > ORA-06512: at line 4 > > > > TIMESTAMP > -------------------------------------------------------------------------------- > COMP_TIMESTAMP UTLRP_END 2015-11-09 11:36:23 > > DOC> The following query reports the number of objects that have compiled > DOC> with errors. > DOC> > DOC> If the number is higher than expected, please examine the error > DOC> messages reported with each object (using SHOW ERRORS) to see if they > DOC> point to system misconfiguration or resource constraints that must be > DOC> fixed before attempting to recompile these objects. > DOC># > > OBJECTS WITH ERRORS > ------------------- > 0 > > DOC> The following query reports the number of errors caught during > DOC> recompilation. If this number is non-zero, please query the error > DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors > DOC> are due to misconfiguration or resource constraints that must be > DOC> fixed before objects can compile successfully. > DOC># > > ERRORS DURING RECOMPILATION > --------------------------- > 0 > > > Function created. > > > PL/SQL procedure successfully completed. > > > Function dropped. > > Warning: XDB now invalid, could not find xdbconfig > ORDIM INVALID OBJECTS: CARTRIDGE - INVALID - PACKAGE BODY > ORDIM INVALID OBJECTS: SI_IMAGE_FORMAT_FEATURES - INVALID - VIEW > ORDIM INVALID OBJECTS: SI_IMAGE_FORMAT_FEATURES - INVALID - SYNONYM > ORDIM INVALID OBJECTS: SI_IMAGE_FRMT_FTRS - INVALID - SYNONYM > ORDIM INVALID OBJECTS: ORDUTIL - INVALID - PACKAGE BODY > ORDIM INVALID OBJECTS: ORDIMG_PKG - INVALID - PACKAGE BODY > ORDIM INVALID OBJECTS: ORDIMGEXTCODEC_PKG - INVALID - PACKAGE BODY > ORDIM INVALID OBJECTS: ORDX_FILE_SOURCE - INVALID - PACKAGE BODY > ORDIM INVALID OBJECTS: DICOM_IMAGE105_T - INVALID - TYPE > ORDIM INVALID OBJECTS: exifMetadata243_T - INVALID - TYPE > ORDIM INVALID OBJECTS: PATIENT_STUDY129_T - INVALID - TYPE > ORDIM INVALID OBJECTS: GENERAL_SERIES134_T - INVALID - TYPE > ORDIM INVALID OBJECTS: GENERAL_IMAGE154_T - INVALID - TYPE > ORDIM INVALID OBJECTS: TiffIfd244_T - INVALID - TYPE > ORDIM INVALID OBJECTS: ExifIfd245_T - INVALID - TYPE > ORDIM INVALID OBJECTS: GpsIfd246_T - INVALID - TYPE > ORDIM INVALID OBJECTS: CODE_SQ103_T - INVALID - TYPE > ORDIM INVALID OBJECTS: iptcMetadataType94_T - INVALID - TYPE > ORDIM INVALID OBJECTS: IMAGE_PIXEL163_T - INVALID - TYPE > ORDIM registered 0 XML schemas. > The following XML schemas are not registered: > https://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0 > https://xmlns.oracle.com/ord/dicom/anonymity_1_0 > https://xmlns.oracle.com/ord/dicom/attributeTag_1_0 > https://xmlns.oracle.com/ord/dicom/constraint_1_0 > https://xmlns.oracle.com/ord/dicom/datatype_1_0 > https://xmlns.oracle.com/ord/dicom/manifest_1_0 > https://xmlns.oracle.com/ord/dicom/mapping_1_0 > https://xmlns.oracle.com/ord/dicom/mddatatype_1_0 > https://xmlns.oracle.com/ord/dicom/metadata_1_0 > https://xmlns.oracle.com/ord/dicom/orddicom_1_0 > https://xmlns.oracle.com/ord/dicom/preference_1_0 > https://xmlns.oracle.com/ord/dicom/privateDictionary_1_0 > https://xmlns.oracle.com/ord/dicom/rpdatatype_1_0 > https://xmlns.oracle.com/ord/dicom/standardDictionary_1_0 > https://xmlns.oracle.com/ord/meta/dicomImage > https://xmlns.oracle.com/ord/meta/exif > https://xmlns.oracle.com/ord/meta/iptc > https://xmlns.oracle.com/ord/meta/ordimage > https://xmlns.oracle.com/ord/meta/xmp > Locator INVALID OBJECTS: ALL_SDO_GEOM_METADATA - INVALID - VIEW > Locator INVALID OBJECTS: USER_SDO_INDEX_METADATA - INVALID - VIEW > Locator INVALID OBJECTS: ALL_SDO_INDEX_METADATA - INVALID - VIEW > Locator INVALID OBJECTS: USER_SDO_INDEX_INFO - INVALID - VIEW > Locator INVALID OBJECTS: ALL_SDO_INDEX_INFO - INVALID - VIEW > Locator INVALID OBJECTS: USER_SDO_LRS_METADATA - INVALID - VIEW > Locator INVALID OBJECTS: SDO_LRS_TRIG_INS - INVALID - TRIGGER > Locator INVALID OBJECTS: SDO_LRS_TRIG_DEL - INVALID - TRIGGER > Locator INVALID OBJECTS: SDO_LRS_TRIG_UPD - INVALID - TRIGGER > Locator INVALID OBJECTS: USER_SDO_TOPO_INFO - INVALID - VIEW > Locator INVALID OBJECTS: ALL_SDO_TOPO_INFO - INVALID - VIEW > Locator INVALID OBJECTS: USER_SDO_TOPO_METADATA - INVALID - VIEW > Locator INVALID OBJECTS: ALL_SDO_TOPO_METADATA - INVALID - VIEW > Locator INVALID OBJECTS: MDPRVT_IDX - INVALID - PACKAGE BODY > Locator INVALID OBJECTS: PRVT_IDX - INVALID - PACKAGE BODY > Locator INVALID OBJECTS: SDO_TPIDX - INVALID - PACKAGE BODY > Locator INVALID OBJECTS: SDO_INDEX_METHOD_10I - INVALID - TYPE BODY > Locator INVALID OBJECTS: SDO_GEOM - INVALID - PACKAGE BODY > Locator INVALID OBJECTS: SDO_3GL - INVALID - PACKAGE BODY > > PL/SQL procedure successfully completed. > > SQL> > > ```   Hence the ORA-20001 error is the true cause of the XDB problem.   ### "ORA-20001: Invalid column name or duplicate columns/column groups/expressions in method_opt" Searching My Oracle Support (MOS) for this error leads to the following notes: - Gather Table Statistics Fails With ORA-20001 ORA-06512 On "invalid Column Name" (Doc ID 1668579.1). - 11i - 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1). - Gather Schema Statistics Fails With Error For APPLSYS Schema (Doc ID 1393184.1). - Performance Issue Noted in Trading Partner Field of Invoice Workbench (Doc ID 1343489.1). Unfortunately, those are all related to specific tables from Oracle Applications Technology Stack, Oracle EBS, or Oracle Payables - none of those were applicable in my case. In my case the application was home grown. Hence, MOS and Google searches returned no relevant results.   ## The Root Cause & Solution The root cause of this problem was the **METHOD_OPT** parameter of **DBMS_STATS**. The METHOD_OPT parameter is related to how optimizer statistic histograms are collected for columns. METHOD_OPT is set using DBMS_STATS.SET_PARAM and can be queried through DBMS_STATS.GET_PARAM or directly from the underlying base table SYS.OPTSTAT_HIST_CONTROL$. *For example:* > ``` > SQL> exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO'); > > PL/SQL procedure successfully completed. > > SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual; > > DBMS_STATS.GET_PARAM('METHOD_OPT') > -------------------------------------------------------------------------------- > FOR ALL COLUMNS SIZE AUTO > > SQL> select sname, spare4 from SYS.OPTSTAT_HIST_CONTROL$ where sname = 'METHOD_OPT'; > > SNAME SPARE4 > ------------------------------ ---------------------------------------- > METHOD_OPT FOR ALL COLUMNS SIZE AUTO > > SQL> > > ```   The actual root cause of the ORA-20001 error and all of the subsequent failures and invalid components is that in the problematic database, the METHOD_OPT was set to the rarely used and outdated setting of **"FOR COLUMNS ID SIZE 1"**. From the database that experienced this issue: > ``` > SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual; > > DBMS_STATS.GET_PARAM('METHOD_OPT') > -------------------------------------------------------------------------------- > FOR COLUMNS ID SIZE 1 > > SQL> > > ```   The **"FOR COLUMNS ID SIZE 1"** setting was sometimes used in older versions of Oracle to prevent histogram buckets for being collected for primary keys and for plan stability through statistic changes. However, it should not be used for modern 11g or 12c databases. In fact it's not even settable through the DBMS_STATS package after Oracle 10g. Executing against an 11.2.0.4 database will give: > ``` > SQL> exec dbms_stats.set_param('METHOD_OPT','FOR COLUMNS ID SIZE 1'); > BEGIN dbms_stats.set_param('METHOD_OPT','FOR COLUMNS ID SIZE 1'); END; > > * > ERROR at line 1: > ORA-20001: method_opt should follow the syntax "[FOR ALL [INDEXED|HIDDEN] > COLUMNS [size_caluse]]" when gathering statistics on a group of tables > ORA-06512: at "SYS.DBMS_STATS", line 13179 > ORA-06512: at "SYS.DBMS_STATS", line 13268 > ORA-06512: at "SYS.DBMS_STATS", line 13643 > ORA-06512: at "SYS.DBMS_STATS", line 31462 > ORA-06512: at line 1 > > ```   Though it can still be set in 11.2.0.4 by directly updating SYS.OPTSTAT_HIST_CONTROL$, which is definitely NOT recommended. And of course this setting can be present in an 11g database that was upgraded from an older version such as a 10g release. Reverting this parameter to **"FOR ALL COLUMNS SIZE AUTO"** resolved the ORA-20001 error with UTL_RECOMP allowing the XDB component to validate and become VALID in the registry and subsequently all other components that depend on XDB.   ## Conclusion If upgrading an older databases to 11.2.0.4 (to remain on a supported version) it is prudent to check the setting of the METHOD_OPT parameter of the DBMS_STATS package. This isn't mentioned in any of the pre-upgrade documents or checklists and isn't caught by even the most recent version of Oracle's Database Pre-Upgrade Utility (MOS Doc ID 884522.1) or the DB Upgrade/Migrate Diagnostic Information (MOS Doc ID 556610.1). The check and solution are simple and should be incorporated into your own pre-upgrade procedure: > ``` > SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual; > > DBMS_STATS.GET_PARAM('METHOD_OPT') > -------------------------------------------------------------------------------- > FOR COLUMNS ID SIZE 1 > > SQL> exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO'); > > PL/SQL procedure successfully completed. > > SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual; > > DBMS_STATS.GET_PARAM('METHOD_OPT') > -------------------------------------------------------------------------------- > FOR ALL COLUMNS SIZE AUTO > > SQL> > > ```   **Discover more about our expertise in the world of [Oracle](https://www.pythian.com/database/oracle-consulting).** ## Oracle Database Consulting Services Ready to optimize your Oracle Database for the future? [View full post](https://www.pythian.com/blog/errors-upgrading-oracle) ```json { "@context" : "http://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Simon Pane" }, "dateModified" : "2026-03-04T14:45:01.785Z", "datePublished" : "2015-11-11T05:00:00Z", "headline" : "Oracle upgrade failures due to METHOD_OPT and XDBCONFIG", "image" : { "@type" : "ImageObject", "height" : 60, "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png", "width" : 60 }, "mainEntityOfPage" : "https://www.pythian.com/blog/errors-upgrading-oracle", "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "height" : 60, "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png", "width" : 60 }, "name" : "Pythian Blog" } } ```