Oracle

gv$session 的不同結果

  • December 7, 2011

以下查詢不返回任何結果,但它似乎應該返回。

SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');

在我的 11.2.0.2 標準版 RAC 環境中,上述查詢不返回任何結果,但以下變體均返回結果。

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');

任何人都可以確認這種行為和/或解釋它嗎?

我得到以下結果

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 result

我無法解釋,但 4 節點 RAC 中的以下查詢給出了有趣的輸出。

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,沒有行

QUERY_NUMBER = 2、4 行(4 節點 RAC)

QUERY_NUMBER = 3,1 行

引用自:https://dba.stackexchange.com/questions/8782