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 table1@database.domain.subdomain.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?
Best Answer
I had exactly the same problem: migrated Oracle 11.2 database to new installation 12.1 via DataPump. Database links in then 12c only worked when accessed with fully qualified names, but the application had been coded using only their partial names. The problem was the domain of the new database's Global Name was different - see http://www.dba86.com/docs/oracle/12.1/ADMIN/ds_admin.htm#ADMIN029 (search for 'partial' within the dblinks section). I created additional dblinks with fully qualified names using new domain name, by extracting the ddl from the 11.2 database: SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a; - see excellent hints from Alex Lima https://oraclespin.com/2008/03/28/how-to-extract-the-db-links-ddl-with-the-password and Simon Krenger https://www.krenger.ch/blog/find-password-for-database-link/