SQL*Plus Command Reference: The 2026 Ops Companion

SQLPlus still pays my bills in 2026. I spend most days inside thick Oracle estates where GUI tools are blocked, air-gapped, or just slower than a prompt. When I need to triage a production hiccup at 2 a.m., SQLPlus is the one binary every jump box already has. In this guide I’m walking you through the commands I actually reach for—how I start sessions, script repeatable work, shape output into readable reports, and squeeze performance insight without leaving the shell. By the end you should feel comfortable treating SQL*Plus not as an ancient relic but as a reliable colleague that never asks for dark mode.

Where SQL*Plus fits in 2026

I use SQLPlus alongside SQLcl and cloud consoles. SQLcl gives modern niceties, but SQLPlus wins when I need deterministic behavior across legacy Unix hosts and tightly controlled bastions. Its footprint is tiny, its startup is instant, and every DBA knows its syntax. Oracle continues to ship incremental improvements—command history, long identifiers, application continuity flags, and CSV markup are now standard—so the tool keeps pace with current database releases without breaking old scripts.

When I pick SQL*Plus vs alternatives

Remote jump boxes with no GUI packages and no outbound internet.
Controlled change windows where I need zero surprises in CLI behavior.
Air-gapped datacenters where only the base client is approved.
CI runners that must stay lean; SQL*Plus is already present in the Oracle Instant Client.
Disaster recovery playbooks where deterministic logging matters more than fancy formatting.

When I avoid it

Heavy interactive tuning where I want line editing, completion, and scripting language support—then I reach for SQLcl or an IDE.
Data wrangling that needs JSON manipulation or procedural loops—Python or SQLcl scripts are nicer.
Massive exports/imports—Data Pump wins; SQL*Plus is for targeted slices.

Starting SQL*Plus: invocation and arguments

I rarely type just sqlplus. Instead I pick switches that control banner noise, resiliency, and authentication upfront:

sqlplus -s for quiet automation where prompts would pollute logs. The silent flag suppresses banners and echoes, which keeps Jenkins jobs clean.
sqlplus -f on long-lived ETL hosts; it nudges SQL*Plus to reduce startup overhead.
sqlplus -ac when I want Application Continuity enabled so transient network blips don’t kill the session mid-batch.
sqlplus /nolog to open a connectionless shell, build or test scripts, then connect with a stored wallet credential later. This avoids putting passwords on the command line.
sqlplus user/password@"host:1521/service" leveraging Easy Connect Plus to skip tnsnames.ora. Quoting the string keeps shells from treating @ specially.

A quick safety habit: never place production passwords directly after sqlplus; most shells keep history. I either use /@alias with an external wallet or start /nolog and connect /@alias.

Options that help in odd environments

-L to fail fast if login fails (useful in pipelines).
-R 1 to disable restricted command usage and force a clean login in locked-down shells.
-M "HTML ON" for quick ad-hoc HTML output without altering scripts.
-V to print the client version when I’m debugging environment drift across hosts.

Startup templates I keep around

Automation template: sqlplus -s /nolog @bootstrap.sql where bootstrap.sql handles wallet connect, session settings, and error traps.
Break-glass template: a shell alias spdb(){ sqlplus -L -ac /@${1:-prod}; } so I can jump into the right PDB with one word during incidents.
Developer template: sqlplus -prelim / as sysdba when I need to inspect hanged instances without consuming a full session.

Connecting patterns I rely on

Default OS authentication: connect / as sysdba when the OS account is trusted. Good for local admin, not for multi-user jump boxes.
Proxy connections: connect appuser[reportuser]/@hrpdb lets me run as a reporting role without separate passwords.
PDB awareness: Always confirm the container with show conname. My scripts begin with alter session set container=pdbfin; to avoid surprises.
Session reattachment: When a VPN bumps me, I restart with -ac so Application Continuity can replay eligible calls transparently.
Edition-based redefinition: alter session set edition=blue; before testing patched packages, so I can roll forward/back without touching synonyms.

Easy Connect Plus recipes

