Not long ago somebody here asked how to query a x$ table (sometimes called fixed table) in a remote instance in RAC. I didn't think of a solution. Now when I think of it, it's actually simple using a DB link. Here's my test in 10.2.0.4 2-node RAC, with instances oracd11 and oracd12. Let's say we want to view both instances' x$ksuxsinst (the base table under gv$instance).
On node 1 where instance 1 is running, create a TNS entry under $ORACLE_HOME/network/admin:
ORACD12 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
(CONNECT_DATA =
(SID = oracd12)
)
)
In instance 1:
create view junk_view as select * from x$ksuxsinst;
grant select on junk_view to yhuang;
create database link oracd12 connect to yhuang identified by password using 'oracd12';
SQL> select inst_id, ksuxsscn from sys.junk_view@oracd12;
INST_ID KSUXSSCN
---------- ----------------
2 30811551902select inst_id, ksuxsscn from sys.junk_view;
INST_ID KSUXSSCN
---------- ----------------
1 30811551921 <-- this comes from the local table
I query the column KSUXSSCN just to show that this is something from the x$ table, which v$instance does not provide.
In fact, this is not limited to instances within a RAC. You can query the remote x$ table through this DB link from one database to another, RAC or not.
Yong Huang |
