I came across a very interesting situation today and I'm putting this question to the community in hopes that someone can explain why this happened. I haven't been able to find an explanation in Oracle's documentation.
We have a database link configured in our 12c instance, and assume the link is named
database.domain.subdomain.com. This link connects to a 10g instance.
In queries that leverage this link, we use the partial link name:
select * from table1@database (omitting .domain.*). This works fine.
A complete rebuild of the remote server and instance was necessary due to a hardware failure. When it was brought back up, we encountered a problem querying the table with the same query
select * from table1@database however using the full name instead of partial worked fine:
select * from email@example.com
Querying other tables using the partial name was fine as well
select * from table2@database
I discovered that there was a session open from before the rebuild. After I terminated this session,
select * from table1@database resumed working as expected.
My question is, why did was I unable to query that specific object using the partial dblink name?