Expression-Based Parameter Value Feature in Oracle 21c

In Oracle 21c, you can define the value of a parameter using environment variables or even by referencing other database parameters. This provides more flexibility and automation when configuring your database.

For example, suppose you want to allocate 50% of the SGA space to the buffer cache (as a minimum). You can easily do this using Oracle’s new feature:

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 3568M

SQL> alter system set db_cache_size=‘sga_target*50/100’;
System altered.


SQL> show parameter db_cache_size
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_cache_size                        big integer 1792M

As you can see, the parameter value is dynamically calculated based on another parameter (sga_target).

Below is another example that demonstrates how to use environment variables:

[oracle@oLinux7 ~]$ mkdir /oracle21c/DATADATA
[oracle@oLinux7 ~]$ export DATADATA=/oracle21c/DATADATA

Then, inside SQL*Plus:

SQL> alter system set db_create_file_dest=’$DATADATA‘;
System altered.

SQL> create tablespace tttt;  
Tablespace created.

SQL> select name from v$datafile where name like ‘%DATADATA%’;
NAME
——————————————————————————–

/oracle21c/DATADATA/DB21C/datafile/o1_mf_tttt_lh6domx8_.dbf

SQL> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string      $DATADATA

As shown above, Oracle successfully interprets the environment variable $DATADATA when setting or displaying the parameter value.