IPv6 service: sqlplus -s /@"[2001:db8::8]:1521/finance?service_name=finpdb"
TCPS with wallet: sqlplus -s /@"db.example.com:2484/erp?walletlocation=/opt/wallet&sslservercertdn=CN=db.example.com"
Load-balanced scan: sqlplus -s /@"scan-fin:1521/finpdb?failover=on&retry_count=3"

Wallet hygiene tips

Store wallets outside home directories on shared jump hosts; set TNS_ADMIN to point to a secured directory with 600 perms.
Version-control the sqlnet.ora that references the wallet location; never commit the wallet files themselves.
In cron, export NLSLANG and TNSADMIN explicitly so locale and wallet paths don’t depend on user profiles.

Command categories at a glance

Editing and re-running without leaving the buffer

SQL*Plus maintains the last statement in a buffer. I treat it like a one-line scratchpad:

LIST shows the current buffer with line numbers.
CHANGE /old/new patches only the first occurrence on the current line; I use distinct separators like ~ to avoid escaping slashes.
APPEND and INPUT add text; two spaces before the new text insert a blank.
RUN lists then executes; / executes silently—handy inside scripts so logs stay short.
Modern builds include command history (arrow keys) so I can recall earlier statements without reopening an editor.

When I need full editing, EDIT respects the SQLPATH and set editor configuration. On remote servers with only vi, I still get a fast workflow: edit, patch, wq, / to run.

Edge cases

Multiline PL/SQL blocks: I use INPUT to avoid accidental buffer resets; a stray ; ends the buffer.
Ampersands in literals: set define off before editing, otherwise &1 expands unexpectedly.
Large statements: buffer is limited; for big DDL I keep it in a file and @ it instead of relying on the buffer.
Lost buffer after CONNECT: reconnecting clears the buffer, so I SAVE to a temp file before swapping credentials.

Making `EDIT` predictable

Set define _editor=vim (or ed, nano, whatever exists) in login.sql so teammates share the same edit behavior.
On Windows jump hosts, point _editor to notepad.exe and add set termout off around edit calls to suppress noisy redraws.
For audited environments, avoid edit entirely and rely on versioned script files to preserve change history.

Formatting and reporting like a human wrote it

Plain SQL output is tough to read. SQL*Plus formatting commands let me ship ad-hoc reports that managers can parse without context. A typical setup for salary reports looks like this:

set pagesize 200 linesize 180 trimspool on feedback on
set numwidth 12 verify off
column dept_name format a30 heading "Department"
column total_pay format $999,999.90 heading "Total Pay"
break on dept_name skip 1
compute sum label "Dept Total" of totalpay on deptname

COLUMN ... FORMAT applies numeric and character masks—dollar signs, commas, wrapping behavior. BREAK and COMPUTE create grouped subtotals without writing analytic functions. TTITLE and BTITLE stamp report headers/footers with timestamps, which I use for audit trails.

The SET MARKUP CSV ON toggle is my shortcut for exporting straight to spreadsheets without external tools. It respects existing column formats and can be combined with SPOOL payroll.csv.

More formatting tricks

set wrap off + wide linesize prevents wrapped numbers from misaligning columns.
column col_name heading "Text\nWrapped" gives two-line headers without post-processing.
set underline ‘=‘ draws separators for TTITLE and BTITLE outputs.
set termout off around helper scripts keeps logs focused on final results.
set trimspool on removes trailing spaces so diffs stay clean in CI comparisons.
set null ‘[null]‘ makes missing values obvious in printed reports.
set colsep ‘ | ‘ when eyeballing joins; quick visual separator reveals mismatches.

HTML and JSON output on demand

Quick HTML: set markup html on spool on then run queries; great for drop-in email attachments.
CSV with UTF-8 BOM: set markup csv on delimiter , encoding UTF8 so Excel on Windows opens it cleanly.
JSON-ish: For narrow result sets, set markup html on and select json_object(...)—it’s crude but works when no jq is available.
Markdown tables: set markup csv on delimiter ‘|‘ plus a quick header row lets me paste outputs into wikis without reformatting.

Reusable formatting bootstrap

