How to use and view comments to your parameter changes

Beginning with Oracle Database version 9.0.1, the ALTER SYSTEM command allowed you to add comments to your parameter changes.
These comments can be up to 255 characters long.

This feature came with the introduction of the spfile. In earlier versions, all parameter changes were made in text files, where you could embed comments with # prefixes on a line. While this is still supported, spfiles are the modern standard for maintaining parameters.

Setting a comment in the spfile would be with the spfile or both scope.

ALTER SYSTEM SET parameter_name = 'value' COMMENT='why are you doing this?' SCOPE=both;

ALTER SYSTEM SET parameter_name = 'value' COMMENT='why are you doing this?' SCOPE=spfile;

Note, each time you set a parameter value, the comments are cleared unless you set a new one. In the example below, I’ll change the cursor_sharing value and then put it back, showing the before and after comments value each time. The comments are visible in the UPDATE_COMMENTS column of the v$parameter and v$parameter2 views.

SQL> SELECT name, VALUE, update_comment FROM v$parameter  WHERE name = 'cursor_sharing';

             NAME    VALUE    UPDATE_COMMENT
_________________ ________ _________________
cursor_sharing    EXACT

SQL> ALTER SYSTEM SET cursor_sharing = 'FORCE' COMMENT = 'testing, put this back to EXACT' SCOPE = BOTH;

System SET altered.

SQL> SELECT name, VALUE, update_comment FROM v$parameter WHERE name = 'cursor_sharing';

             NAME    VALUE                     UPDATE_COMMENT
_________________ ________ __________________________________
cursor_sharing    FORCE    testing, put this back to EXACT

SQL> ALTER SYSTEM SET cursor_sharing = 'EXACT';

System SET altered.

SQL> SELECT name, VALUE, update_comment FROM v$parameter  WHERE name = 'cursor_sharing';

             NAME    VALUE    UPDATE_COMMENT
_________________ ________ _________________
cursor_sharing    EXACT

Also note, you can set values in memory using the memory or both scope. If you set a comment in memory, it will be kept in memory as well – thus both the value change and the comment will be lost when the instance is restarted.

SQL> SELECT name, VALUE, update_comment FROM v$parameter  WHERE name = 'cursor_sharing';

             NAME    VALUE    UPDATE_COMMENT
_________________ ________ _________________
cursor_sharing    EXACT

SQL> ALTER SYSTEM SET cursor_sharing = 'FORCE' COMMENT = 'testing, put this back to EXACT' SCOPE = memory;

System SET altered.

SQL> SELECT name, VALUE, update_comment FROM v$parameter WHERE name = 'cursor_sharing';

             NAME    VALUE                     UPDATE_COMMENT
_________________ ________ __________________________________
cursor_sharing    FORCE    testing, put this back to EXACT

SQL> ALTER SYSTEM SET cursor_sharing = 'EXACT';

System SET altered.

SQL> SELECT name, VALUE, update_comment FROM v$parameter  WHERE name = 'cursor_sharing';

             NAME    VALUE    UPDATE_COMMENT
_________________ ________ _________________
cursor_sharing    EXACT

Since parameter comments are only kept for the most recent change, they are not useful for maintaining a history of changes. If you wish to maintain a log of value changes and when/why they were changed, I recommend placing the parameter changes in your source code repository along with your application code. This will not only provide the history of changes, but also provide context as parameter changes will often coincide with an application change. Thus they can all be extracted, updated, and committed to your repository together.

Leave a ReplyCancel reply