patternMinor
Differing results from gv$session
Viewed 0 times
resultsfromdifferingsession
Problem
The following query returns no results, yet it seems like it should.
In my 11.2.0.2 Standard Edition RAC environment the above query returns no results, yet the following variations all return results.
Can anyone confirm this behavior and/or explain it?
SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');In my 11.2.0.2 Standard Edition RAC environment the above query returns no results, yet the following variations all return results.
SELECT sid FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');
SELECT * FROM gv$session where SID = 256; --Your SID will likely be different.
SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');Can anyone confirm this behavior and/or explain it?
Solution
I got following results
I cannot explain but following query in 4 node RAC gives interesting output.
QUERY_NUMBER = 1, no rows
QUERY_NUMBER = 2, 4 rows (4 node RAC)
QUERY_NUMBER = 3, 1 row
SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
-- 5276
SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');
-- No result
SELECT * FROM gv$session where SID =5276;
-- 4 results for 4 node RAC
-- 1 result matches my USERNAME,OSUSER, MACHINE and PROGRAM
-- 3 results are different from my USERNAME,OSUSER, MACHINE and PROGRAM
SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');
-- 1 resultI cannot explain but following query in 4 node RAC gives interesting output.
SELECT '1' AS QUERY_NUMBER,S.* FROM gv$session S where SID = SYS_CONTEXT('USERENV','SID')
UNION ALL
SELECT '2',S.* FROM gv$session S where SID = ( SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL)
UNION ALL
SELECT '3',-1,S.* FROM v$session S where SID = SYS_CONTEXT('USERENV','SID');QUERY_NUMBER = 1, no rows
QUERY_NUMBER = 2, 4 rows (4 node RAC)
QUERY_NUMBER = 3, 1 row
Code Snippets
SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
-- 5276
SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');
-- No result
SELECT * FROM gv$session where SID =5276;
-- 4 results for 4 node RAC
-- 1 result matches my USERNAME,OSUSER, MACHINE and PROGRAM
-- 3 results are different from my USERNAME,OSUSER, MACHINE and PROGRAM
SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');
-- 1 resultSELECT '1' AS QUERY_NUMBER,S.* FROM gv$session S where SID = SYS_CONTEXT('USERENV','SID')
UNION ALL
SELECT '2',S.* FROM gv$session S where SID = ( SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL)
UNION ALL
SELECT '3',-1,S.* FROM v$session S where SID = SYS_CONTEXT('USERENV','SID');Context
StackExchange Database Administrators Q#8782, answer score: 3
Revisions (0)
No revisions yet.