Oracle – How to Delete All Services Associated with a SID

oracle

Is the situation when an Oracle instance is accessible via its SID, but not a single Service Name, technically feasible?

Is it possible to delete all Service Names associated with a given SID?

I have already set service_names of my XE instance to an empty string:

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      XE
db_unique_name                       string      XE
global_names                         boolean     FALSE
instance_name                        string      xe
lock_name_space                      string
log_file_name_convert                string
service_names                        string

Still, XE and XEXDB show up in the services list:

SQL> select name from sys.v$active_services;

NAME
----------------------------------------------------------------
XEXDB
XE
SYS$BACKGROUND
SYS$USERS

SQL> SELECT name, enabled FROM sys.dba_services;

NAME                                                             ENA
---------------------------------------------------------------- ---
SYS$BACKGROUND                                                   NO
SYS$USERS                                                        NO
XEXDB                                                            NO
XE                                                               NO

and an attempt to delete any of them fails:

SQL> exec dbms_service.delete_service('XE');
BEGIN dbms_service.delete_service('XE'); END;

*
ERROR at line 1:
ORA-44305: service XE is running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 454
ORA-06512: at "SYS.DBMS_SERVICE", line 343
ORA-06512: at line 1

Best Answer

SYS$BACKGROUND and SYS$USERS are built-in internal services, they are not exposed to the listener and clients.

XE is your default service, because an Oracle database by default has a service with its db_unique_name, and registers itself to a listener running on the default port (1521) with the db_unique_name as service name, and instance_name as instance.

The XEXDB service exists, because you have the XML database option configured, and the dispatchers parameter is set.

You do not need to modify service_names manually, the database handles that parameter based on the service configuration.

In my opinion, the use of services should be promoted, not prevented. Using multiple services for a single database has the benefit of being able to manage clients connecting through different services seperately, when troubleshooting (tracing), or prioritizing workload (resource manager).

Related Question