Oracle DBA Lesson 42A — Enable Shared Server and Verify a Client

Shared server lets many connected clients use a smaller pool of execution processes. A practical configuration check has three pieces: an intentionally bounded process pool, a service with a registered dispatcher, and a fresh client session whose actual server type is SHARED.

Oracle DBA Lesson 42A — Enable Shared Server and Verify a Client

Use this check before measuring capacity. Keep a separate dedicated administrator connected so that the management path remains available while the shared clients are tested.

Process counts and connection limits

These settings control different resources:

Parameter	Meaning	Small experiment
DISPATCHERS	Dispatcher configuration: protocol, count, and optional service/network attributes	One TCP dispatcher
SHARED_SERVERS	Initial and minimum shared-server process count	2
MAX_SHARED_SERVERS	Ceiling for automatically created shared servers	4
SHARED_SERVER_SESSIONS	Maximum concurrent shared user sessions	8
CIRCUITS	Limit on virtual circuits for inbound and outbound network sessions	8

Oracle can grow the shared process pool in response to demand. Keep MAX_SHARED_SERVERS at least equal to SHARED_SERVERS, and below PROCESSES. A minimum below the maximum allows the pool to vary. A minimum at or above the maximum holds the process count at the minimum, so a smaller maximum cannot override a larger minimum. An unspecified MAX_SHARED_SERVERS has no explicit default ceiling: free process slots and system resources constrain growth. See SHARED_SERVERS and MAX_SHARED_SERVERS.

Set the shared-session limit below SESSIONS to leave session slots for dedicated work. Actual connection capacity also depends on available process slots, other sessions, dispatcher limits, memory, and operating-system resources. CIRCUITS counts virtual circuits, including inbound and outbound use. It has a separate role from a user-session ceiling. The 19c parameter reference lists its default as 4294967295. Eight is an explicit experimental limit, chosen here to keep the example small. These counts are teaching inputs rather than universal production sizing values. See SHARED_SERVER_SESSIONS and CIRCUITS.

Shared-session memory needs capacity in the SGA. Plan the large pool and overall SGA headroom using measured session demand and the memory-management mode. When the large pool is configured, shared-session UGA uses it. Otherwise it uses the shared pool. Large-pool use can compete with other consumers. A single free-memory sample is useful context, but it does not establish that future peak demand will fit. This exercise supplies no memory-resizing command. See shared-server memory and managing memory.

Lab conditions and original configuration

Baseline: Oracle Database 19c, a single-instance multitenant CDB on Linux, using SQL*Plus. Use an instructor-designated disposable CDB with no unrelated clients, no background consumers of dispatcher services, and no required XML DB dispatcher configuration. Record the exact RU, edition, platform, and feature eligibility in the actual lab. These conditions and capacity checks still require validation before executing the example.

The administrator is a common account, represented here by C##COURSE_ADMIN, connected to CDB$ROOT with commonly granted ALTER SYSTEM and explicitly authorized access to the relevant SYS.V_$ views. Monitoring local reader sessions from root requires visibility of LABPDB, including appropriate CONTAINER_DATA. The reader already has CREATE SESSION in the open LABPDB. The local listener-owner account performs the listener inspection. This exercise creates no database account and supplies no broad catalog grant. See ALTER SYSTEM privileges.

Keep the independent administrator connection explicit: its root service descriptor contains (SERVER=DEDICATED). Verify its session's server type before making changes. Do not reconnect or close that management session during the experiment.

The worked example requires this exact instructor-prepared original profile:

Root parameter	Captured original	Test value
SHARED_SERVERS	Explicit 0	2
DISPATCHERS	Explicit empty string	(PROTOCOL=TCP)(DISPATCHERS=1)
MAX_SHARED_SERVERS	Explicit 8	4
SHARED_SERVER_SESSIONS	Explicit 16	8
CIRCUITS	Explicit 16	8

Before mutation, validate PROCESSES > 8, SESSIONS > 16, sufficient unused process and session slots for the test plus dedicated administration, and a reviewed memory budget. Confirm that LABPDB has no local disable override that will block shared use after the root enables it. The declared root baseline is still disabled at this point. The root configures the CDB process pool. From 12.1.0.2 onward, an individual PDB can set SHARED_SERVERS=0 to disable its use, or reset that PDB override to re-enable inheritance. It cannot size a separate shared process pool. This exercise changes only the root. See SHARED_SERVERS container behavior.

Capture the original values and full multi-valued dispatcher entries, not a shortened screenshot:

SHOW USER
SHOW CON_NAME
SET LINESIZE 250
SET PAGESIZE 1000
SET WRAP ON
COLUMN name FORMAT A30
COLUMN value FORMAT A100

