1

We have a legacy code which connectes to Oracle DB using SQLPLUS command:

sqlplus "$USERNAME/$PASSWORD@//$HOST:$PORT/$SERVICE_NAME"

Values of variables USERNAME, PASSWORD, HOST, PORT and SERVICE_NAME are configured using external properties file. This code works when we have the DB with the service name. In our case DB has default serice:

SYS_CONTEXT('USERENV','SERVICE_NAME')                                                                                                                                                                                                                           
-------------------------------------
SYS$USERS

DB has only the SID:

SYS_CONTEXT('USERENV','SID')                                                                                                                                                                                                                                    
----------------------------
521

Is there any way to connect to Oracle DB using default service name? e.g.

sqlplus "$USERNAME/$PASSWORD@//$HOST:$PORT/SYS$USERS"

It fails with error: ERROR:

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

WE also tried with SID as:

sqlplus "$USERNAME/$PASSWORD@//$HOST:$PORT/521"

It fails with error:

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

There is no scope to change code of this legacy app or change DB to add service names. Please let me know if there is any to resolve this issue.

user613114
  • 2,731
  • 11
  • 47
  • 73
  • 2
    SID would be with colon: sqlplus "$USERNAME/$PASSWORD@//$HOST:$PORT:521" – Bjarte Brandt May 18 '20 at 14:01
  • 2
    SYS$USERS is a special service for internal use; it is the default service used when a "real" service name isn't specified for a connection. I'm not sure you can connect to it explicitly, as the network listener won't have registered it. You must use a service name or SID that has been registered with the listener to receive remote connections. – pmdba May 18 '20 at 14:46
  • 1
    In addition to @pmdba comments, realize that in a shell script, _every_ occurance of '$' is a tag indicating to substitute the value of the variable whose name follows the '$'. That's what you are using for $USERNAME, $PASSWORD, $HOST, $PORT. Now, consider what happens with "SYS$USERS" By the same token you don't want a $ in the password, nor do you want a '@' in the password. – EdStevens May 18 '20 at 18:19
  • The word "SID" has multiple meanings here. Your `SYS_CONTEXT` query is actually return the session identifier, not the system identifier. Try this instead: `SYS_CONTEXT('userenv', 'db_name')`. – Jon Heller May 19 '20 at 01:52
  • @JonHeller: Even with db_name, we get ORA-12514 error :( – user613114 May 19 '20 at 08:58
  • @EdStevens: Thats an excellent point. I agree, we need to handle this. – user613114 May 19 '20 at 08:59
  • @user613114 To connect to a sid instead of a service name you should also change the last "/" to a ":". – Jon Heller May 19 '20 at 22:34

1 Answers1

0

You are mis-using sys_context to determine the correct values for SERVICE_NAME or SID. This is not what SYS_CONTEXT is reporting. The "sid" here is your session_id, not the database's system id. I'm not sure exactly what SYS.USERS represents in this context, but it is certainly NOT a service name by which you connect to a database. You should look at v$parameter:

 select name, value from v$parameter where name='service_names';

Or better, just look at the output of 'lsnrctl status' to show you what the listener _does_ know.

I have never seen a listener serving SYS$USERS.

EdStevens
  • 3,708
  • 2
  • 10
  • 18