Execute SQL in SQL*Plus (Shared Server or Dedicated)

Key idea: Whether the database is configured for Shared Server or Dedicated Server, your SQL*Plus workflow to execute SQL is the same. Shared Server is a database-side connection architecture; the way you type and run SQL in the client does not change.

If you see references to Server Manager (svrmgrl), that’s historical content—Oracle retired it long ago. Use SQL*Plus/SQLcl and Enterprise Manager Cloud Control for administration.

Use Easy Connect or a TNS alias:

-- Easy Connect
sqlplus scott/tiger@//dbhost:1521/orclpdb1

\-- Or with OS authentication for administration
sqlplus / AS SYSDBA 

Example query (DBA environments may use DBA_USERS; use ALL_USERS if you lack privileges):

SQL> SELECT username FROM all_users ORDER BY username;

## USERNAME

ANONYMOUS
APEX\_PUBLIC\_USER
HR
SYS
SYSTEM
... 

• Semicolon (;): ends and executes a single SQL statement immediately.
• Slash on a new line (/): executes whatever is currently in the SQL*Plus buffer. It’s also how you execute a PL/SQL block after you’ve finished typing it.

Multi-line SQL example (semicolon):

SQL> SELECT username, account_status
  2  FROM   dba_users
  3  WHERE  username IN ('SYS','SYSTEM');

Re-executing from the buffer (slash):

SQL> LIST        -- show the current statement in buffer
SQL> /           -- run it again

PL/SQL block (requires / on its own line):

SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL');
  3  END;
  4  /

LIST              -- show the current statement
RUN               -- synonym for slash; executes buffer
ED                -- edit current buffer in default editor
/                 -- execute buffer
CLEAR BUFFER      -- empty the buffer
@script.sql       -- run a script file
@@more.sql        -- run another script from same directory

List currently logged-in database users (user sessions only)

SQL> SELECT username, sid, serial#, status
  2  FROM   v$session
  3  WHERE  type = 'USER'
  4    AND  username IS NOT NULL
  5  ORDER BY username;

Show your own session info

SQL> SELECT sys_context('USERENV','SESSION_USER') AS whoami,
  2         sys_context('USERENV','SID')          AS sid
  3  FROM   dual;

These checks are not required to execute SQL, but help confirm the server-side setup:

SQL> SHOW PARAMETER shared_servers
SQL> SHOW PARAMETER dispatchers

SQL> SELECT name, status, requests
2  FROM   v\$shared\_server
3  ORDER  BY name;

SQL> SELECT name, status, accepted, idle, busy
2  FROM   v\$dispatcher
3  ORDER  BY name; 

• “Use Shared Server to execute SQL.” Not exactly—clients execute SQL the same way; Shared Server just changes how the database services many sessions.
• “’/’ terminates any SQL statement.” In SQL*Plus, ; ends a SQL statement. / runs the current buffer (and is required to run PL/SQL blocks).
• “Server Manager” instructions. Historical. Use SQL*Plus/SQLcl; for GUI/monitoring, use Enterprise Manager Cloud Control.

• For readable output: SET PAGESIZE 200, SET LINESIZE 200, SET TRIMSPOOL ON.
• Need a record? Use SPOOL file … SPOOL OFF to capture output.
• Repeatable scripts: put SQL in .sql files and run with @.

Click the quiz link below to take a quiz on using the server manager.
Using SQL Plus - Quiz