Oracle DBA Lesson 38A — Identify Workloads with Database Services

A database service gives an application workload a connection identity. Reporting and order entry can use separate services while sharing the same pluggable database (PDB). That makes it easier to identify the activity belonging to each workload and to apply service-aware settings.

Oracle DBA Lesson 38A — Identify Workloads with Database Services

Identify the instance, container, and workload

Identity	What it identifies	Example
Instance SID	The Oracle instance on its host	LABCDB
PDB	A pluggable database container	LABPDB
Database service	A workload entry point associated with a database or container	report_app, orders_app

An instance runs the Oracle memory structures and processes. A PDB holds a database's application objects within the CDB. The service is the identity through which a client requests database work. Keep these identities separate when interpreting a connection. See Oracle instance concepts.

For this example, both report_app and orders_app have their PDB association set to LABPDB. A reporting session uses report_app. An order-entry session uses orders_app. Both arrive in LABPDB and can access the objects permitted to their database users. Database grants still determine access.

Choose services for the workload

Oracle creates a default service when it creates a PDB. The default service shares the PDB's name and is intended for administrative tasks. Use user-defined services for applications so their configuration can fit application requirements. See default and user-defined PDB services.

Service in this example	Intended use	PDB association
LABPDB	Administration through the default service	LABPDB
report_app	Reporting workload	LABPDB
orders_app	Order-entry workload	LABPDB

Use service-aware observation to ask useful questions: how much database CPU time belongs to reporting, or how many calls belong to order entry? Oracle's service statistics support this kind of analysis when the required aggregation is enabled. Timing statistics in V$SERVICE_STATS are cumulative values in microseconds. Collect interval differences when measuring a period. See V$SERVICE_STATS.

Resource allocation requires configured policy. Resource Manager can use a session's connection service in consumer-group mapping rules, with an active resource plan governing allocation. Define and verify that policy separately from choosing a service name. This lesson only explains the relationship. It does not configure Resource Manager or assume any licensing entitlement. See Resource Manager session mappings.

Map service definitions to PDBs

Run the following read-only SQL with the delegated view access appropriate to your lab:

SELECT name, network_name, pdb
FROM v$services
ORDER BY pdb, name;

NAME identifies the service definition. NETWORK_NAME is its network name, which can differ from NAME. PDB identifies the associated PDB. Ordering by PDB and service makes related definitions easier to compare. A PDB value of NULL represents a service with no PDB association. In a CDB, a connection through that service reaches the root. See V$SERVICES.

Under the example's stated definitions, the relevant rows would be:

NAME        NETWORK_NAME   PDB
----------  -------------  ------
LABPDB      LABPDB         LABPDB
orders_app  orders_app     LABPDB
report_app  report_app     LABPDB

This is authored instructional output, not a captured database execution. Real results may include additional internal and application services, qualified network names, and different casing. Record the exact values returned by your lab.

The three rows map three service definitions to one PDB. Assess activation separately with active-service evidence, listener advertisement, and an actual client connection. V$ACTIVE_SERVICES describes active services. Its BLOCKED column supplies another condition relevant to accepting new connections. The full service lifecycle and end-to-end verification are the next practical skill. See V$ACTIVE_SERVICES.

Interpret visibility before drawing an estate-wide conclusion. Use an authorized common root monitor with SELECT on SYS.V_$SERVICES and appropriate container visibility for cross-container mapping, or a PDB account with delegated view access for its permitted scope. An ordinary application account may lack access to this view. A missing row should prompt checks of the account, the current container, and reporting visibility.

Identify your own session's destination

An ordinary connected application user can inspect its own session context:

SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') AS current_service,
       SYS_CONTEXT('USERENV','CON_NAME') AS current_container
FROM dual;

The USERENV namespace describes the current session environment. SERVICE_NAME reports the service to which this session is connected. CON_NAME reports its current container. The column aliases make the result easier to read. See SYS_CONTEXT.

For a reporting connection in the example:

CURRENT_SERVICE   CURRENT_CONTAINER
----------------  -----------------
report_app        LABPDB

For a separate order-entry connection:

CURRENT_SERVICE   CURRENT_CONTAINER
----------------  -----------------
orders_app        LABPDB

These are expected example values under the stated connection assumptions. The service values distinguish the two workload connections. The common container value identifies their shared destination. Keep a connection's net service alias separate from the service reported by the database. An alias can resolve to a differently named database service.

Use the supported management interface

Oracle Database 19c deprecates customer use of the SERVICE_NAMES initialization parameter. Oracle recommends supported service tools and APIs such as SRVCTL, GDSCTL, or DBMS_SERVICE, chosen for the owning framework. Creation, activation, verification, and cleanup belong to the service owner and are outside this read-only lesson. See SERVICE_NAMES and DBMS_SERVICE.

Practice and conditions

Use the instructor-designated Oracle Database 19c single-instance CDB/PDB on Linux and the pre-provisioned accounts. The PDB must be open for the ordinary application connection, and the user needs CREATE SESSION in that PDB. Use existing services supplied by the instructor. The example report_app and orders_app names are a design illustration. This lesson does not authorize creating them or changing a production database.

1. Confirm the connected user and container with SQL*Plus SHOW USER and SHOW CON_NAME.
2. Run the session-context query. Record the service and container exactly as returned.
3. With delegated access, run the service-definition query. Match the session's service to the appropriate service definition and PDB association. Ask the instructor about any differing definition or network names, or about reporting scope.
4. Design names for an application workload and a batch workload in the same PDB. Write down one measurement each would enable, and any separately configured resource policy the design requires.
5. Explain which observations would establish the definitions, active status, and an actual successful connection.

Success means you can identify the instance, the PDB, and the service separately, explain two workload services sharing one PDB, and interpret both queries within their scope. The exercise is read-only and has no cleanup.

Quiz

1. Can two services lead to the same PDB?

Yes. Each can provide a separate workload identity within that PDB. No. Each service requires its own instance. Only if the PDB has two SIDs. Only if the services share the same name.

2. What does an instance SID identify?

The password of the connected user. A workload's consumer group. The Oracle instance on its host. The current SQL statement.

3. Which query identifies your connected session's service and current container?

A query of every row in V$SERVICES. SYS_CONTEXT with USERENV attributes SERVICE_NAME and CON_NAME. A query of the client machine's host name only. A query of SERVICE_NAMES with an assumed PDB name.

4. What does a V$SERVICES row associate with its PDB column?

A service definition and its associated PDB. A guaranteed successful client connection. A guaranteed CPU reservation. A separate physical database for every row.

5. Which action gives workload services resource-allocation behavior?

Adding the word priority to the service name. Choosing different text for the two service names. Renaming the instance SID. Configuring and verifying the applicable consumer-group mappings and active resource plan.

No SQL shown here was executed against a live lab during lesson production. Validate the examples and account permissions in the designated lab.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro