select name from sys.V$PARAMETER where name like '\_%' escape '\' and ISDEFAULT='FALSE';
Actually during the migration, I used PFILE and commented out the hidden parameters exists on 11gR1. However someone created the SPFILE from MEMORY.
It seems creating of SPFILE from MEMORY caused this issue.
Edited by: Sivaprasad S on Jul 4, 2012 5:49 PM
I am aware that _underscore parameters are need to be set based on Oracle support request.
My question is " when I already commented the hidden paramenters in PFILE and started the instance with this PFILE, there is no hiidden parametes exists. However when i create a SPFILE from MEMORY, there are around 130 hidden parameters on the newly created SPFILE. I am wondering how does the MEMORY got the hidden parameters, when the instance is started with PFILE of no hidden parameters? "
Hope my query is clear now.
Then I can only surmise that something went wrong or was not done correctly during the upgrade. I have not seen this behavior ever before when performing upgrades. I would suggest you log an SR with Support
I just came across a similar case: 1 (just one) of our (RAC-) databases has 130-something underscore-parameters set to non-default values.
version is still 220.127.116.11 (e.g. not 11gR2).
All of those hidden parameters seem to be CBO related (list on request).
Weird is: Only one of our (very similar, supposedly same-config) databases have these hidden parameters set.
Colleagues justly wonder: Who, When, What has caused these parameters to be expliclty set for this one system?
Note: we do not have the complete history of this system documented (systems have been managed by 3 different parties already).
It is quite possible they came in with some patch or upgrade (catupgr?) somewhere between 18.104.22.168 and 22.214.171.124, but a confirmation would be nice.
Linux hostname 2.6.18-164.9.1.el5 #1 SMP Wed Dec 9 03:27:37 EST 2009 x86_64 x86_64 x86_64 GNU/Linux
Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production
PL/SQL Release 188.8.131.52.0 - Production
CORE 184.108.40.206.0 Production
TNS for Linux: Version 220.127.116.11.0 - Production
NLSRTL Version 18.104.22.168.0 - Production
5 rows selected.
Could you please help me. I need to know default values of hidden parameters. I am working on performance issue and I have checked that there many parameters having the isdefaut set to FALSE. Is thera any Oracle documentation showing default values?
I have found that on client database, that there are many hidden are without default values, isdefault=False.
The below query is very helpful