In "ALTER SYSTEM" one can include a comment which gets recorded in the spfile and also in v$parameter as shown below. It can be used to record why parameter was changed. Example: SQL> alter system set open_cursors=1000 comment='07-Jan-2012 Changed AJ needed for application XYZ'; SQL> select value, update_comment from v$parameter where name = 'open_cursors'; VALUE … Continue reading How to add a comment when changing a parameter?
Tag: parameter
How to loop through parameters in batch file?
The following example shows how to loop through parameters in batch file. c:> TYPE script.bat @ECHO OFF :TOP IF (%1) == () GOTO END ECHO %1 SHIFT GOTO TOP :END ECHO End Sample Ouptut: C:\>.\script.bat aaa bbb aaa bbb End
How to delete/reset a parameter in spfile using ALTER SYSTEM?
Using ALTER SYSTEM RESET one can delete parameter from spfile if the parameter is in the spfile. Example: -- shows the parameter is in the spfile $ pwd /u01/oracle/product/10.2.0/db_1/dbs $ strings spfileTEST.ora | grep open *.open_cursors=100 -- Login to SQL*Plus, scope has to spfile and sid has to be supplied. When '*' is specified it … Continue reading How to delete/reset a parameter in spfile using ALTER SYSTEM?
How to tell if the database was started with spfile or pfile?
There are couple of ways of finding if the database was started with spfile or pfile. One way would be tell show the check the value of parameter spfile, if it returns blank then database was started by pfile. -- belows show database was started by spfile SQL> show parameter spfile; NAME TYPE VALUE ------ … Continue reading How to tell if the database was started with spfile or pfile?
How to identify parameters that has been modified since instance started?
To identify the parameter one can query v$parameter view to find the parameters that were modified. The column ISMODIFIED is FALSE by default when the instance starts up, when the value is changed it is SYSTEM_MOD indicating the value is changed at the SYSTEM level and if it's MODIFIED then it's changed at session level. … Continue reading How to identify parameters that has been modified since instance started?
alexlima.com 