I keep a common/env.sql that sets serveroutput on size unlimited, sensible pagesize, linesize, date formats, and a consistent currency mask. Each script begins with @common/env.sql so every report looks identical regardless of who ran it.

Scripting and automation patterns

Parameterized scripts

I keep scripts idempotent and parameter-driven:

accept v_year number prompt "Fiscal year? "
accept v_region char prompt "Region code? "
spool revreport&vyear.&v_region..log
@queries/revenuebyregion.sql &vyear &vregion
spool off

ACCEPT validates datatype (NUMBER, CHAR, DATE), supports default values, and hides input with HIDE for secrets. DEFINE stores constants; UNDEFINE cleans the namespace to keep scripts side-effect free.

Running nested files

@file.sql runs from the current directory.
@@file.sql reuses the last script’s path, which is perfect for modular installs (install/main.sql calling @@tables/create.sql).
START is an alias of @; I prefer @ for brevity.
@?/rdbms/admin/utlxplan.sql is a classic example of calling stock scripts shipped with the database; the ? expands to $ORACLE_HOME.

Spooling for audit and debugging

SPOOL captures everything: query results, feedback lines, error messages, and optional command echo when SET ECHO ON is active. That comprehensive trail makes post-mortems easy. I redirect long runs to /var/tmp/script$ORACLESID_$(date +%s).log to avoid collisions. Remember to SPOOL OFF or the file stays locked.

Mixing OS commands safely

HOST (or ! on Unix) lets me call ls, move files, or gzip logs without exiting SQL*Plus. I keep these minimal to avoid shell injection risks. For example:

host mkdir -p /var/tmp/runlogs
spool /var/tmp/runlogs/job_&1..log

Control flow without PL/SQL

SQL*Plus has primitive conditional support:

whenever sqlerror exit 1 to halt on errors.
whenever oserror exit 2 to bail on host issues.
set termout off + column/spool for pseudo-branching by selecting values and sourcing scripts conditionally from shell wrappers.
pause with a message to require human confirmation mid-run during risky steps.

Error handling blueprint

At the top of every automation script I add:

set define on verify off feedback on termout on echo off
whenever sqlerror exit sql.sqlcode
whenever oserror exit 9

This guarantees that CI jobs fail loud and propagate the database return code back to the orchestrator.

Parameter defaults and validation

accept v_date date default trunc(sysdate) prompt "Run date (YYYY-MM-DD)? " enforces format and keeps logs consistent.
Guardrails: column ok newvalue vok; select case when ‘&venv‘ in (‘PROD‘,‘DR‘) then ‘Y‘ else ‘N‘ end ok from dual; followed by whenever sqlerror exit 99 if vok=‘N‘ to prevent prod runs from unapproved hosts.

Performance and troubleshooting from the prompt

Timing: TIMING START load_step before a block, TIMING STOP after—gives millisecond resolution without external tools.
Client-side fetch tuning: SET ROWPREFETCH 100 and SET LOBPREFETCH 16384 reduce round trips for reporting jobs pulling many rows or LOBs.
Statement cache: SET STATEMENTCACHE 50 keeps parsed statements on the client side, cutting parse overhead for repetitive loops.
AUTOTRACE: pairs with SET AUTOTRACE ON EXPLAIN STATISTICS to see plan and row counts after each query—ideal in non-production where I can afford the overhead.
Server output: SET SERVEROUTPUT ON FORMAT WORD_WRAPPED for readable PL/SQL debug text; switch to TRUNCATED when lines are noisy.
SQL Trace launch: alter session set sql_trace=true; then tkprof the trace file; no GUI needed.
Optimizer environment diff: show parameter optimizer and select * from v$parameter where name like ‘optimizer%‘; bundled in a script tells me if a host is using odd session hints.

A small habit: I keep SET FEEDBACK ON ONLY in batch mode. It suppresses result sets while still telling me how many rows were touched, which is perfect for runbooks that only care about counts.

Quick plan and stats loop

set autotrace traceonly explain statistics
select /+ gatherplanstatistics / * from factsales where saledt >= trunc(sysdate)-7;
set autotrace off

