8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.