Alternative to ALL_SYNONYMS view

                

The SYS.ALL_SYNONYMS view is used to get all the details of existing synonyms accessible to the current user.

However, SYS.ALL_SYNONYMS has some known performance issues — and at the time I'm writing this blog,  hey still haven’t been resolved. 

This becomes an issue if your database has a huge number of synonyms. (Yeah, sounds like a long shot, but believe me, speaking from experience — it can definitely happen.)

So, if your application has a PL/SQL block that's querying this view, you might want to consider updating your code to avoid referencing it altogether.

So the question is What's the alternative? I had the same question! And then a smart teammate of mine suggested using DBMS_UTILITY.NAME_RESOLVE. (s/o to Gerardo!)

What is DBMS_UTILITY.NAME_RESOLVE?

This is a built-in Oracle procedure that helps you resolve the components of a name in the database — things like the object name, schema, dblink, and type behind a reference.

So if you pass in something like a synonym name, it tells you what object it points to. Very handy when you want to bypass the heavier views like ALL_SYNONYMS.

Official doc: DBMS_UTILITY.NAME_RESOLVE DOCS 

Here’s a simple stored procedure that uses DBMS_UTILITY.NAME_RESOLVE to extract the owner and name of the object referenced by a synonym:

 CREATE OR REPLACE PROCEDURE get_object_from_synonym( 
     l_syn_name IN VARCHAR2,
     l_obj_owner  OUT VARCHAR2,
     l_obj_name OUT VARCHAR2
 )  

 IS

     l_part2 VARCHAR2(128));
     l_dblink VARCHAR2(128); 
     l_part1_type NUMBER ; 
     l_object_number NUMBER;

 BEGIN 
    

      DBMS_UTILITY.NAME_RESOLVE( 
         name => l_syn_name, 
         context => 0, 
         schema => l_obj_owner, 
         part1 => l_obj_name, 
         part2 => l_part2, 
         dblink => l_dblink, 
         part1_type => l_part1_type, 
         object_number => l_object_number
    );
    
 END get_object_from_synonym; 

 / 

And now let’s call the procedure and print the owner and name of the resolved object:

 

 DECLARE 

     l_object_owner VARCHAR2(128); 

     l_obj_name VARCHAR2(128);

     l_name VARCHAR2(20) := 'APEX_POOL_CONFIG'; 

 BEGIN  

    get_object(l_name, l_object_owner, l_obj_name);

    dbms_output.put_line(' object owner: ' || l_object_owner);

    dbms_output.put_line(' object name: ' || l_obj_name);

      

 END;

/ 

Quick Note: What’s a synonym?

A synonym is basically an alias for a database object — a table, a procedure, a function and so on…

You are now thinking that  synonyms are only about simplifying access to objects — But they're also super useful as a form of abstraction. You can think of a synonym as a stable/constant thing that’s less likely to change, even if the underlying object (its name, schema, etc.) does. That stability can make a big difference when you want to write resilient PL/SQL code.