In “ALTER SYSTEM” we can include a comment which gets recorded in the spfile and also in v$parameter as shown below. It can be used to record while parameter was changed. As we know the scope clause, allows the DBA to indicate how Oracle should interpret the persistence of the change.
Spfile – The parameter being changed will be changed in the spfile but will not take effect until the next time the instance is cycled.
Memory – The parameter being changed will be changed only for the currently active instance. The parameter in the spfile will not be updated. Therefore, the parameter is not persistent. This cannot be used for static parameters.
Both – The parameter being change will be changed in the current instance and, it will be updated in the spfile for the database. The change will be persistent through future cycles of the database instance.
Dynamic changing:
ALTER SYSTEM SET shared_pool_size=100m COMMENT=’Change by shahid 6/27′ SCOPE=BOTH, aq_tm_processes=5 COMMENT=’Change by shahid 6/27′ SCOPE=BOTH
In the above example two parameter setting changes has also been associated with a comment. The scope of the change is BOTH thus the change will take place immediately and also be reflected in the spfile of the database.
ALTER SYSTEM SET query_rewrite_enabled=TRUE COMMENT=’Change by shahid 6/27′ SCOPE=MEMORY DEFERRED SID=’orcl3′;
We know that the deferred parameter can be used to cause the change to take effect only for future sessions that connect to the database.
Note:You can use V$PARAMETER, V$PARAMETER2, AND V$SPPARAMETER view to query spfile information.
To check on Linux Machine:
$ strings spfileORCL3.ORA | grep open_cursors
*.open_cursors=400
Static Changing:
Create a Pfile from spfile
Edit the paramter value (open_cursors=400)
then open the database with this pfile and finally create spfile from that pfile.
