Expressions in Initialization Parameters
Description:-
New in Oracle 21c is the ability to use an expression to derive the value of an initialization parameter.
About initialization parameters:-
The parameters are the backbone of the database that is responsible for running the database with full utilized I/O operations. The database parameters can be modified in the system or session level. Some of the parameters can be altered in memory level and do not require any database restart, and some are not which requires a database to restart to get reflect for the database instance.
- Oracle Database 21c introduced the ability to use expressions to set initialization parameters. These expressions can reference other parameters and environment variables.
Let’s start Demo:-
Demo:-1
SQL> show parameter sga
NAME TYPE VALUE
———————————— ———– ——————————
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 1856M
sga_min_size big integer 0
sga_target big integer 1856M
SQL> ALTER SYSTEM SET sga_target = ‘sga_max_size*80/100’;
System altered.
SQL> show parameter sga
NAME TYPE VALUE
———————————— ———– ——————————
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 1856M
sga_min_size big integer 0
sga_target big integer 1488M
Demo:-2
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
SQL> show parameter processes
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> ALTER SYSTEM SET job_queue_processes=’processes*10/100′ SCOPE=BOTH;
System altered.
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 40
log_archive_max_processes integer 4
processes integer 400
Demo:-3
Example:- Using functions job_queue_processes=’max(processes/10,10)’
SQL> show parameter processes
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 40
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 40
log_archive_max_processes integer 4
processes integer 400
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = ‘MIN(39, PROCESSES * .1)’ SCOPE=BOTH;
System altered.
SQL> show parameter processes
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 39
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 40
log_archive_max_processes integer 4
processes integer 400
Demo:-4
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 13896M
SQL> ALTER SYSTEM SET db_recovery_file_dest=’$HOME’ SCOPE=BOTH;
System altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string $HOME
db_recovery_file_dest_size big integer 13896M
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> !ls -ltr | more
total 0
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Videos
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Templates
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Public
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Pictures
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Music
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Downloads
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Documents
drwxr-xr-x. 3 oracle oinstall 75 Sep 3 07:49 Desktop
drwxr-x—. 3 oracle oinstall 23 Sep 4 21:53 ORADBWR
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0
[oracle@oracle21c ~]$ ls -lrt
total 0
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Videos
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Templates
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Public
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Pictures
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Music
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Downloads
drwxr-xr-x. 2 oracle oinstall 6 Aug 27 20:33 Documents
drwxr-xr-x. 3 oracle oinstall 75 Sep 3 07:49 Desktop
drwxr-x—. 3 oracle oinstall 23 Sep 4 21:53 ORADBWR
[oracle@oracle21c ~]$ cd ORADBWR
[oracle@oracle21c 2022_09_04]$ ls
o1_mf_1_5_kk9n8s3q_.arc o1_mf_1_6_kk9n8ty4_.arc
[oracle@oracle21c 2022_09_04]$ pwd
/home/oracle/ORADBWR/archivelog/2022_09_04
Change back to original location
SQL> alter system set db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’ scope=both;
System altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 13896M
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/oracledbwr