SELECT name, ordinal, value, isdefault, issys_modifiable
FROM v$system_parameter2
WHERE name IN ('dispatchers','shared_servers','max_shared_servers',
               'shared_server_sessions','circuits','processes','sessions',
               'large_pool_size','sga_target','memory_target');

SELECT name, sid, ordinal, value, isspecified
FROM v$spparameter
WHERE name IN ('dispatchers','shared_servers','max_shared_servers',
               'shared_server_sessions','circuits');

SELECT * FROM v$dispatcher_config;
SELECT name, status FROM v$shared_server;
SELECT pool, name, bytes FROM v$sgastat
WHERE pool IN ('large pool','shared pool');

Save non-secret results in the lab record. V$SYSTEM_PARAMETER2 provides separate rows for list values and reports current instance settings. V$SPPARAMETER records stored parameter entries when an SPFILE is in use. Preserve empty values, the default or explicit state, and each dispatcher index. Also retain the relevant client aliases and their prior file content. See V$SYSTEM_PARAMETER2, V$SPPARAMETER, and V$DISPATCHER_CONFIG.

If your original profile differs, stop this mutating example. Existing dispatchers may serve XML DB or a service-specific configuration. A protocol-matching ALTER SYSTEM SET DISPATCHERS can change an existing configuration. Use an instructor-prepared, tested addition and reversal procedure, or complete the descriptor and evidence exercise without mutation. Unspecified or default original limits need their own validated restoration procedure. Do not substitute a null value or assume every parameter accepts an in-memory reset. See dispatcher modification rules.

Configure the bounded experiment

From the existing dedicated root administrator, after the stated preflight passes:

ALTER SYSTEM SET max_shared_servers=4 SCOPE=MEMORY;
ALTER SYSTEM SET shared_server_sessions=8 SCOPE=MEMORY;
ALTER SYSTEM SET circuits=8 SCOPE=MEMORY;
ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)(DISPATCHERS=1)'
  SCOPE=MEMORY;
ALTER SYSTEM SET shared_servers=2 SCOPE=MEMORY;

SELECT name, status FROM v$shared_server;
ALTER SYSTEM REGISTER;

The three ceilings are applied before enabling the pool. DISPATCHERS configures the TCP dispatcher, and SHARED_SERVERS=2 enables the shared pool with a minimum of two processes. SCOPE=MEMORY applies runtime changes immediately and keeps them until shutdown. It writes no persistent SPFILE change. Recheck the effective parameter values and any command errors before opening a client. See configuration and dynamic changes and SCOPE semantics.

For example, two idle process rows could be:

NAME  STATUS
S000  WAIT (COMMON)
S001  WAIT (COMMON)

WAIT (COMMON) means waiting for a user request. Active work can change these statuses, and process names and counts can vary with load. ALTER SYSTEM REGISTER requests immediate listener registration. The listener must be running, and the registration destination must be correct. See V$SHARED_SERVER.

Request a fresh shared connection

From the operating-system listener inspection connection:

lsnrctl SERVICES LISTENER

Use the actual listener name. Locate the intended service and instance, then inspect its service handlers. An authored abbreviated example is LABPDB, instance cdb1, with a handler D000, DISPATCHER, state:ready. This shows that the listener advertises a dispatcher for that service. See listener SERVICES.

Use a separate client alias with an explicit shared request. Replace the example host, port, and service with the instructor's validated values:

LAB_SHARED =
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=lab-db.example)(PORT=1521))
   (CONNECT_DATA=(SERVICE_NAME=LABPDB)(SERVER=SHARED)))

For the comparison client, create a separate LAB_DEDICATED alias to the same reader service with (SERVER=DEDICATED). The independent management alias targets the root service and also explicitly requests dedicated server. Avoid modifying an alias used by unrelated work. Inspect the applicable client profile: USE_DEDICATED_SERVER=ON overrides existing SERVER entries with DEDICATED, so that profile is unsuitable for the explicit shared test. See mixed shared and dedicated client configuration.

Open a new client:

sqlplus -L course_reader@LAB_SHARED

SQL*Plus prompts for the reader's password. (SERVER=SHARED) requires a dispatcher. When none is available, the request is rejected. Keep authentication controls unchanged while investigating a failed connection. After login, confirm the reader's container and user:

SHOW USER
SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV','SID') AS my_sid FROM dual;

Match session identity to connection evidence

From the root monitor:

SELECT con_id, sid, serial#, username, server, service_name
FROM v$session
WHERE username IN ('COURSE_READER','C##COURSE_ADMIN');

Match the reader's SID to its new connection, retain its SERIAL#, and check the user, service, and container. A separate dedicated reader connection can share the same username and service. Retain its own identity. An authored example of the new shared reader and existing root administrator is:

