奇怪的ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

开发人员发了的问题，创建dblink后查询报错，你以为这要么是监听没起，要么就是服务名不对，其实不是。

直接通过conn xxx/xxx@jzshdev是可以连接的，正常没问题。这就排除了上述的可能原因。

怀疑开发是不是写错了？自己动手操作一下。
根据开发给的语句：
create public database link JZSHDEV connect to xxx identified BY “xxx” using ‘jzshdev’;

处理过程很简单，步骤如下：

SQL> conn xxx/xxx
Connected.
SQL> create public database link JZSHDEV connect to xxx identified BY "xxx" using 'jzshdev';

Database link created.

------dblink创建正常

SQL> select * from dual@jzshdev;
select * from dual@jzshdev
                   *
ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

----------此处查询确实报错了，奇怪。

SQL> drop public database link JZSHDEV;

Database link dropped.

----删掉public dblink，尝试创建普通私有dblink看看

SQL> create database link JZSHDEV connect to xxx identified BY "xxx" using 'jzshdev';
create database link JZSHDEV connect to xxx identified BY "xxx" using 'jzshdev'
                     *
ERROR at line 1:
ORA-02011: duplicate database link name
----竟然报了已存在，这个问题就很清楚了

SQL> select * from dual@jzshdev;
select * from dual@jzshdev
                   *
ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
----原先数据库用户下存在同名的普通dblink，而且这个dblink是有问题的，指向不对，所以报了错。查询的时候优先选择了普通dblink导致报错。

SQL> drop database link JZSHDEV;

Database link dropped.
----问了开发，这个dblink没用可删，既然普通dblink没用，那就删除，重新创建给public dblink就行了。

SQL> create public database link JZSHDEV connect to xxx identified BY "xxx" using 'jzshdev';

Database link created.

SQL> select * from dual@jzshdev;

D
-
X

----查询正常。

复制

总结提示：oracle的dblink分为public dblink和普通私有dblink的名字可以相同，默认优先使用普通私有dblink。

其它常见的错误原因参考：https://dbamarco.wordpress.com/2023/09/28/connecting-to-an-oracle-database-what-could-possible-go-wrong/