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... Continue Reading →


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... Continue Reading →

How to make trace files visible to all users?

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... Continue Reading →

Internal parameters

-- view oracle internal parameters, note this can be run by SYS user -- name, value, default value, session modifiable, system modifiable, description select a.ksppinm name, b.ksppstvl value, b.ksppstdf deflt, decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD, decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD, a.ksppdesc description from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm like '\_%' escape '\' order by... Continue Reading →

Create a free website or blog at

Up ↑