This combo shows the plan and row stats without dumping the full result set, speeding iteration on predicates and indexes.

Watching waits from the client

I sometimes sample wait events from the same session:

set serveroutput on
begin
for i in 1..5 loop
dbms_lock.sleep(1);
for r in (select event, waittime, timeremainingmicro from v$sessionwait where sid = sys_context(‘USERENV‘,‘SID‘)) loop
dbmsoutput.putline(r.event|‘ ‘r.waittime‘ ‘r.timeremaining_micro);
end loop;
end loop;
end;
/

It’s crude but enough to see if I’m blocked on IO, row locks, or network.

Diagnosing ORA-errors fast

show errors after compiling PL/SQL surfaces line numbers without needing an IDE.
column "ORA" format a60; select * from v$diagalertext where originating_timestamp > systimestamp - interval ‘5‘ minute; gives me recent alert messages while staying inside the same session.
host oerr ora 1555 works if oerr is on the host; it’s still the quickest way to recall error text on ancient servers.

Session-level instrumentation

alter session set tracefileidentifier=‘RUNBOOK101‘; so trace files are easy to find.
alter session set events ‘10046 trace name context forever, level 8‘; when I need waits without binds; remember to turn it off with alter session set events ‘10046 trace name context off‘;.

Common mistakes I still see

Forgetting EXIT at the end of scripts, leaving sessions idle on shared servers.
Pushing passwords in cleartext via sqlplus user/pwd@db—shell history and process lists will record it. Use wallets or /nolog + connect /@alias.
Not setting TERMOUT OFF when spooling large install scripts; it slows runs by echoing every line.
Using SET ECHO ON globally; enable it only inside debugging blocks or logs get unreadable.
Ignoring PDB context in multitenant deployments—always show con_name before DDL.
Relying on default pagesize/linesize; clipped rows hide data issues.
Leaving define on when scripts embed & in literal URLs; disable or escape.
Forgetting set numformat for scientific notation-prone columns; large numbers become unreadable.

Security and credential handling I trust

In regulated environments I lean on the secure external password store. With a wallet entry named sales_ro, I connect like this:

sqlplus /@sales_ro

The wallet keeps credentials off disk in cleartext and off the process list entirely. For batch jobs, pair this with -s and WHENEVER SQLERROR EXIT 1 to fail fast. Avoid embedding passwords in @ scripts; if a script must prompt, use ACCEPT ... HIDE so input stays invisible.

Hardening checklist for SQL*Plus scripts

Use /nolog + wallet aliases; never hardcode passwords.
Set umask 077 in wrappers so spooled logs aren’t world-readable.
Prefer spool /secure/path over /tmp on shared hosts.
Add whenever sqlerror exit 1 at the top of automation scripts.
Turn on set termout off when echoing sensitive setup commands.
Clean up environment variables that might expose connection strings in process listings.
On shared runners, unset HISTFILE or run set +o history in wrappers to avoid leaking connection strings.

Auditing and accountability

Use set appinfo on plus DBMSAPPLICATIONINFO.setmodule(‘sqlplus‘,‘monthlyclose‘); so DBAs can trace sessions in AWR and ASH.
Spool logs with UTC timestamps (column now newvalue vnow; select tochar(systimestamp at time zone ‘UTC‘,‘YYYYMMDDHH24MISSFF3‘) now from dual;) to correlate with database alert logs.

Real-world playbook: assembling a reusable report runner

Here’s a pattern I roll out for finance close each quarter:

-- report_driver.sql
set define on verify off serveroutput on feedback on timing on
accept v_period char prompt "Period (YYYY-MM)? "
accept v_pdb char prompt "PDB name? "
conn /@&v_pdb
set termout off
@common/env.sql -- sets linesize/pagesize/markup
set termout on
spool close&vperiod..log
@@reports/revenue.sql &v_period
@@reports/expenses.sql &v_period
@@reports/cashflow.sql &v_period
spool off
exit

