Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to determine if an initialization parameter is dynamic. This procedure is the same on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL>

3. Execute the following SQL statement: select NAME, ISSYS_MODIFIABLE from V$PARAMETER;

SQL> set pagesize 500
SQL> column name format a30
SQL> select NAME, ISSYS_MODIFIABLE from V$PARAMETER;
NAME ISSYS_MODIFIABLE
——————— ——————-
..
..
..
user_dump_dest IMMEDIATE
max_dump_file_size IMMEDIATE
audit_file_dest DEFERRED
LINUX_sched_noage FALSE
object_cache_optimal_size DEFERRED
object_cache_max_size_percent DEFERRED
session_max_open_files FALSE
..
..
..
SQL>

4. Note: In the example above we have the NAME of the initialization parameter and three values for ISSYS_MODIFIABLE column, we can use this to determine if the initialization parameter is modifiable or not.

IMMEDIATE – The parameter is dynamic and can take affect immediately no matter if you are using a SPFILE or INIT file. When using an SPFILE you can use the clause SCOPE in the following ways: BOTH = change will take affect immediately and on restart of database; MEMORY = change will only take affect in current instance; and SPFILE = change will only take affect on restart of database. When using an INIT file you can use the clause SCOPE in the following ways: MEMEORY = change will only take affect in current instance.

DEFERRED – The parameter is not dynamic and will not take affect immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

FALSE – The parameter is not dynamic and cannot be changed immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

This completes determining if an initialization parameter is dynamic.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com