Oracle synonyms selection

Question

Hi,

Got into a situation where in a schema i have a table, say table ACTION , while i got a synonym called ACTION as well which refers to another table to another schema.

Now, when i run the query

select * from ACTION

it will select the records from the table, but not the synonym.

Anyway for me to select from the synonym AND the table both together?

Thanx

Answer 1

Well, your underlying ACTION table should be renamed, let's say do LOCAL_ACTION.

Let's pretend your ACTION synonym is on otheruser.LOCAL_ACTION table...

Then you can redefine the ACTION synonym to be:

SELECT * from LOCAL_ACTION
UNION
SELECT * from otheruser.LOCAL_ACTION

Then in the end, select * from ACTION will give you a combined list of both tables.

Answer 2

I don't think that your ACTION synonym resides in the same schema as your ACTION table, as that isn't allowed in Oracle. Most likely your ACTION synonym resides in other schema, perhaps it's a PUBLIC synonym. If that's the case you can use

select * from ACTION
union 
select * from public.ACTION

Answer 3

Tables and private synonyms do indeed share the same name space, so that is a public synonym: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm#sthref723

Be aware that UNION is an implicit distinct on the result set. Waht you need is UNION ALL.

select * from ACTION
union all
select * from public.ACTION