第1部分:dblink对应远程库对象表及其sql语句查询:
语句一: 查询dblink"BASSA"和"DLN"对应远程库中的表名称,以及涉及该表的sql语句数;
SELECT OBJECT_NODE, OBJECT_NAME, COUNT(DISTINCT SQL_ID) SQL_ID_COUNT
FROM DBA_HIST_SQL_PLAN
WHERE OBJECT_TYPE = 'REMOTE'
AND OBJECT_NODE IN ('BSSA', 'DLN')
GROUP BY OBJECT_NODE, OBJECT_NAME
ORDER BY OBJECT_NODE, OBJECT_NAME;
语句二:查询dblink"BASSA"和"DLN"对应远程库中的表名称,以及涉及该表的具体sql_id;
SELECT OBJECT_NODE, OBJECT_NAME, SQL_ID
FROM DBA_HIST_SQL_PLAN
WHERE OBJECT_TYPE = 'REMOTE'
AND OBJECT_NODE IN ('BSSA', 'DLN')
ORDER BY OBJECT_NODE, OBJECT_NAME
语句三:在以上基础上根据SQL_ID查出具体的sql_text;
SELECT SQL_ID, SQL_TEXT
FROM DBA_HIST_SQLTEXT
WHERE SQL_ID IN ('0vhwyrsupbc6c',
'ansq0wd87ga9v',
'8hrckpkmbwm2k',
'au666c331yr2c',
'3fdvykxh62sgc');
第2部分:找出调用DBLINK的SESSION信息:
怎么找出通过dblink访问的用户信息? 参考语句脚本如下:
SELECT /*+ ORDERED */
S.KSUSEMNM "O_HOSTNAME",
S.KSUSEPID "O_SPID", --操作dblink用户信息
G.K2GTITID_ORA "O_TXID",
S.INDX "S_SID",
S.KSUSESER "S_SERIAL#", --dblink session信息
DECODE(BITAND(KSUSEIDL, 11),
1,
'ACTIVE',
0,
DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'),
2,
'SNIPED',
3,
'SNIPED',
'KILLED') "S_STATUS",
S.KSUUDNAM "DBLINK_USER"
FROM SYS.X$K2GTE G, SYS.X$KTCXB T, SYS.X$KSUSE S
WHERE G.K2GTDXCB = T.KTCXBXBA
AND G.K2GTDSES = T.KTCXBSES
AND S.ADDR = G.K2GTDSES;
查询结果如下:
如果需要获取更加信息的信息,可以通过结合两端的v$session和v$process视图获得。