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.

1. 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>

2. 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>

3. Considerations

Some things to consider when using expressions.

◉ When issued from the ALTER SYSTEM or ALTER SESSION commands, the expression must be enclosed in single quotes.

◉ When setting parameters we have access to the MIN and MAX functions, both of which accept two values. The MIN function returns the lower of the two values, and so may be useful in defining a maximum value for a parameter. The MAX 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.

Source: oracle-base.com