Search Results disconnect_session

Overview

SYS.DBMS_SERVICE is an Oracle-supplied PL/SQL package that allows an application or database administrator to manage database services and the sessions connected through a specific service name. In the Oracle EBS 12.1.1 and 12.2.2 environments, this package is invoked at the database tier rather than by the E-Business Suite application code directly, and it is most commonly encountered during patching, cloning, or troubleshooting activities relating to Oracle Real Application Clusters (RAC) and the Net Service / NetServiceDB configuration used by EBS. The package supports both RAC and single-instance databases, providing a programmatic method to create, delete, start, and stop service names across instances, and to force the disconnection of sessions associated with a given service.

The package is owned by SYS and is installed under the SYS schema using the @dbmssrv script. Execute privilege is restricted to the DBA role, which is consistent with its administrative nature. Because service management affects connectivity at the instance level, this package is frequently referenced when diagnosing the error message "netservicedb is not running," a condition that indicates the application-facing database service expected by an EBS instance is not available or registered.

Key Procedures and Functions

The documented subprograms expose the full lifecycle of service administration and session management:

• DISCONNECT_SESSION — Disconnects all sessions connected to the local instance through a specified service name. It does not return until all corresponding sessions have disconnected, and it may be given an option such as disconnect_session_immediate (equivalently immediate) to terminate sessions at once rather than waiting for a transaction boundary. Internally it issues ALTER SYSTEM DISCONNECT SESSION sid, serial option, with the default option being POST_TRANSACTION.
• CREATE_SERVICE — Creates a new service name on the instance.
• MODIFY_SERVICE — Modifies the attributes of an existing service name.
• DELETE_SERVICE — Removes a service name from the instance.
• START_SERVICE — Starts a previously stopped service so that new connections may be accepted through that service name.
• STOP_SERVICE — Stops a running service, preventing new sessions from connecting through that service name.

Tables Accessed

The supplied ETRM metadata records no application tables referenced through APPS synonyms for this package. As a SYS-owned utility, DBMS_SERVICE operates against the Oracle data dictionary and internal service and session registries rather than against EBS application tables. Any impact on EBS data is therefore indirect, arising from session disconnections or service state changes, and not from direct DML against EBS schema objects.

Usage Notes

DBMS_SERVICE is typically invoked from a SYSDBA or DBA-privileged SQL session, from database administration scripts, or from operating-system level startup and shutdown automation. It is not normally called from EBS Forms, concurrent programs, or application-level custom code, since EXECUTE is granted only to the DBA role. It is used during clone and RAC configuration to establish the service names an EBS application tier expects, and during maintenance to stop or restart a service, or to forcibly disconnect sessions bound to a service. When investigating "netservicedb is not running," administrators use the package to verify service state, start a stopped service, or disconnect stale sessions, after which the service should be re-checked with the appropriate database views. Because DISCONNECT_SESSION blocks until completion, callers that cannot wait should invoke it through DBMS_JOB or otherwise run it in the background.