Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 28 October 2012

How to add a comment while changing a parameter dynamically/statically?

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#28-may-2012 Changed by shahid as per need
Static Changing:
Create a Pfile from spfile
Edit the paramter value (open_cursors=400#28-may-2012 Changed by shahid as per need) into the pfile
then open the database with this pfile and finally create spfile from that pfile.

1 comments: