sid and pluggable

I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant.

You create a container CDB01 and a pluggable DB01, you can connect with


sqlplus "scott/tiger@
(description=(address=
(host=srv01)(port=1521)(protocol=tcp))
(connect_data=(service_name=DB01.example.com)))"

But one of your application has hardcoded SID=DB01


sqlplus "scott/tiger@
(description=(address=
(host=srv01)(port=1521)(protocol=tcp))
(connect_data=(sid=DB01)))"
ORA-12514: TNS:listener does not currently know of
service requested in connect descriptor

How do you do the trick?

  • Remove the domain name in the pluggable

  • alter session set container=DB01;
    alter system set db_domain=''
    container=current
    scope=spfile;

  • You use the listener parameter USE_SID_AS_SERVICE
  • listener.ora

    USE_SID_AS_SERVICE_LISTENER = on

    You restart the listener and the database.

    sqlplus "scott/tiger@
    (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(sid=DB01)))"
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

    You just connected with SID to a pluggable database

1 Comment

Leave a Reply

Your email address will not be published.