& TWO TYPES OF PARAMETER FILE
q PFILE
v $ORACLE_HOME/dbs/initPROD.ora
v STATIC FILE
v NOT IMMEDIATE [False]
v TEXT-BASED FILE
v IF CHANGES ANY PARAMETER, RESTART IS REQUIRE
q SPFILE
v $ORACLE_HOME/dbs/spfilePROD.ora
v DYNAMIC FILE
v IMMEDIATE [True]
v BINARY FILE
v BY DEFAULT, USE SPFILE ONLY
v IF CHANGES ANY PARAMETER, RESTART IS NOT REQUIRED
q SQL> desc v$parameter;
q SQL> select NAME, ISSYS_MODIFIABLE from v$parameter where
ISSYS_MODIFIABLE='FALSE | IMMEDIATE';
q SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
processes integer 300
q SQL> ALTER SYSTEM SET PARAMETER_NAME VALUE=XXXX
SCOPE=MEMORY | SPFILE | BOTH;
q MEMORY
v IT'S EFFECTIVE IMMEDIATELY BUT NOT VALID AFTER RESTART
v USE FOR DYNAMIC PARAMETER
q SPFILE
v IT'S EFFECTIVE AFTER RESTART
v USE FOR STATIC PARAMETER
q BOTH
v MEMORY AND SPFILE
v ITS EFFECTIVE IMMEDIATELY AND ALSO VALID AFTER RESTART
v USE FOR DYNAMIC PARAMETER
q SQL> alter system set undo_retention=1800 scope=both;
System altered.
& TOTAL PARAMETER - 445
& DYNAMIC PARAMETER - 315
& STATIC PARAMETER – 130