CON_ID SID SERIAL# USERNAME         SERVER     SERVICE_NAME
3      217 4089    COURSE_READER    SHARED     LABPDB
1      44  910     C##COURSE_ADMIN  DEDICATED  CDBLAB

In this authored example, SHARED represents the new reader's connection type and DEDICATED represents the management connection. The example identifiers, root service, and container number are illustrative inputs. Use the actual lab identities and record real query results. Changing SHARED_SERVERS leaves an already connected dedicated session on its existing dedicated connection. Open a fresh explicit shared connection and verify that new session. See V$SESSION.

Practice, pass criterion, and cleanup

1. Confirm the original profile and permissions, record the complete parameter, dispatcher, and client configuration, and validate capacity in the designated disposable lab.
2. Apply the runtime test values and verify the effective limits and shared-process rows.
3. Inspect the dispatcher handler for the intended service. Open one fresh shared reader and one fresh dedicated reader while retaining the existing root administrator.
4. Match their identities. Pass when you can show both reader connections reach LABPDB, identify their SHARED and DEDICATED server values, and show that the original management connection remains dedicated.
5. Keep the shared reader only if continuing immediately into the queue and draining practice. Retain the captured originals for that exercise. Otherwise complete the following cleanup now.

For this exact baseline, stop new shared assignments from the dedicated root administrator:

ALTER SYSTEM SET shared_servers=0 SCOPE=MEMORY;

Keep MAX_SHARED_SERVERS=4 while the existing clients finish. Oracle retains some shared servers for existing connections after the minimum becomes zero. Setting both SHARED_SERVERS and MAX_SHARED_SERVERS to zero terminates the shared processes and leaves remaining client requests queued, so retain the positive maximum during the drain. End only the test reader clients at their own prompts using EXIT ROLLBACK after finishing the read-only practice. This rolls back any unintended uncommitted work. See disabling shared server.

From the root monitor, check remaining user sessions and circuits:

SELECT con_id, sid, serial#, username, server
FROM v$session
WHERE server='SHARED' AND type='USER';

SELECT circuit, saddr, dispatcher, server, status FROM v$circuit;

For this isolated baseline, wait until all test shared sessions and circuits are absent. A root report with incomplete container visibility cannot prove that drain condition. Resolve visibility first. Unexpected rows are a stop condition for dispatcher removal, not permission to terminate unrelated work. See V$CIRCUIT.

Once the drain is confirmed, restore the explicit captured original limits and empty dispatcher setting:

ALTER SYSTEM SET dispatchers='' SCOPE=MEMORY;
ALTER SYSTEM SET max_shared_servers=8 SCOPE=MEMORY;
ALTER SYSTEM SET shared_server_sessions=16 SCOPE=MEMORY;
ALTER SYSTEM SET circuits=16 SCOPE=MEMORY;
ALTER SYSTEM SET shared_servers=0 SCOPE=MEMORY;
ALTER SYSTEM REGISTER;

Recheck effective parameter values against the saved original profile, confirm no test shared sessions, circuits, or dispatchers remain, and confirm the dedicated administrator can still issue a simple query. Compare the persistent parameter capture to confirm no SPFILE entries changed. Restore or remove only the client alias edits created by this exercise. Close the independent administrator after cleanup is complete. These restoration statements apply to the declared original profile. Other profiles require their prepared reversal. Full interval diagnosis and drain interpretation are developed in the companion lesson.

Quiz

1. Will changing SHARED_SERVERS convert an already connected dedicated session?

Yes, immediately. Yes, after its next query. No. Establish a fresh shared connection and verify it. Only if its username is COURSE_READER.

2. What does SHARED_SERVERS=2 set in the root?

The initial and minimum shared-process count. The maximum number of user sessions. Exactly two dispatchers. A permanent ceiling of two processes under every workload.

3. Which setting bounds automatic shared-process growth in this example?

SHARED_SERVER_SESSIONS=8 CIRCUITS=8 MAX_SHARED_SERVERS=4 SERVER=SHARED

4. What happens to an explicit (SERVER=SHARED) request when no dispatcher is available?

Oracle rejects the connection request. It automatically becomes a dedicated request. It creates a new PDB. It changes the existing administrator connection.

5. Which evidence completes the check for the new reader connection?

The parameter text contains SHARED_SERVERS=2. The intended new session's identity matches a SERVER=SHARED row. Two shared-process names are visible. A dispatcher exists for any service.

All numeric settings, handler fragments, process rows, and session identifiers are authored documentation-based examples. They are not captured Oracle execution. No database was connected to or changed while preparing this lesson. Actual lab execution, capacity validation, the exact RU and edition, permissions, observer visibility, and cleanup remain learner and instructor checks. The examples use core dynamic views and require no AWR or ASH report. Feature and licence eligibility remains part of the actual lab preflight.

AIOps Pro

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