Who’s who in CDB_SERVICES, SERVICE$, and more?

I visited a customer recently, and they will use Refreshable Clone PDBs to move from Oracle Database 19c to Oracle Database 23ai. So far, nothing special. But after cloning there was an issue with the services creation with srvctl. We started querying the various views in the database, found some divergence but no good MOS note or documentation listing all the differences. Maybe this blog post will help a bit to shed some light.

Photo by Ansgar Scheffold on Unsplash

Why do you need to take care on services?

Daniel has described this very nicely the other week in his blog post. Once you relocate your database, for instance you are going to create a refreshable clone and migrate your non-CDB into a PDB, you must take care on the services as well. You are going to use either DBMS_SERVICE or srvctl add service commands depending on whether you work with a single instance or a RAC database.

• Blog post: Recreate Database Services after moving an Oracle Database (Jan 28, 2025)

But what if the service recreation fails? This was the case here. But at first, we wanted to drill deep into the various views.

Which views do contain information?

The list of views is not short, and I may miss one or the other potentially. So, don’t see this list as complete, please. At first, I tried to find a MOS note listing all of them after consulting the documentation. But none of the documents had it all in one place. This is when we started digging deeper. I link all findings in the Further Links and Information section at the end of this blog post.

DBA_SERVICES

This view lists all the started and stopped services in a database or PDB. It is a view based on SERVICE$ with the difference that it does not list any previously deleted services. When you look at its definition, you see the WHERE clause at the end: where DELETION_DATE is null;.

Using DBMS_METADATA.GET_DDL you can see its definition:

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_SERVICES" ("SERVICE_ID", "NAME", "NAME_HASH", "NETWORK_NAME", "CREATION_DATE", "CREATION_DATE_HASH", "FAILOVER_METHOD", "FAILOVER_TYPE", "FAILOVER_RETRIES", "FAILOVER_DELAY", "MIN_CARDINALITY", "MAX_CARDINALITY", "GOAL", "DTP", "ENABLED", "AQ_HA_NOTIFICATIONS", "CLB_GOAL", "EDITION", "COMMIT_OUTCOME", "RETENTION_TIMEOUT", "REPLAY_INITIATION_TIMEOUT", "SESSION_STATE_CONSISTENCY", "GLOBAL_SERVICE", "PDB", "SQL_TRANSLATION_PROFILE", "MAX_LAG_TIME", "GSM_FLAGS", "PQ_SVC", "STOP_OPTION", "FAILOVER_RESTORE", "DRAIN_TIMEOUT", "TABLE_FAMILY_ID", "PLACEMENT_POLICY", "RESET_STATE", "VCSPARE1", "NSPARE1") AS 
  select SERVICE_ID, NAME, NAME_HASH, NETWORK_NAME,
          CREATION_DATE, CREATION_DATE_HASH,
          ...
   from service$
where DELETION_DATE is null;    

This is taken from an 19.24.0 database.

SERVICE$

