Skip to Main Content
  • Questions

  • PDBs do not show up in all_services view

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: March 15, 2022 - 2:19 pm UTC

Last updated: March 18, 2022 - 5:25 am UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

Good Morning,

I noticed that when I create a PDB, Oracle doesn't create it a service for it; however, I am still able to log in using the following tns alias with SERVICE_NAME=<new PDB>:

[oracle@localhost bin]$ tnsping orcl

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-MAR-2022 09:12:12

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/version/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)


Here is the list of services:

09:15:30 SQL> select name from all_services;

NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
orclcdbXDB
orclcdb


09:10:40 SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orclcdb

Here are the PDBs:
09:10:43 SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
09:11:27 SQL>


Thank you

and Connor said...

There is a "hard coded" or "built in" service for every database ()pluggable or otherwise).

Because its built-in, the ideal configuration is *not* to use that service but create your own, so that you have more control over it. This comes into play especially for RAC where you might want to control service availability across nodes etc. But I acknowledge that most people just use the ones that come with each database.

Rating

  (2 ratings)

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

Comments

CDB_Services.

Rajeshwaran Jeyabal, March 21, 2022 - 5:15 am UTC

from a container standpoint, shouldn't we look at cdb_services dictionary to get the services across all the PDBs?

since all_services will display the services_name only from the current container.
sys@XE> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
         5 PDB1                           MOUNTED
sys@XE> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      XE
sys@XE> select name from all_services;

NAME
--------------------
SYS$BACKGROUND
SYS$USERS
xeXDB
xe

sys@XE> select name from cdb_services;

NAME
--------------------
SYS$BACKGROUND
SYS$USERS
xeXDB
xe
XEPDB1


CDB_Services.

Rajeshwaran Jeyabal, March 21, 2022 - 5:15 am UTC

from a container standpoint, shouldn't we look at cdb_services dictionary to get the services across all the PDBs?

since all_services will display the services_name only from the current container.
sys@XE> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
         5 PDB1                           MOUNTED
sys@XE> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      XE
sys@XE> select name from all_services;

NAME
--------------------
SYS$BACKGROUND
SYS$USERS
xeXDB
xe

sys@XE> select name from cdb_services;

NAME
--------------------
SYS$BACKGROUND
SYS$USERS
xeXDB
xe
XEPDB1


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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database