Resolving Oracle Object Names and Synonym Chains

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy-version support. I hope you enjoy it and find it helpful.

The data dictionary holds a wealth of information about your objects, but sometimes seemingly easy questions are still difficult to answer with a simple query to one of the views. Somebody once asked me how to determine what object was pointed to by a synonym. That's easy, just query DBA_SYNONYMS. The question becomes more complicated though if the target object is itself a synonym. Then you need to use hierarchical queries (i.e. CONNECT BY or recursive WITH clauses.) Answering this question is made even more difficult by changes in the way synonyms are resolved if the target doesn't exist.

Prior to 10g this condition would produce an error. Starting in 10g you can sometimes use a public synonym of the same name to fill in for the missing object. To this was added a further requirement of displaying the object type of the final object in the synonym chain.

The function below is what I came up with to answer the question. By default it resolves synonyms by 10g rules. You can pass in any number less than 10 to change to the pre-10g method. If a synonym chain resolves to some object in a remote database that's as far as I search and identify the final object as REMOTE. I also keep track of the synonym chain as it's built, so if a synonym loop is created, it will be detected and identified.

Since the output is of VARCHAR2 type, there is an implicit upper limit of 32K on the output in pl/sql or 4K in sql; but I've never encountered a synonym chain that came anywhere close to those limits in a real system.

It was a seemingly easy question that produced an interesting answer that was fun to write.

The Code

CREATE OR REPLACE FUNCTION resolve_name( p_object IN VARCHAR2, p_delimiter IN VARCHAR2 DEFAULT ' ->', p_owner IN VARCHAR2 DEFAULT USER, p_version IN INTEGER DEFAULT 10) RETURN VARCHAR2IS -- Resolve_Name -- by Sean D. Stuber -- -- Given an object name, show how that name is resolved through -- a chain of private and/or public synonyms to the final object -- and show what type of object is resolved. -- If no object can be found then return NULL. -- If the resolution chain runs into a remote object the returned path -- stops at the link and the remote type is undefined -- If synonyms loop (causing ORA-01775 errors) -- the path to the loop will be returned with the loop identified TYPE object_table IS TABLE OF CHAR(1) INDEX BY VARCHAR2(200); v_objects object_table; FUNCTION recursive_resolve( p_object IN VARCHAR2, p_delimiter IN VARCHAR2, p_owner IN VARCHAR2, p_version IN INTEGER, p_first IN BOOLEAN ) RETURN VARCHAR2 IS v_owner dba_objects.owner%TYPE; v_object dba_objects.object_name%TYPE; v_object_type dba_objects.object_type%TYPE; v_link dba_synonyms.db_link%TYPE; v_temp VARCHAR2(32767); v_dummy INTEGER; v_schema_exists BOOLEAN; BEGIN -- Check to see if we're recursing into a loop IF v_objects.EXISTS('"' || p_owner || '"."' || p_object || '"') THEN v_temp := p_owner || '.' || p_object || ' (--SYNONYM LOOP--)'; ELSE v_objects('"' || p_owner || '"."' || p_object || '"') := NULL; BEGIN -- At what type of object are we looking? SELECT object_type INTO v_object_type FROM dba_objects WHERE object_name = p_object AND owner = p_owner AND object_type NOT IN ('PACKAGE BODY', 'TYPE BODY', 'TABLE PARTITION', 'TABLE SUBPARTITION'); v_temp := p_owner || '.' || p_object; IF v_object_type = 'SYNONYM' THEN -- If it's a synonym then find what the synonym points to -- and call this procedure again to resolve that object -- if the synonym points to a remote object, -- note it and end the search. SELECT table_owner, table_name, db_link INTO v_owner, v_object, v_link FROM dba_synonyms WHERE synonym_name = p_object AND owner = p_owner; v_temp := v_temp || p_delimiter || CASE WHEN v_link IS NULL THEN recursive_resolve( v_object, p_delimiter, v_owner, p_version, FALSE ) ELSE v_owner || '.' || v_object || '@' || v_link || ' (--REMOTE UNKNOWN--)' END; ELSE -- If this object is not a synonym then we've resolved -- to the end, so return the object with its type v_temp := v_temp || ' (' || v_object_type || ')'; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- For first attempt or version is 10 or higher -- when no object is found in given schema -- look for a public synonym of the same name -- Note, 10g after the first level we only do this -- if the synonym points to an existing schema IF (NOT p_first) AND p_version >= 10 THEN BEGIN SELECT 1 INTO v_dummy FROM dba_users WHERE username = p_owner; v_schema_exists := TRUE; EXCEPTION WHEN NO_DATA_FOUND THEN v_schema_exists := FALSE; END; END IF; IF p_first OR v_schema_exists THEN v_temp := recursive_resolve( p_object, p_delimiter, 'PUBLIC', p_version, FALSE ); ELSE -- In versions 9i or lower, if a synonym points to a -- nonexisting object then the search ends -- In 10g or higher if the synonym points to a schema -- that doesn't exist then the search ends v_temp := NULL; END IF; END; END IF; RETURN v_temp; END;BEGIN RETURN recursive_resolve( p_object, p_delimiter, p_owner, p_version, TRUE );END resolve_name;

And finally, here's an example of a tracing a synonym across a dblink. In this case "C" points to a remote table "CLIENTS" but the function doesn't traverse the remote dictionary so the type of the remote object is unknown and labeled so.

This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines