4 Replies Latest reply on Oct 29, 2015 5:57 AM by thatJeffSmith-Oracle

    Navigator queries against DBA_* dictionary views

    HarbourGhost

      We've got an odd situation in one of our databases.

       

      We have a bunch of schemas that our team own, each with its own code in packages.

      There are other schemas that we don't own and aren't allowed to see into.

       

      Via a GRANT EXECUTE on code in our schemas, we can see procedures, functions and packages in ALL_SOURCE but not package bodies.

      Our friendly DBAs gave us a view over DBA_SOURCE so that we could see the code in the package bodies and so the schema has local synonyms for DBA_SOURCE and DBA_OBJECTS.

       

      What SQL Developer seems to do is check whether the schema can resolve the DBA_xxxx name, and then tries to select from SYS.DBA_xxxx and gets confused when the first works and the second doesn't.

      My preference would be that it checks for DBA_xxxx access and selects from DBA_xxxx. Checking for SYS.DBA_xxxx access and selecting from SYS.DBA_xxxx also makes sense.

      But one way or another, could you make it consistent please.

       

      At the moment, the navigator tree gives a "table or view does not exist" when trying to expand the "Tables" node

        • 1. Re: Navigator queries against DBA_* dictionary views
          thatJeffSmith-Oracle

          I think what your DBA did was nice, but not a good idea.

           

          Overloading the data dictionary object names seems questionable, at best.

          • 2. Re: Navigator queries against DBA_* dictionary views
            BPeaslandDBA

            Our friendly DBAs gave us a view over DBA_SOURCE so that we could see the code in the package bodies and so the schema has local synonyms for DBA_SOURCE and DBA_OBJECTS.

             

             

            IMO, it's always a bad idea to replace well known public synonym names with private synonyms of the same name. These type of things are bound to happen with this approach. This is not SQL Developer's fault.

             

            When I need to do something similar, I remove the "A" from the name. So it becomes DB_OBJECTS. This way, I don't have any potential for name collisions, but the object name is still just as meaningful. Maybe the DBA can do something similar for you.

             

            Cheers,
            Brian

            • 3. Re: Navigator queries against DBA_* dictionary views
              HarbourGhost

              As i said, I've got no issue if SQL Developer consistently wants to explicitly refer to SYS.DBA_* objects.It is the inconsistent referencing that is results in the error.

              We could rename the DBA_**** synonyms but so far it is only SQL Developer that has an issue, and some of the team use TOAD which works better with the synonyms.

              As an aside, we also have synonyms for some GV$/V$ views too, limiting to a subset of schemas. Oracle built-ins such as DBMS_SQL.DISPLAY_CURSOR need those exact names to function so we can't use alternatives there.

               

              [Personally, I don't care if it gets fixed. I've put up with it for several months, and only raised it because another team member asked me about the problem. The attitude in these forums suck, and I avoid coming here whenever possible. In future I won't bother raising issues.]

              • 4. Re: Navigator queries against DBA_* dictionary views
                thatJeffSmith-Oracle

                There's not really a bug here, so there's nothing to fix. If you want to report a bug, please open an SR with MOS.