8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Using Expressions in Initialization Parameters in Oracle Database 21c
Oracle database 21c introduced the ability to use expressions to set initialization parameters. These expressions can reference other parameters and environment variables.
For more information see:
Referencing Parameters
We check the values of the JOB_QUEUE_PROCESSES
and PROCESSES
parameters. We see the values are 80 and 400 respectively.
SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 80 log_archive_max_processes integer 4 processes integer 400 SQL>
We set the JOB_QUEUE_PROCESSES
parameter to 1/10 of the PROCESSES
parameter. In this case we use the MAX
function, to make sure the JOB_QUEUE_PROCESSES
parameter value never drops below 10.
SQL> alter system set job_queue_processes='max(processes/10,10)'; System altered. SQL>
We check the JOB_QUEUE_PROCESSES
parameter again, and we see it has been set to the correct value.
SQL> show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 40 SQL>
We create a parameter file based on the current spfile.
SQL> create pfile='/tmp/pfile.txt' from spfile; File created. SQL>
We check the setting of the JOB_QUEUE_PROCESSES
parameter in the resulting pfile.
SQL> host fgrep job_queue_processes /tmp/pfile.txt *.job_queue_processes=max(processes/10,10) SQL>
So this has not only set the correct JOB_QUEUE_PROCESSES
parameter value, but maintained the relationship to the PROCESSES
parameter in the parameter definition.
As a result, if we alter the PROCESSES
parameter value, we will also be altering the JOB_QUEUE_PROCESSES
parameter value.
SQL> alter system set processes=600 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1.4496E+10 bytes Fixed Size 9702824 bytes Variable Size 2147483648 bytes Database Buffers 1.2314E+10 bytes Redo Buffers 23851008 bytes Database mounted. Database opened. SQL> show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 60 SQL>
Referencing Environment Variables
We check the current setting of the ORACLE_BASE
environment variable.
SQL> host echo $ORACLE_BASE /u01/app/oracle SQL>
We check the current value of the AUDIT_FILE_DEST
parameter.
SQL> show parameter audit_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/cdb1_lhr 12p/adump SQL>
We replace the path with one containing the ORACLE_BASE
environment variable.
SQL> alter system set audit_file_dest='$ORACLE_BASE/admin/cdb1_lhr12p/adump' scope=spfile; System altered. SQL>
We create a parameter file based on the current spfile.
SQL> create pfile='/tmp/pfile.txt' from spfile; File created. SQL>
We check the setting of the AUDIT_FILE_DEST
parameter in the resulting pfile.
SQL> host fgrep audit_file_dest /tmp/pfile.txt *.audit_file_dest='$ORACLE_BASE/admin/cdb1_lhr12p/adump' SQL>
So now the audit location is based on the value of the ORACLE_BASE
environment variable, as it was set at instance startup.
We need to make sure any required environment variables are set before startup time, or the instance will not start. As an example, we unset the ORACLE_BASE
environment variable value.
$ unset ORACLE_BASE $ echo $ORACLE_BASE; $
We shutdown the instance, and when we attempt to start it we get an error.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORA-07217: sltln: environment variable cannot be evaluated. SQL>
We exit SQL*Plus and set the environment variable again.
$ export ORACLE_BASE=/u01/app/oracle
Now the instance starts as expected.
SQL> startup; ORACLE instance started. Total System Global Area 1.4496E+10 bytes Fixed Size 9702624 bytes Variable Size 2147483648 bytes Database Buffers 1.2314E+10 bytes Redo Buffers 23851008 bytes Database mounted. Database opened. SQL>
Considerations
Some things to consider when using expressions.
- When issued from the
ALTER SYSTEM
orALTER SESSION
commands, the expression must be enclosed in single quotes. - When setting parameters we have access to the
MIN
andMAX
functions, both of which accept two values. TheMIN
function returns the lower of the two values, and so may be useful in defining a maximum value for a parameter. TheMAX
function returns the higher of the two values, and so may be useful in defining a minimum value for a parameter. These aren't to be confused with the SQL functions of the same name. - Environment variables must be set before instance startup, and their values are read at that point only.
- Expressions can be used in a spfile or a pfile.
For more information see:
- Using Expressions for Parameter Values
- Practice: Using Expressions in Initialization Parameters
- Using Expressions in Initialization Parameters in Oracle Database 21c
Hope this helps. Regards Tim...