set termout off hides helper script noise while env.sql configures shared settings.
The double @@ keeps relative paths working regardless of where I invoke the driver.
timing on captures per-report durations; later I grep the log for Elapsed:.

Deploying this playbook safely

Keep env.sql under version control; it’s the single source of formatting truth.
Add a thin shell wrapper that sets ORACLESID, exports NLSLANG, and writes a start/end marker to syslog.
Store the wallet in a root-owned directory; scripts run as a service user with read-only access.
Rotate logs with logrotate or a cron job that gzips anything older than a week.
Add whenever sqlerror exit near the top so the wrapper can alert on non-zero exit codes.

Making it observable

Have each report script write a summary row into a control table (report_runs) with run id, period, status, and elapsed seconds. Later, dashboards can show success rates without scraping logs.
Include set markup csv on when generating attachments; the wrapper emails the CSV using mailx from the host—no extra tooling.

Working with large outputs

SQL*Plus can drown you with data. I manage this with:

set pagesize 50000 for single-page dumps to avoid page headers mid-file.
set long 2000000 when selecting CLOBs; otherwise values truncate.
set arraysize 200 to fetch more rows per round trip; good for full-table copies piped to gzip.
set termout off while spooling; re-enable before final summary to keep operators informed.
set sqlformat ansiconsole (newer clients) for readable column-aligned output when not using COLUMN FORMAT.
set markup csv on quote off when dumping to a pipe feeding gzip; reduces characters in-flight.

Streaming exports without Data Pump

For quick extracts I sometimes do:

sqlplus -s /@sales_ro <<'SQL'
set pagesize 0 feedback off verify off heading off echo off
set arraysize 1000
set longchunksize 32767
set linesize 4000
spool /var/tmp/sales.tsv
select /+ parallel(4) / custid|chr(9)tochar(sale_dt,‘YYYY-MM-DD‘)chr(9)amount
from fact_sales
where saledt >= addmonths(trunc(sysdate,‘MM‘),-1);
spool off
SQL

This streams straight to TSV; I can then gzip and scp without involving Data Pump.

Sampling instead of dumping everything

When prod links are slow I rely on sample hints and fetch first:

select /+ sample(2) / * from big_table fetch first 1000 rows only;

This keeps testing fast while still surfacing skew and null patterns.

Session settings I standardize

set nlsdateformat ‘YYYY-MM-DD HH24:MI:SS‘ for consistent logs.
set time on timing on so every output line shows clock time during long runs.
set sqlprompt ‘user@connectidentifier date> ‘ to remind me which DB I’m on; prevents prod/dev confusion.
set autocommit off in admin scripts to avoid accidental commits; commit explicitly when ready.
set heading on feedback on in interactive mode; heading off feedback off in machine-parsed scripts.

Login scripts that travel well

Place a login.sql in a directory listed in SQLPATH with:

Prompt customizations.
Sensible NLS and formatting defaults.
A guard to skip heavy settings when &1 equals -s (silent). Example:

column silent newvalue vsilent
select case when ‘&1‘=‘-s‘ then ‘Y‘ else ‘N‘ end silent from dual;
whenever sqlerror continue
set termout off
column host newvalue vhost
select sys_context(‘USERENV‘,‘HOST‘) host from dual;
set termout on

Now scripts stay light in silent mode but rich in interactive sessions.

Modern niceties you might have missed

History navigation: Recent builds support arrow-key history; enable with set history on if your distro ships it disabled.
LONG identifiers: Up to 128-char object names work fine; older scripts that truncate aliases may need cleanup.
SQLFORMAT options: set sqlformat ansiconsole, json, csv, xml—handy for feeding other tools without leaving SQL*Plus.
PDB change prompt: connectidentifier updates after alter session set container=..., so your prompt reflects the active PDB.
PASSWORD Rollover: alter user ... identified by password expire plus connect user; SQL*Plus will prompt for new passwords interactively, which is smoother than scripting it.

Comparing SQL*Plus and SQLcl in practice

Scenario

SQL*Plus

SQLcl —

—

— Footprint on bastion

Already installed with Instant Client

Requires extra package Command history

Basic, newer builds only

