Synonym Translation in Cursor

A user ran a script with many create or replace synonym statements to redirect the synonyms to new objects (or objects in a new schema), at the same time the application is being used. He would like to know if the synonyms used by the app start to point to the new objects. Indirectly, we can check v$sql.object_status, and compare last_load_time with first_load_time to infer. But there's direct evidence to see the synonym translation.

First, find the SQL child cursor address with select child_address from v$sql where ..., where the where-clause can be sql_id=... or sql_text like .... Suppose the address you find is 0000000955254390.

SQL> select sql_text from v$sql where child_address = '0000000955254390';

SQL_TEXT
--------------------------------------------------------------------------
select a.PNID , a.DESCDISPLAY    (m mmw_drug_name a where  a.pnid in
...

Then you check the synonym translation or mapping in x$kgltr:

SQL> col KGLOBTYD for a10
SQL> col KGLNAOWN for a10
SQL> col KGLFNOBJ for a30
SQL> select * from x$kgltr where KGLHDADR = '0000000955254390';

ADDR                   INDX    INST_ID KGLHDADR         KGLTRORG         KGLTRFNL
---------------- ---------- ---------- ---------------- ---------------- ----------------
00007FEFB1CEEF78        349        6 0000000955254390 00000009369477C8 000000095082E908
00007FEFB1CEEFB0        350        6 0000000955254390 0000000936949398 00000009496F7FC0
00007FEFB1CEEFE8        351        6 0000000955254390 000000094F5F12B0 00000009363BAE18

Take the last child cursor as an example. We'll see how the synonym goes from the original to the final library cache object in translation.

SQL> select KGLOBTYD, KGLNAOWN, KGLFNOBJ from x$kglob where kglhdadr = '000000094F5F12B0';

KGLOBTYD   KGLNAOWN   KGLFNOBJ
---------- ---------- ------------------------------
SYNONYM    DIB2_USER  MMW_DRUG_NAME

SQL> select KGLOBTYD, KGLNAOWN, KGLFNOBJ from x$kglob where kglhdadr = '00000009363BAE18';

KGLOBTYD   KGLNAOWN   KGLFNOBJ
---------- ---------- ------------------------------
TABLE      DIB2_OWNER MMW_DRUG_NAME

So in this child cursor, the synonym DIB2_USER.MMW_DRUG_NAME is mapped to table DIB2_OWNER.MMW_DRUG_NAME.

2015-08

To my OraNotes Page