6 Replies Latest reply: Nov 30, 2012 1:37 PM by jgarry RSS

    Need suggestions about synonyms

    852974
      We are using Oracle 10g (10.2.0.5).

      I have few doubts about synonyms. Please find the below..

      1.What is schema based synonym switching?
      2. How it will work and to understand explain with the example...
      3. what are advantages and disadvantages of schema based synonym switching?
      4. Can we implement it in Oracle 10g? or It is new in Oracle 11g?


      Thanks in advance..
        • 1. Re: Need suggestions about synonyms
          JustinCave
          A Google search on the phrase "schema based synonym switching" brings up only this thread so it does not appear that this is a term that anyone else has ever used. Can you explain with an example of what you're talking about or a link to whatever you read that used this term?

          Justin
          • 2. Re: Need suggestions about synonyms
            rp0428
            >
            We are using Oracle 10g (10.2.0.5).

            I have few doubts about synonyms. Please find the below..

            1.What is schema based synonym switching?
            2. How it will work and to understand explain with the example...
            3. what are advantages and disadvantages of schema based synonym switching?
            4. Can we implement it in Oracle 10g? or It is new in Oracle 11g?
            >
            I haven't heard that specific term used but public synonyms can be used to get data from different sources in different environments (prod, qa, test, dev).

            On a DEV server there may be a public synonym DEPARTMENT that points to an applications department table where the actual table may be the DEPT table in the SCOTT schema.

            In the TEST server the public synonym DEPARTMENT may point to a table with a different name in a totally different schema.

            Not sure if that kind of 'repointing' of a synonym is what you mean or not.
            • 3. Re: Need suggestions about synonyms
              jgarry
              You don't want to use synonyms, as they are a maintenance nightmare (you need too many, the objects may not exist and so on) and have negative performance implications. Much better to use a login trigger that checks the sys_context for the login, each of which has proper roles granted, and then does a set current_schema to the other schema where the data actually is.

              From whence does this question arise?
              • 4. Re: Need suggestions about synonyms
                rp0428
                >
                You don't want to use synonyms, as they are a maintenance nightmare (you need too many, the objects may not exist and so on) and have negative performance implications.
                >
                I've never been involved in any issues where synonyms were responsible for any performance issues. Can you provide an example of that?

                Sure there is maintenance involved as this is with any database object. Need too many? That's like saying don't use tables or indexes because you will need too many. How many you need is entirely dependent on how you use them.

                Yes, the underlying object may not exist. This is perfectly valid even though I have no object named 'abc' in the scott schema
                create synonym foobar for abc
                Just saying the synonym may not exist has no weight as an argument for me. You need to explain why that is a negative, if you believe it is, and why you think that negative (and any others you care to mention) outweighs the positives.

                Actually I'm more surprised you didn't highlight what I think is one of the biggest 'gotchas' developers may need to be aware of. That is how the improper maintenance of synonyms in various environments can wasted time and resources of the technical team chasing down problems that wouldn't otherwise exist if the synonyms were maintained properly. Namely a missing synonym or one pointing to the wrong (or wrong version of an) object. Sometimes the first check you need to make is whether your query SELECT * FROM EMP is really querying what you think it is.
                • 5. Re: Need suggestions about synonyms
                  Vedant..
                  1.What is schema based synonym switching?
                  Check whether this link solves your purpose
                  link : http://docs.oracle.com/cd/B28359_01/server.111/b28310/general009.htm

                  Edited by: Vedant.. on Nov 30, 2012 10:21 AM
                  • 6. Re: Need suggestions about synonyms
                    jgarry
                    You can take an assertion with no proof however you want, including that I may be talking out of my, um, hat. Certainly in the O7/8 timeframe it was a legitimate issue, poke around on ixora for details on that. Looking at my current production system, I see 20K public synonyms owned by Oracle schemata, and almost nothing else. So I can't really give an example, since I've stopped using them decades ago. It could be one of those "no longer true" things, I wouldn't know. But I sometimes wonder with some of the odd performance posts. Bug 9322994 shows an example of slow invalidation in 10gR2, and explains how finer grained dependencies in 11 fixes that. So you can see, some people have millions of synonyms, and find some pain.

                    You're right about the real-world gotcha. Add the bug about getting data from the wrong schema and you could go crazy.