Choosing Oracle Synonyms
Got into a situation where in a schema I have a table, say an ACTION table, while I got a synonym called ACTION that refers to another table to another schema.
Now when I run the query
select * from ACTION
it will select records from the table, but not a synonym.
Is there anyway for me to choose from synonym AND table together?
Thanx
I don't think your synonym for ACTION is in the same schema as your ACTION table as this is not allowed in Oracle. Most likely your synonym for ACTION is in a different schema, perhaps it is PUBLIC synonym. If this is the case, you can use
select * from ACTION
union
select * from public.ACTION
source to share
Well, your base ACTION table should be renamed, let's say LOCAL_ACTION.
Suppose your ACTION syntax is on otheruser.LOCAL_ACTION table ...
Then you can override the ACTION synonym:
SELECT * from LOCAL_ACTION
UNION
SELECT * from otheruser.LOCAL_ACTION
Then, at the end, select * from ACTION, you will get a combined list of both tables.
source to share
Tables and private synonyms do share the same namespace, so this is a public synonym: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm#sthref723
Remember UNION is implicit, different from a result set. You need UNION ALL.
select * from ACTION
union all
select * from public.ACTION
source to share