This is very common on the web, but I will still want to have it here as well.
Sometimes hidden parameters are interesting, and we would like to see them and their value
The “show parameter” command in SQL*Plus shows us only the regular parameters and excludes the hidden ones. It is important to know, though, that if a hidden parameter was explicitly set in the database, the “show parameter” will show it. It will not show any hidden parameter with a default untouched value.
In order to get a list of all initialization parameters, including the hidden ones, use the following script, it will return the name of the parameter, its current value and its default value:
| select par.ksppinm name, | |
| val.ksppstvl value, | |
| val.ksppstdf def_val | |
| from x$ksppi par, | |
| x$ksppcv val | |
| where par.indx=val.indx | |
| order by 1; |
Remember, if you want to remove a parameter, remove it. Don’t set it to its default value. If you don’t know how to remove a parameter from the spfile look here.
1 thought on “See ALL Initialization Parameters”