Alter Session Failed: ORA-02097: Parameter Cannot Be Modified Because Specified Value Is Invalid (Doc ID 1584176.1)

Last updated on NOVEMBER 07, 2023

Applies to:

Symptoms

• Complete non atomic refresh of materialized view errors out with
  ORA-02097: parameter cannot be modified because specified value is invalid

• The error occurs during index rebuild job that is happening as the last part of the non atomic complete refresh.

• The failing internal statement is ALTER SESSION SET "sec_protocol_error_further_action" 

e.g.

ALTER SESSION SET "sec_protocol_error_further_action" = "delay, 10"

or the following, which shows an extract from the error stack dumped for 20297:

*** MODULE NAME:(DBMS_SCHEDULER) 2013-09-07 01:54:54.245
*** ACTION NAME:(XXYYZZ) 2013-09-07 01:54:54.245

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-02097: parameter cannot be modified because specified value is invalid
----- Current SQL Statement for this session (sql_id=xxxxxxxxxxxxx) -----
ALTER SESSION SET "sec_protocol_error_further_action" = delay, 10, drop, 10
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
41cb23b00      1862  package body SYS.DBMS_I_INDEX_UTL
41cb23b00      1263  package body SYS.DBMS_I_INDEX_UTL
4f4613fa8         1  anonymous block

----- Call Stack Trace -----
    kgeade <- kgeselv <- ksesecl0 <- opisecparamvalcb <- 112
       <- kspsetv0 <- kkyase <- kksExecuteCommand <- opiexe <- opipls
        <- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2
         <- rpidrv <- psddr0 <- psdnal <- pevm_EXIM <- pfrinstr_EXIM
          <- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe
           <- opiexe <- kpoal8 <- opiodr <- kpoodr <- upirtrc
            <- kpurcsc <- kpuexec <- OCIStmtExecute
 

• The value of the parameter set at instance level shows something like the following. Note in all these cases the syntax  of the value is not right.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.