The difference between SERVICE$ and DBA_SERVICES is then the fact that SERVICE$ will also list previously deleted services. Let us check the definition in an 19.24 database:

  CREATE TABLE "SYS"."SERVICE$" SHARING=METADATA 
   (	"SERVICE_ID" NUMBER, 
	"NAME" VARCHAR2(64), 
	"NAME_HASH" NUMBER, 
	"NETWORK_NAME" VARCHAR2(512), 
	"CREATION_DATE" DATE, 
	"CREATION_DATE_HASH" NUMBER, 
	"DELETION_DATE" DATE, 
	"FAILOVER_METHOD" VARCHAR2(64), 
	"FAILOVER_TYPE" VARCHAR2(64), 
	"FAILOVER_RETRIES" NUMBER(10,0), 
	"FAILOVER_DELAY" NUMBER(10,0), 
	"MIN_CARDINALITY" NUMBER, 
	"MAX_CARDINALITY" NUMBER, 
	"GOAL" NUMBER, 
	"FLAGS" NUMBER, 
	"EDITION" VARCHAR2(128), 
	"PDB" VARCHAR2(128), 
	"RETENTION_TIMEOUT" NUMBER, 
	"REPLAY_INITIATION_TIMEOUT" NUMBER, 
	"SESSION_STATE_CONSISTENCY" VARCHAR2(128), 
	"SQL_TRANSLATION_PROFILE" VARCHAR2(261), 
	"MAX_LAG_TIME" VARCHAR2(128), 
	"GSM_FLAGS" NUMBER, 
	"PQ_SVC" VARCHAR2(64), 
	"STOP_OPTION" NUMBER, 
	"FAILOVER_RESTORE" NUMBER, 
	"DRAIN_TIMEOUT" NUMBER, 
	"TABLE_FAMILY_ID" NUMBER, 
	"PLACEMENT_POLICY" NUMBER, 
	"RESET_STATE" NUMBER, 
	"VCSPARE1" VARCHAR2(1024), 
	"NSPARE1" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;    

CDB_SERVICE$

This is an interesting one. Since it has the “dollar” sign in it, it is not part of the documentation. Such tables are considered dictionary objects which normally will be accessed via the USER_/ALL_/DBA_/CDB_-views. Let us have a look at its definition:

  CREATE TABLE "SYS"."CDB_SERVICE$" SHARING=METADATA 
   (	"NAME" VARCHAR2(64), 
	"NETWORK_NAME" VARCHAR2(512), 
	"CON_ID#" NUMBER, 
	"NAME_HASH" NUMBER, 
	"FLAGS" NUMBER, 
	"SPAREN1" NUMBER, 
	"SPAREN2" NUMBER, 
	"SPAREVC1" VARCHAR2(4000), 
	"SPAREVC2" VARCHAR2(4000)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;

This table got introduced in Oracle Database 12c already.

GV$SERVICES

This is a fixed view, and those can be queried from V_$FIXED_VIEW_DEFINITION. It gets its information from X$KSWSASTAB. More information can be found below.

X$KSWSASTAB

This is the table structure responsible for the active services cache.

These 4 views get sourced from it:

• gv$active_services
• gv$servicemetric
• gv$servicemetric_history
• gv$services

You can track all services since instance startup, regardless of whether they are present or have been stopped. The latter services will only disappear from the view once you restart the instance, and a given service remains stopped.

Quick summary

The sequence or order of views, synonyms and fixed tables is this:

gv_$services (view) => v_$services  (view) => V$SERVICES (fixed view) => GV$SERVICES (fixed view) => x$kswsastab

The part which is somehow a bit misleading is the fact that the synonyms in some cases have the same name as the views. But I hope the above list is helpful in case you need to drill down.

You may ask yourself whether we could solve the customer’s issue with the service error? The customer uses a workaround given by Oracle Support based on MOS Note: 2851365.1 – ORA-44303 & ORA-44775 errors when trying to open a cloned PDB where entries get deleted manually.

Further Links and Information

• Blog post: Recreate Database Services after moving an Oracle Database (Jan 28, 2025)
• MOS Note: 1496074.1 – Differences in outputs between DBA_SERVICES and V$SERVICES view
• MOS Note: 2639579.1 – Deleted Services Are Not Cleaned Up from X$KSWSASTAB, Causing Slowness In Querying GV$ACTIVE_SERVICES
• MOS Note: 2740588.1 – autoupgrade fails with Error:”ORA-65122: Pluggable database GUID conflicts with the GUID of an existing”
• MOS Note: 2851365.1 – ORA-44303 & ORA-44775 errors when trying to open a cloned PDB
• MOS Note: 1611804.1 – ORA-44303 and ORA-44775 Encountered While Creating a PDB
• MOS Note: 3039261.1 – Default CDB service not registering on listener after DB startup
• MOS Note: 2009500.1 – Service Management For Multitenant

–Mike