ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
一、问题原因
用Navicat Premium 连接 Oracle,提示:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
中文版的提示:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求服务
1、各软件的版本
Navicat Premium 版本:11.2.14(64-bit)
Oracle 版本:10.2.0.1.0(32-bit)
2、连接方式
其中,服务名或SID是默认的ORCL,点击连接测试,出现上诉的错误!
谷歌查询了一番,折腾了一天,都建议修改 listrner.ora 和 tnsnames.ora 。
这两个文件的路径:(我是安装在C盘)
C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN
listrner.ora源码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SID_LIST_LISTENER = 2 (SID_LIST = 3 (SID_DESC = 4 (SID_NAME = PLSExtProc) 5 (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server) 6 (PROGRAM = extproc) 7 ) 8 (SID_DESC = 9 (SID_NAME = CLRExtProc) 10 (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server) 11 (PROGRAM = extproc) 12 ) 13 ) 14 15 LISTENER = 16 (DESCRIPTION_LIST = 17 (DESCRIPTION = 18 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) 19 (ADDRESS = (PROTOCOL = TCP)(HOST = Darlin-PC)(PORT = 1521)) 20 ) 21 ) 22 23 DEFAULT_SERVICE_LISTENER = (XE)
tnsnames.ora源码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 XE = 2 (DESCRIPTION = 3 (ADDRESS = (PROTOCOL = TCP)(HOST = Darlin-PC)(PORT = 1521)) 4 (CONNECT_DATA = 5 (SERVER = DEDICATED) 6 (SERVICE_NAME = XE) 7 ) 8 ) 9 10 EXTPROC_CONNECTION_DATA = 11 (DESCRIPTION = 12 (ADDRESS_LIST = 13 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) 14 ) 15 (CONNECT_DATA = 16 (SID = PLSExtProc) 17 (PRESENTATION = RO) 18 ) 19 ) 20 21 ORACLR_CONNECTION_DATA = 22 (DESCRIPTION = 23 (ADDRESS_LIST = 24 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) 25 ) 26 (CONNECT_DATA = 27 (SID = CLRExtProc) 28 (PRESENTATION = RO) 29 ) 30 ) 31 TEST = 32 (DESCRIPTION = 33 (ADDRESS_LIST = 34 (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*)) 35 ) 36 (CONNECT_DATA = 37 (SERVER = DEDICATED) 38 (SERVICE_NAME = *<servicenamefromDB>*) 39 ) 40 )
比如说, 有建议在 listrner.ora 的第7行后添加以下代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 (SID_DESC = 2 (GLOBAL_DBNAME = ORCL) 3 (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server) 4 (SID_NAME = ORCL) 5 )
重启Oracle的服务,问题解决!
也有建议在 tnsnames.ora 文件中添加以下代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 TEST = 2 (DESCRIPTION = 3 (ADDRESS_LIST = 4 (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*)) 5 ) 6 (CONNECT_DATA = 7 (SERVER = DEDICATED) 8 (SERVICE_NAME = *<servicenamefromDB>*) 9 ) 10 )
重启Oracle的服务,问题解决!
然而,在我这里,问题依然没有解决,没有解决,没有解决!!!
二、解决方案
在我准备放弃的时候,看到 listrner.ora 最后一行有这么一句代码:
DEFAULT_SERVICE_LISTENER = (XE)
并且我的Oracle服务如下:
并没有OracleServiceORCL这一项啊,有的是OracleServiceXE 于是,我就用以下方式连接:
看到这个界面的时候,那个激动啊,折腾了一天了。终于连接成功了!!!
三、总结
文件 listrner.ora 中配置监听的服务名是XE, 并且有的是OracleServiceXE。
意思就是,我的服务器名字叫XE,不是默认的ORCL,所以才出现这样的错误!