IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I got ” ORA-02097: parameter cannot be modified because specified value is invalid ” error in Oracle database.
ORA-02097: parameter cannot be modified because specified value is invalid
Details of error are as follows.
ORA-02097: parameter cannot be modified because specified value is invalid. Cause: Though the initialization parameter is modifiable, the modified value is not acceptable to the parameter. Action: Check the DBA guide for range of acceptable values for this parameter.
You can get the below error while setting the initialization parameter with non-SYS/DBA user SQL> alter session set db_create_file_dest='+DG_DATA_01_CDB1DEV'; ERROR: ORA-02097: parameter cannot be modified because specified value is invalid ORA-01031: insufficient privileges Even user this have the "alert session" privileges
parameter cannot be modified because specified value is invalid
This ORA-02097 error is related with the initialization parameter is modifiable, the modified value is not acceptable to the parameter.
Alter session is insufficient on non-sys/dba users use to alter few initialization parameter example : db_create_file_dest
– Grant “alter database” system privilege to non-sys/dba users.
++++ TEST CASE +++++++
SQL> conn / as sysdbA
Connected.
SQL> create <user> identified by <password>;
User created.
SQL> grant create session to <user> ;
Grant succeeded.
SQL> grant alter database to <user> ;
Grant succeeded.
SQL> conn <user>/<password>
Connected.
SQL> alter session set db_create_file_dest=’/u01/db/db12/12.2.0/dbs’;
Session altered.
SQL> select * from session_privs;
PRIVILEGE
—————————————-
ALTER DATABASE
CREATE SESSION
OR
When you set any parameter incorrectly, you will get this error as follows.
ALTER SESSION SET "sec_protocol_error_further_action" = "delay, 10"
ORA-02097: parameter cannot be modified because specified value is invalid
- 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.
SQL> show parameter sec_protocol NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_protocol_error_further_action string delay, 10, drop, 10 or SQL> show parameter sec_protocol NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_protocol_error_further_action string delay, 10
The syntax of the value of the parameter sec_protocol_error_further_action does not look right at instance level already.
How the invalid value got into there at instance level is not fully explored yet. There are cases where it was set from init.ora.
Nevertheless the value of the parameter should be with brackets around them, e.g. as can be seen below:
SQL> show parameter sec_protocol_error_further_action NAME TYPE VALUE ------------------------------------ ----------- ------------- sec_protocol_error_further_action string (delay, 10)
1./ Check the value of the paramter at instance level
SQL>conn sys as sysdba SQL> show parameter sec_protocol_error_further_action
2./ Make sure the value set is valid.
To solve this error, set this parameter as follows.
SQL> alter system set "sec_protocol_error_further_action" = "(delay, 10)" scope=both;
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
