8 Replies Latest reply: Dec 11, 2012 1:50 PM by User286067 RSS

    something/someone keeps changing a synonym...how can I track that down?

    643412
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for Solaris: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.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?
        • 1. Re: something/someone keeps changing a synonym...how can I track that down?
          Srini Chavali-Oracle
          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 ?

          HTH
          Srini
          • 2. Re: something/someone keeps changing a synonym...how can I track that down?
            643412
            Srini Chavali wrote:
            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 ?

            HTH
            Srini
            Sorry, didn't think that the actual individual synonym mattered...as I'm looking for a generic methodology to finding these kind of issues...

            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
            new:fs_nris_analyzer.nrsa_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.
            • 3. Re: something/someone keeps changing a synonym...how can I track that down?
              sb92075
              Willy_B wrote:
              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
              PL/SQL Release 11.2.0.3.0 - Production
              CORE     11.2.0.3.0     Production
              TNS for Solaris: Version 11.2.0.3.0 - Production
              NLSRTL Version 11.2.0.3.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?
              AUDIT

              Which user owns the synonym?
              • 4. Re: something/someone keeps changing a synonym...how can I track that down?
                643412
                each schema owns it, granted execute to public.

                The create statements go as such...

                CREATE or REPLACE PUBLIC SYNONYM NRI_FRCC_ORDER_REF FOR FS_NRIS_INFORMS.NRI_FRCC_ORDER_REF;

                CREATE or REPLACE PUBLIC SYNONYM NRSA_FRCC_ORDER_REF FOR FS_NRIS_ANALYZER.NRSA_FRCC_ORDER_REF;
                • 5. Re: something/someone keeps changing a synonym...how can I track that down?
                  sb92075
                  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)
                  • 6. Re: something/someone keeps changing a synonym...how can I track that down?
                    jgarry
                    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.
                    • 7. Re: something/someone keeps changing a synonym...how can I track that down?
                      643412
                      It doesn't show the changes.
                      I ran
                      select * from all_objects
                      where 
                      OWNER = 'FS_NRIS_INFORMS'
                       AND OBJECT_NAME LIKE '%ORDER_REF%'
                      it returned
                      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,
                      The key there is that the last DDL was last month.
                      • 8. Re: something/someone keeps changing a synonym...how can I track that down?
                        User286067
                        Shouldnt you be looking into dba_synonyms? also see synonyms in dba_objects with invalid status. Remove 'create any synonym' privileges from those who shouldn't have it. Then your field of suspects will trim down.

                        Raj