ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (Oracle 11g XE)

事象

• ローカル開発環境用のXEインスタンスにTNS接続できない
  • ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
• ローカル接続(sqlplus / as sysdba)は問題なし

解決した方法

• listener.ora のSID_LISTに明示的にXEのSID_DESCを追記、リスナー再起動により解消
• 発生原因は不明

SID_LIST_LISTENER =
  (SID_LIST =
    ### ここから↓ ###
    (SID_DESC =
      (GLOBAL_DBNAME = XE)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (SID_NAME = XE)
    )
    ### ここまで↑ ###
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)