This content has been marked as final. Show 8 replies
Srini Chavali wrote:Sorry, didn't think that the actual individual synonym mattered...as I'm looking for a generic methodology to finding these kind of issues...
How does the user find the looping synonym ? What should the correct synonym point to ? After it is modified, what does the synonym point to ?
Ok, the user is finding it while using the forms. When it tries to query the synonym, it throws the error to screen.
The tables were recently copied from one schema to another. Synonyms were also generated on the new tables in the new location.
Since then, this keeps happening.
I've checked my code, and I don't see any reference in it to the old schema what so ever.
old synonym fs_nris_informs.nri_frcc_order_ref
tables names identical to the synonym name...
But the changes were made originally, and everything was fine. No further changes to that code have been made. Yet, twice now in two weeks this error has come up.
I go in and the synonym is either pointing to the wrong schema or it's removed.
Hope that answers what you are asking.
Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production
PL/SQL Release 126.96.36.199.0 - Production
CORE 188.8.131.52.0 Production
TNS for Solaris: Version 184.108.40.206.0 - Production
NLSRTL Version 220.127.116.11.0 - Production
I am having issues with a synonym. A user keeps finding a looped synonym. I fix it, and then days later, it's broken again.
I've tried to query dba_source on the text field to see if I can catch it...but I'm not finding it.
Are there any other ways to find if it's being altered manually, or by maybe another object?
Which user owns the synonym?
AUDIT to see who changes it
query ALL_OBJECTS to see when it gets changed
SQL> desc all_objects Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NOT NULL NUMBER EDITION_NAME VARCHAR2(30)
Have you bounced the db since you made the change? It sounds like some procedure uses the synonym, and has been pinned including the old value. This could survive an alter system flush shared_pool or recompiling packages. So some forms get a new one, but some get the old one (because they are calling old code still pinned). You might also want to check out bugs 10165083 and 13080778.
It doesn't show the changes.
select * from all_objects where OWNER = 'FS_NRIS_INFORMS' AND OBJECT_NAME LIKE '%ORDER_REF%'
The key there is that the last DDL was last month.
FS_NRIS_INFORMS,NRI_FRCC_ORDER_REF_T,,1944393,,TRIGGER,6/24/2007 9:38:12 AM,7/16/2007 5:18:10 PM,2007-07-16:17:18:10,VALID,N,N,N,3, FS_NRIS_INFORMS,NRI_FRCC_ORDER_REF_PK,,1944314,20811413,INDEX,6/24/2007 9:38:06 AM,8/27/2011 9:55:58 AM,2007-06-24:09:38:06,VALID,N,N,N,4, FS_NRIS_INFORMS,NRI_FRCC_ORDER_REF,,1944281,1944281,TABLE,6/24/2007 9:38:03 AM,8/27/2011 9:55:58 AM,2007-06-24:09:38:03,VALID,N,N,N,1,