Lesson 6	Shared Server Commands used by Oracle DBAs
Objective	Operate, monitor, and troubleshoot Oracle Shared Server with modern, supported commands.

Oracle Shared Server: Practical Commands for DBAs (12c–23ai)

You do not need Oracle Shared Server enabled to use SQL*Plus. SQL*Plus (or SQLcl) is a client; Shared Server is an optional instance configuration used to multiplex many lightweight sessions onto fewer server processes. Use it when connection concurrency is high and average request time is short.

Connect with modern, supported syntax

For day-to-day administration, use OS authentication or Easy Connect Plus with a service name (not SID):

Local OS authentication (recommended for DBAs)


sqlplus / AS SYSDBA

Remote (service name)


sqlplus scott@//dbhost.example.com:1521/finance.us-oracle.example.com
-- or with EZConnect+ options, e.g., timeouts, failover, etc.

To run as a normal user, just omit AS SYSDBA. Avoid legacy constructs like CONNECT INTERNAL and SID-based strings in new material.

Shared Server essentials

Key parameters and views you’ll use frequently:

Parameters: SHARED_SERVERS, MAX_SHARED_SERVERS, DISPATCHERS, MAX_DISPATCHERS.
Views: V$SHARED_SERVER, V$DISPATCHER, V$QUEUE, V$CIRCUIT, V$SHARED_SERVER_MONITOR.

1) Inspect current configuration


SHOW PARAMETER shared_servers
SHOW PARAMETER max_shared_servers
SHOW PARAMETER dispatchers
SHOW PARAMETER max_dispatchers

2) Enable or resize Shared Server

From dedicated mode to Shared Server (example values):


ALTER SYSTEM SET SHARED_SERVERS = 4 SCOPE=BOTH;
ALTER SYSTEM SET MAX_SHARED_SERVERS = 40 SCOPE=BOTH;

\-- Define dispatchers (TCP example); adapt SERVICE and PROTOCOL as needed
ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=4)' SCOPE=BOTH;

Tip: If you only need Shared Server during peak periods, schedule changes via job automation and trend off V$SHARED_SERVER_MONITOR.

3) Monitor runtime health


-- Server processes and throughput
SELECT id, status, requests, bytes_received, bytes_sent
FROM   v$shared_server
ORDER  BY id;

\-- Dispatcher load
SELECT name, network, status, messages, bytes, busy, idle
FROM   v\$dispatcher
ORDER  BY name;

\-- Request queues (dispatcher and request)
SELECT name, totalq, wait, busy, totalq - wait AS nowait
FROM   v\$queue
ORDER  BY name;

\-- Circuits (client ↔ server mappings)
SELECT circuit, dispatcher, server, busy, idle, bytes
FROM   v\$circuit;

4) Trace and diagnose

Target a specific session for SQL tracing (least intrusive, session-level):


-- Identify SID,SERIAL# for the target session first (e.g., from V$SESSION)
-- Then:
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<sid>, <serial#>, TRUE);
-- ... reproduce the issue ...
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<sid>, <serial#>, FALSE);

5) Disable (return to dedicated)

To go back to dedicated servers:


ALTER SYSTEM SET SHARED_SERVERS = 0 SCOPE=BOTH;      -- disables new shared servers
ALTER SYSTEM SET DISPATCHERS   = '' SCOPE=BOTH;      -- removes dispatchers

Quick troubleshooting checklist

Long waits in V$QUEUE: increase SHARED_SERVERS or DISPATCHERS; confirm network listener endpoints match client usage.
High busy ratios on dispatchers: scale dispatchers; ensure load balancers aren’t “pinning” sessions.
Large requests/long calls: Shared Server isn’t ideal for batch-heavy workloads; use dedicated (per service) for those modules.
Connection storms: use server-side profiles, client connect timeouts, and (if applicable) connection pooling.

Common SQL*Plus utilities you’ll use in this lesson

HELP – built-in reference
DESCRIBE – show object columns/arguments
SPOOL – capture output to a file
EXIT – terminate the session/script

Example admin workflow


-- Check current mode
SHOW PARAMETER shared_servers

\-- Enable Shared Server and add dispatchers
ALTER SYSTEM SET SHARED\_SERVERS = 4 SCOPE=BOTH;
ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=4)' SCOPE=BOTH;

\-- Verify load
SELECT name, totalq, wait, busy FROM v\$queue ORDER BY name;

\-- Capture findings
SPOOL shared\_server\_health.txt
SELECT \* FROM v\$shared\_server\_monitor;
SPOOL OFF;

Shell example (Linux)


#!/usr/bin/env bash
set -euo pipefail

ORACLE\_SID=finance01
export ORACLE\_SID

ORACLE\_HOME=\$(grep "^\${ORACLE\_SID}:" /etc/oratab | cut -d: -f2)
export ORACLE\_HOME PATH="\${ORACLE\_HOME}/bin:\${PATH}"

sqlplus -s / as sysdba <<'SQL'
SET PAGES 80 LINES 200
SHOW PARAMETER shared\_servers
ALTER SYSTEM SET SHARED\_SERVERS = 4 SCOPE=BOTH;
SELECT name, totalq, wait, busy FROM v\$queue ORDER BY name;
EXIT
SQL

Historical note: Server Manager

Server Manager (svrmgrl) was Oracle’s older DBA utility. It was retired; use SQL*Plus/SQLcl and Enterprise Manager Cloud Control for GUI workflows.

Legacy guidance: If you encounter “Server Manager” in older scripts or screenshots, treat it as historical. Replace with SQL*Plus equivalents.