1

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
  • 2
    What's the reason for even wanting to do this? – Philᵀᴹ Sep 1 '16 at 13:55
  • @Phil: I'm trying to add some minimal Oracle-specific information to the documentation of my database-based solution. – Bass Sep 1 '16 at 15:14
  • 2
    Well, the "minimal" as you've already discovered, is that the db WILL broadcast at least one service name. I see no reason to even address that in your documentation, except (perhaps) to point out the fact that the database will broadcast at list one default service name. – EdStevens Sep 2 '16 at 13:45
1

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).

| improve this answer | |
1
sys@KAUSH05P_1> exec dbms_service.delete_service('SRVKAUSH05P_ROBERTO1');
BEGIN dbms_service.delete_service('SRVKAUSH05P_ROBERTO1'); END;

*
ERROR at line 1:
ORA-44305: service SRVKAUSH05P_ROBERTO1 is running
ORA-06512: at "SYS.DBMS_SERVICE", line 68
ORA-06512: at "SYS.DBMS_SERVICE", line 458
ORA-06512: at line 1


sys@KAUSH05P_1>  exec dbms_service.stop_service('SRVKAUSH05P_ROBERTO1');

PL/SQL procedure successfully completed.

sys@KAUSH05P_1> exec dbms_service.delete_service('SRVKAUSH05P_ROBERTO1');

PL/SQL procedure successfully completed.

sys@KAUSH05P_1> 

You need first stop service and the delete service

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.