Rich (rlwrap-like) Scripting

Minimalistic, deterministic

Java-based, supports scripting and completion Output formats

CSV, HTML, classic fixed-width

Adds JSON, Liquibase-style DDL, pretty plans Startup time

Near-zero

Slightly slower due to JVM When I choose it

Change windows, DR drills, CI

Development, ad-hoc exploration

Practical mini-recipes

Check active PDBs quickly: column name format a30; select name, open_mode from v$pdbs order by 1;
Find blocking sessions: select sid, serial#, blockingsession from v$session where blockingsession is not null;
Disable a trigger safely: alter session set container=pdbfin; alter trigger trgpayroll disable; then show errors to confirm no compilation issues.
Generate grants for a user: select ‘grant ‘| privilege
‘ to newuser;‘ from dbasysprivs where grantee=‘OLDUSER‘; spool to file, review, then run.
Spot bad plans: set autotrace on statistics; select /+ gatherplanstatistics / count(*) from orders where status=‘OPEN‘; look for high consistent gets.
Quick data diff between PDBs: spool query results from each PDB with the same formatting settings, then diff the files on the host.

Edge cases in multitenant worlds

connect / as sysdba logs you to CDB$ROOT; remember to switch containers before running PDB-specific DDL.
create user in root creates a common user; use c## prefix intentionally. Local users must be created after alter session set container=....
set container is not transactional; scripts should show con_name after switching and log it to spool for audit.
Resource manager plans differ per PDB; slow queries might be throttled—select plan, utilizationlimit from cdbrsrcplandirectives where pdbname=‘PDBFIN‘; reveals constraints.

Backup and recovery helpers

Even though RMAN is king, SQL*Plus remains useful:

startup mount; followed by recover database using backup controlfile until cancel; when guided by storage teams.
archive log list; to confirm archivelog mode during checks.
alter system switch logfile; before cold copies.
select checkpoint_change# from v$datafile; to compare file checkpoints across nodes.

Working with Data Guard and Application Continuity

show parameter logarchiveconfig; to verify broker settings from a jump box.
select databaserole, protectionmode, switchover_status from v$database; at the start of every DR drill script.
sqlplus -ac is critical on flaky links; it allows eligible calls to be replayed without re-running scripts manually.
When running read-only reports on a standby, set set transaction read only; to avoid accidental writes and get consistent snapshots.

Operating system integration patterns

Environment guard: shell wrapper checks hostname pattern before allowing prod aliases, blocking accidental prod runs from dev laptops.
Color-coded prompts: shell sets SQLPLUS_COLOR=prod and sqlprompt reflects it; even in dumb terminals I can tell prod vs non-prod.
Log shipping: after spooling, a host gzip -9 keeps footprints small; pair with scp -q to archive logs centrally.
Scheduler integration: use cron or systemd timers with sqlplus -s /@alias @script.sql > /var/log/sqlplus/script.log 2>&1; always include set echo off termout off to keep logs parseable.

Testing your scripts without touching prod

Point TWO_TASK or LOCAL to a sandbox service name before running; the same scripts should work unchanged.
Use alter session set optimizerfeaturesenable=‘19.1.0‘; to simulate older behavior while connected to newer DBs.
Wrap DDL in begin execute immediate ‘...‘ ; exception when others then dbmsoutput.putline(sqlerrm); end; blocks to surface permissions issues during rehearsals.
Dry-run installs with set define ‘^‘ so variables don’t expand; this reveals unbound & references before prod.

Observability from nothing but SQL*Plus

When monitoring stacks are down, I still gather:

select systimestamp, instancename, status, databasestatus from v$instance;
select tablespacename, usedpercent from dbatablespaceusagemetrics order by usedpercent desc;
select event, totalwaits, timewaited/100 from v$system_event where event like ‘enq%TX%‘;
select sqlid, executions, elapsedtime/1e6 ets, buffergets from v$sql order by et_s desc fetch first 10 rows only;
select sid, serial#, event, state, secondsinwait from v$session where username is not null;

These snippets fit in outage bridges and give DBAs enough to triage without OEM.

