Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 12, 2017 - 3:02 am UTC

Last updated: March 14, 2017 - 3:44 am UTC

Version: 11g R2

Viewed 1000+ times

You Asked

when I used to login my account using net service name then ora-12514 error occurs and when i try to without net service name then i login successfully so what's a problem?

c:\>sqlplus sys@mycon as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 12 07:47:13 2017

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor



c:\>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 12 07:47:43 2017

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"
SQL>

and Connor said...

A service name means you are connecting via the listener. On that box, run "lsnrctl status xxx" (where 'xxx' is the listener name) to show what services the listener knows about.

For example:

$ lsnrctl status LISTENER122

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-MAR-2017 23:49:58

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER122
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                10-MAR-2017 21:43:58
Uptime                    1 days 2 hr. 6 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2/db_2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/vbgeneric/listener122/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbgeneric)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "db122" has 1 instance(s).
  Instance "db122", status READY, has 1 handler(s) for this service...
Service "db122XDB" has 1 instance(s).
  Instance "db122", status READY, has 1 handler(s) for this service...
The command completed successfully


This tells me I have a service "db122" and "db122XDB" that my listener knows about.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 13, 2017 - 1:49 pm UTC


suppose i written as like "c:\>sqlplus sys as sysdba" is no need listener?
Connor McDonald
March 14, 2017 - 3:44 am UTC

if you are on the same server as the db, then the listener will not be needed
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.