New in Oracle 21c is the ability to use an expression to derive the value of an initialization parameter.

Oracle 21c allows us to base the value of a particular parameter which is derived from the existing value of some other parameter – or even based on the value of an environment variable.

So now the Oracle 21c database is able to automatically adjust the value of a parameter in response to environmental changes – or even when some other parameter value changes.

An example is to have the Shared Pool size always based on what the value of the SGA is. So regardless of what the SGA size is, the Shared Pool will always be 20% of that value.

###############################################################################
Configure parameter shared_pool_size to be 20% of SGA 
###############################################################################

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 3G

SQL> alter system set shared_pool_size='sga_target*20/100' scope=both;

System altered.

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 624M




###############################################################################
Configure parameter job_queue_processes to be the higher value of 50 
or 20% of the value of parameter processes 
###############################################################################


SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     200

SQL>  alter system set job_queue_processes='max(50,processes*20/100)' scope=both;

System altered.

SQL>  show parameter job_queu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     50


SQL> alter system set processes=300 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 3221225216 bytes
Fixed Size                  9690880 bytes
Variable Size            1929379840 bytes
Database Buffers         1258291200 bytes
Redo Buffers               23863296 bytes
Database mounted.
Database opened.

SQL>  show parameter job_queu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     60


###############################################################################
Configure parameter diagnostic_dest to be based on environment variable DIAG
###############################################################################

[oracle@db21c homes]$ cd /home/oracle

[oracle@db21c ~]$ mkdir diag

[oracle@db21c ~]$ export DIAG=/home/oracle

SQL>  alter system set diagnostic_dest='$DIAG' scope=both;

System altered.

SQL> show parameter diagnostic

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      $DIAG

SQL> alter system switch logfile;

System altered.


[oracle@db21c ~]$ cd $DIAG

[oracle@db21c db21c_syd1tp]$ pwd
/home/oracle

[oracle@db21c diag]$ ls
diag

[oracle@db21c diag]$ cd diag/

[oracle@db21c diag]$ ls
rdbms

[oracle@db21c diag]$ cd rdbms/db21c_syd1tp/

[oracle@db21c db21c_syd1tp]$ ls
db21c  i_1.mif

Last Update: January 17, 2021  

January 17, 2021 833 Gavin Soorma
Total 1 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?