How I teach new teammates

Start with buffer commands (list, change, run) to build muscle memory.
Introduce set pagesize/linesize/numwidth next so their outputs look clean.
Add spool and whenever sqlerror exit for discipline in automation.
Finally show autotrace and sql_trace so they learn to self-serve performance questions.
I share a tiny cheatsheet.sql that prints current settings (show all) and the active container; they run it first on every new host.

Checklist before running any SQL*Plus job in prod

Confirm wallet alias works: sqlplus -s /@alias @ping.sql where ping.sql is a harmless select 1 from dual;.
Verify PDB: show con_name and log it to the spool.
Set termout off while loading helper scripts; termout on before the main query so operators see progress.
Ensure whenever sqlerror exit 1 is active; double-check via show errorlogging if enabled.
Set heading off only if the consumer is a machine; humans need headings.
Align NLSDATEFORMAT with downstream consumers (CSV vs human report).
Rotate old spools in /var/tmp to avoid disk issues; host find /var/tmp -name ‘*.log‘ -mtime +7 -delete if policy allows.

Quick reference: SET options I actually toggle

Setting

Why I toggle it

Default pain avoided —

—

— set echo on/off

Show commands during debug

Silent failures set define on/off

Control & substitution

Accidental variable expansion set feedback on only/off

Count vs clutter

Noisy logs set linesize 200-4000

Preserve wide columns

Wrapped numbers set pagesize 0/100/50000

Control headers

Page breaks in spools set serveroutput on size unlimited

PL/SQL logging

Truncated messages set termout on/off

Screen vs spool

Slow terminal echoes set timing on

Measure blocks

Guessing durations set autotrace on/off

Plans + stats

External tools set markup csv/html on/off

Export quickly

External formatting set sqlformat json/csv/ansiconsole

Feed downstream scripts

Manual conversions

Building a portable toolkit directory

I keep a sqlplus/ folder under version control containing:

login.sql and glogin.sql templates.
env.sql for formatting defaults.
ping.sql, whoami.sql (prints user, container, host), tsfree.sql, topsql.sql.
install/ with @@-friendly relative paths.
README.md documenting environment variables (TNSADMIN, NLSLANG, SQLPATH).
A Makefile target make check that runs sqlplus -s /@test @ping.sql to verify wallet and listener before shipping changes.

Troubleshooting checklist when SQL*Plus misbehaves

Hangs at connect: check tnsping service; if good, try sqlplus -prelim to see if the database is blocked at process creation.
Character garble: ensure NLS_LANG matches server charset; set set markup csv on encoding UTF8 when in doubt.
Slow fetch: bump arraysize and rowprefetch; confirm no firewall idle timeouts via sqlplus -ac.
ORA-28040 (no matching auth protocol): update Instant Client; 10g-era clients fail against 19c unless patched.
SP2-0734 unknown command errors: usually missing semicolons or trailing spaces after / in scripts—@ files are safer than copy-paste.
SP2-0306 invalid option: double-check position of -s or -L; switches must precede username/password@.

Extending SQL*Plus with small helpers

Wrap frequent queries in scripts: topsegments.sql, locktree.sql, ash_sample.sql.
Use COLUMN ... NEWVALUE to capture values into substitution variables (e.g., column pdb newvalue vpdb; select syscontext(‘USERENV‘,‘CONNAME‘) pdb from dual; then reference &vpdb in filenames).
Collect metrics into CSV for Grafana imports: set markup csv on; spool metrics.csv; select systimestamp, logicalreads, physicalreads from v$sysstat where name in (...); spool off;.

Final thoughts

SQL*Plus in 2026 is still my fastest path from question to answer inside Oracle estates that prize stability over flash. Its command set is compact but sharp: session control, formatting, scripting, and lightweight diagnostics all live in a single binary that boots instantly. Master a handful of SET, COLUMN, SPOOL, and AUTOTRACE patterns and you can ship reliable reports, safe change scripts, and performance checks from any jump box on the planet. Treat it like a colleague—predictable, terse, and always awake—and it will keep paying your bills too.