How to add a comment when changing a parameter?

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 ——————————————————————————– UPDATE_COMMENT ——————————————————————————– 400 $ strings spfileTESTDB.ORA | grep open_cursors … Continue reading How to add a comment when changing a parameter?

How to find parameters that will take into effect for new sessions?

Using the following query one can find the list of parameters that will take info effect for new sessions if the value of the parameter is changed. SQL> SELECT name FROM v$parameter WHERE issys_modifiable = ‘DEFERRED’; The parameter has be changed using the deferred option: SQL> alter system set sort_area_size=65538 deferred; System altered. Continue reading How to find parameters that will take into effect for new sessions?

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 applies to all instances if it is a cluster SQL> … Continue reading How to delete/reset a parameter in spfile using ALTER SYSTEM?

Format for parameter LOG_ARCHIVE_FORMAT?

This parameter controls the format of the archive log file name.  This parameter can’t be changed on the fly therefore requires a restart of the instance.  This parameter can be changed with scope=spfile if spfile is used.   If the format defined in the parameter log_archive_format is invalid the database will startup but archiver will fail to archive logs which will cause database to hang and … Continue reading Format for parameter LOG_ARCHIVE_FORMAT?