Unable to show the synonym

[aloon9999]

Enable to see the synonym in Database Navigator even it is available. It is confirm exist by selecting from the system table ALL_SYNONYMS. The synonyms is shown in Oracle SQL Developer.

[serge-rider]

Public synonyms are in Global metadata->Public Synonyms path (the same about public db links). This is not very obvious. Perhaps it is better to have PUBLIC schema but it is quite specific..

[aloon9999]

I have check the Global metadata but i do not see the synonym appear in the Global Metadata tree. i create a non-public synonym, i expect to see it under my schema but it does not show up too.

Capture1.jpg Not able to see under Global Metadata Capture2.jpg Not able to see under specific schema.

.

[serge-rider]

According to select results above your synonym reside in PUBLIC schema. It is no it particular schema. To see public synonyms check this:

[aloon9999]

I did not see the public synonym appear in TYPE tree either. May i know what i expect to see, is it the synonym name?

Secondly what about the synonym with specific schema and not public that i have defined, may i know where can i see it.

[serge-rider]

You can see all synonyms but PUBLIC in their schemas->Synonyms. And you can see PUBLIC synonyms in Global Metadata->Public synonyms (why TYPE?)

To read synonyms DBeaver uses this query:

SELECT s.*,O.OBJECT_TYPE 
FROM ALL_SYNONYMS S, ALL_OBJECTS O
WHERE S.OWNER=? AND O.OBJECT_TYPE NOT IN ('JAVA CLASS','PACKAGE BODY')
AND O.OWNER=S.TABLE_OWNER AND O.OBJECT_NAME=S.TABLE_NAME
ORDER BY S.SYNONYM_NAME

[aloon9999]

I have check and unfortunately it does not work. The query show there are 2 synonyms created.

However when i use query that provided above also no result return.

[aloon9999]

After i try with the provided query, i found something wrong with the query to get the synonym.

1. Firstly the query to get synonym should get the O.OBJECT_NAME=S.SYNONYM_NAME and not O.OBJECT_NAME=S.TABLE_NAME.
2. The object owner does not always same as table owner, hence condition O.OWNER=S.TABLE_OWNER is not valid too.
3. Last but not least, i cannot get the public synonym either, i believe the query to get the public synonym also need to modified.

[serge-rider]

Good point. I've just committed the fix. Please try this in EA version: https://dbeaver.jkiss.org/files/ea/

[p91paul]

Wait a minute. The original query was correct!!! I can see both public and private synonyms with 5.0.2 (non EA), even when they point to different schemas. @serge-rider with your commit you will get "SYNONYM" as object type for all synonym, because the modified join will get the synonym object from ALL_OBJECTS and NOT the object the synonym points to.

Possibly @aloon9999 has synonyms that point to objects he has no grants on, or that don't exist; this means he can see them in all synonyms, but not in all_objects.

I believe the correct query is:

SELECT s.*,O.OBJECT_TYPE  FROM ALL_SYNONYMS S LEFT JOIN ALL_OBJECTS O
ON O.OBJECT_TYPE NOT IN ('JAVA CLASS','PACKAGE BODY')
AND O.OWNER=S.TABLE_OWNER AND O.OBJECT_NAME=S.TABLE_NAME
WHERE S.OWNER = ?
ORDER BY S.SYNONYM_NAME

But it's way slower than the original (because of the LEFT JOIN, it gets a nasty execution plan when there are a lot of synonyms).

I found that this one is more complicated but fast enough

SELECT owner, synonym_name, max(table_owner), max(table_name), max(db_link), max(object_type) from (
SELECT s.*, NULL OBJECT_type FROM all_synonyms s
WHERE owner = ?
UNION all
SELECT s.*,O.OBJECT_TYPE  FROM ALL_SYNONYMS S, ALL_OBJECTS O
WHERE S.OWNER = ?
AND O.OBJECT_TYPE NOT IN ('JAVA CLASS','PACKAGE BODY')
AND O.OWNER=S.TABLE_OWNER AND O.OBJECT_NAME=S.TABLE_NAME
)
GROUP BY owner, synonym_name
ORDER BY SYNONYM_NAME;

[serge-rider]

@p91paul Yep, also a good point. I forgot about permissions. But I don't understand this outer join. Synonym doesn't make sense if we can't get it's target object (which is resolved by OBJECT_TYPE).

[aloon9999]

@p91paul, may i know what about the permission that you refer to. I can select from the the synonym, i can also see the synonym in Oracle SQLDeveloper as mention in my first post. Can you elaborate more what else can i investigate. By the way, the synonym that i create is from another oracle database that exist in different host, just to clarify this point.

[p91paul]

@serge-rider Oracle allows creation of synonyms to non existent objects. They may be invalid or make no sense to exist, but they are in database and they should be shown.

@aloon9999 case is quite different, because the object exists in another database reached through a dblink. To get the object type you should query the remote all_objects view using the dblink (i don't even know if that's feasible); however the outer join would serve at least the purpose of showing them in the synonyms list.

[aloon9999]

@serge-rider, may i confirm if you going to revert the fix? If you revert then i not going to see the synonym.

I try the newly propose SQL by @p91paul, it work for my case and i can see the result for both public and schema Synonym.

---

SELECT owner, synonym_name, max(table_owner), max(table_name), max(db_link), max(object_type) from ( SELECT s., NULL OBJECT_type FROM all_synonyms s UNION all SELECT s.,O.OBJECT_TYPE FROM ALL_SYNONYMS S, ALL_OBJECTS O WHERE O.OBJECT_TYPE NOT IN ('JAVA CLASS','PACKAGE BODY') AND O.OWNER=S.TABLE_OWNER AND O.OBJECT_NAME=S.TABLE_NAME ) WHERE synonym_name LIKE 'ERP%' GROUP BY owner, synonym_name ORDER BY SYNONYM_NAME;

---

[serge-rider]

@aloon9999 Agreed about broken synonyms. We must show them.

But I still have some concerns about performance. Anyhow, let's try this is new EA version (the same location).

[serge-rider]

I assume it works as expected now