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…
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.
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…
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 make trace files visible to all users, set the parameter _TRACE_FILES_PUBLIC=TRUE, this is undocumented parameter. This parameter requires a restart of instance to take into effect. SQL> alter system set “_TRACE_FILES_PUBLIC”=true scope=spfile; System altered. Shows the trace file is accessible by all users, you may have change permissions on the user_dump_dest folder -rw-r–r– 1…
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 ——…
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.…
