Home » Server Options » RAC & Failsafe » Listing hidden parameters for all RAC instances (Oracle 10.2.0.4.0 EE - Linux RH EE)
Listing hidden parameters for all RAC instances [message #484408] |
Tue, 30 November 2010 06:46 |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Hi, guys.
I used following statement (user SYS as SYSDBA)
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),
decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),
decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash
from SYS.x$ksppi x, SYS.x$ksppcv y
where x.indx = y.indx
to list hidden parameters. However, when using it on RAC I found that only a singe instance data is displayed. Anybody aware of any limitation/problem disabling the possibiliti to compare
hidden parameters for RAC?
Michael
|
|
|
|
Re: Listing hidden parameters for all RAC instances [message #484424 is a reply to message #484420] |
Tue, 30 November 2010 08:44 |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Michel, thank you for reply.
I'll make another try tomorrow, however I took the query definition from GV$PARAMETER view, which lists regular parameters from all instances and it references x$ksppcv table.
I tried selecting the data from each referenced table and even a single table select displays data from one instance only (the one my session is connected to).
Michael
[Updated on: Tue, 30 November 2010 08:47] Report message to a moderator
|
|
|
|
|
|
Re: Listing hidden parameters for all RAC instances [message #484485 is a reply to message #484408] |
Wed, 01 December 2010 00:55 |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Both views (GV$PARAMETER and GV$SYSTEM_PARAMETER are referencing the same tables X$KSPPI and X$KSPPSV) and the same problem remains:
1. Signed to 3 mode RAC as SYS as SYSDBA
2. Made:
SELECT * FROM V$FIXED_VIEW_DEFINITION
WHERE VIEW_NAME = 'GV$SYSTEM_PARAMETER'
3. Got following text from VIEW_DEFINITION column:
select x.inst_id, x.indx + 1, ksppinm, ksppity, ksppstvl, ksppstdvl, ksppstdf,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
decode(bitand(ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE'),
decode(bitand(ksppiflg, 4), 4, 'FALSE', decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),
decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 'FALSE'),
decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),
decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),
ksppdesc, ksppstcmnt, ksppihash
from x$ksppi x, x$ksppsv y
where (x.indx = y.indx)
and ((translate(ksppinm, '_', '#') not like '##%') and
((translate(ksppinm, '_', '#') not like '#%') or
(ksppstdf = 'FALSE') or (bitand(ksppstvf, 5) > 0)))
4. Now, when I execute:
SELECT * FROM GV$SYSTEM_PARAMETER
I receive 807 rows (data from all 3 instances), however, when I run (again as SYS user) the statement I got in par. #3 - I get 269 rows only (from an instance I connected to).
It does NOT matter if I use GV$PARAMETER of GV$SYSTEM_PARAMETER views. When retrieving data through GV$xxx views - everything works fine, but when selecting directly from X$ tables - only single instance data is displayed.
So the question WHY remains open.
Michael
|
|
|
Re: Listing hidden parameters for all RAC instances [message #484490 is a reply to message #484485] |
Wed, 01 December 2010 01:18 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Both views (GV$PARAMETER and GV$SYSTEM_PARAMETER are referencing the same tables X$KSPPI and X$KSPPSV) and the same problem remains:
Wrong! Just read your first query.
Quote:everything works fine, but when selecting directly from X$ tables - only single instance data is displayed.
Just because you are unable to read.
Regards
Michel
[Updated on: Wed, 01 December 2010 01:18] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 23:30:48 CDT 2024
|