DBA Hub

📋Steps in this guide1/3

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.

oracle 21cconfigurationintermediate
by OracleDba
21 views
1

Referencing Parameters

We check the values of the and parameters. We see the values are 80 and 400 respectively. We set the parameter to 1/10 of the parameter. In this case we use the function, to make sure the parameter value never drops below 10. We check the parameter again, and we see it has been set to the correct value. We create a parameter file based on the current spfile. We check the setting of the parameter in the resulting pfile. So this has not only set the correct parameter value, but maintained the relationship to the parameter in the parameter definition. As a result, if we alter the parameter value, we will also be altering the parameter value.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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>

SQL> alter system set job_queue_processes='max(processes/10,10)';

System altered.

SQL>

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     40
SQL>

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.

SQL>

SQL> host fgrep job_queue_processes /tmp/pfile.txt
*.job_queue_processes=max(processes/10,10)

SQL>

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 environment variable. We check the current value of the parameter. We replace the path with one containing the environment variable. We create a parameter file based on the current spfile. We check the setting of the parameter in the resulting pfile. So now the audit location is based on the value of the 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 environment variable value. We shutdown the instance, and when we attempt to start it we get an error. We exit SQL*Plus and set the environment variable again. Now the instance starts as expected.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SQL> host echo $ORACLE_BASE
/u01/app/oracle

SQL>

SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/cdb1_lhr
                                                 12p/adump
SQL>

SQL> alter system set audit_file_dest='$ORACLE_BASE/admin/cdb1_lhr12p/adump' scope=spfile;

System altered.

SQL>

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.

SQL>

SQL> host fgrep audit_file_dest /tmp/pfile.txt
*.audit_file_dest='$ORACLE_BASE/admin/cdb1_lhr12p/adump'

SQL>

$ unset ORACLE_BASE
$ echo $ORACLE_BASE;

$

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-07217: sltln: environment variable cannot be evaluated.
SQL>

$ export ORACLE_BASE=/u01/app/oracle

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 or commands, the expression must be enclosed in single quotes. - When setting parameters we have access to the and functions, both of which accept two values. The function returns the lower of the two values, and so may be useful in defining a maximum value for a parameter. The 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: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!