Scops
Docs Download

Standard Edition

Oracle Standard Edition 2 Monitoring: What the Packs Take Away, What You Can Still Query, and How to Monitor SE2 Fully

2 November 2026

Standard Edition 2 is Oracle at a fraction of the price, with one condition that bites at monitoring time: the Diagnostics Pack and the Tuning Pack cannot be licensed for it. ASH, AWR, ADDM and Real-Time SQL Monitoring are not an upgrade you forgot to buy. They are not for sale on SE2. This page lists what that takes away, what every SE2 DBA can still query for free, and how to get full monitoring anyway.

What Standard Edition 2 includes, and what it does not

The rules are in Oracle's own licensing manual, and the table below is taken from it. Oracle's licensing data sheet adds the hardware rule: Standard Edition 2 "may only be licensed on servers that have a maximum capacity of 2 sockets", and "each Oracle Database Standard Edition 2 database may use a maximum of 16 CPU threads at any time". Oracle RAC is not available on SE2 since 19c; Standard Edition High Availability, a cold failover on Grid Infrastructure, is included instead.

FeatureSE2EEWhat it means for monitoring
Oracle Diagnostics Pack (AWR, ASH, ADDM)NoExtra costNo session history and no workload reports from Oracle. V$ACTIVE_SESSION_HISTORY and the DBA_HIST views belong to the pack.
Oracle Tuning Pack (SQL Tuning Advisor, Real-Time SQL Monitoring)NoExtra costNo V$SQL_MONITOR, no SQL profiles, no automatic plan fixes with profiles.
SQL Plan ManagementYes, restrictedYesOne baseline per statement, no plan evolution. Enough to pin a plan that flipped.
Oracle PartitioningNoExtra costNo interval partitions, so old rows are deleted or archived, not dropped.
Oracle Data Guard (redo apply)NoYesA standby on SE2 is a scripted archive-log shipping, so its lag needs its own watch.
Oracle RACNo (19c)Extra costOne instance per database. Standard Edition High Availability is included.
Parallel query and DMLNoYesA big report runs on one CPU thread, however many the server has.
Online index rebuild, online table redefinitionNoYesRebuilds and moves lock the table. Plan them.
Flashback Database, Flashback TableNoYesA bad change is undone from a backup or by hand.
Advanced Compression, Basic Table CompressionNoYes / extra costSpace grows at the uncompressed rate. The forecast matters more.
Database Resource ManagerNoYesNo consumer groups. A runaway session is found and killed, not throttled.
Block change trackingNoYesIncremental backups read the whole database. Watch their duration.
Instance CagingYesYesCPU_COUNT caps the instance.

The rows read "available" and "unavailable" in the manual; "extra cost" is its own note on Enterprise Edition. Each SE2 "No" is a monitoring task that falls on you: there is no pack to show the slow hour, no Resource Manager to contain a bad query, no partition to drop.

The views an SE2 database must not query

The views still exist in an SE2 database, which is the trap. The licensing manual says that V$ACTIVE_SESSION_HISTORY and its base table X$ASH are part of the Diagnostics Pack, and so is every DBA_HIST_ view except six: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ and DBA_HIST_UNDOSTAT. DBMS_WORKLOAD_REPOSITORY, DBMS_ADDM and the awrrpt.sql, ashrpt.sql and addmrpt.sql reports are in the pack; DBMS_SQLTUNE, DBMS_SQL_MONITOR, V$SQL_MONITOR and V$SQL_PLAN_MONITOR are in the Tuning Pack. On SE2 none of them can be licensed, so a script that reads them is a licensing finding, not a shortcut.

Two settings keep that from happening by accident. The parameter turns the pack functionality off in the server, and the usage view shows whether anything has touched a pack feature:

ALTER SYSTEM SET control_management_pack_access = 'NONE'
      SCOPE = BOTH;

SELECT name, detected_usages, currently_used,
       TO_CHAR(last_usage_date, 'YYYY-MM-DD') AS last_used
FROM   dba_feature_usage_statistics
WHERE  name LIKE '%Pack%'
OR     name LIKE 'Automatic Workload%'
OR     name LIKE 'SQL Monitoring%'
OR     name LIKE 'SQL Tuning%'
OR     name = 'ADDM'
ORDER  BY name;

A row with DETECTED_USAGES above zero on an SE2 database is a conversation with your license team. The same view is what Scops reads for its License page, which lists the packs and options each database has used and any new use in the last 90 days.

What you can still see, with views that are free

Nothing below needs a pack. The limit is not the data; it is that these views show now, or a running total, and the slow hour was two hours ago.

Right now: who is active and what they wait on

SELECT s.sid, s.serial#, s.username, s.sql_id,
       s.sql_plan_hash_value AS plan_hash, s.event,
       ROUND(s.wait_time_micro / 1e6) AS wait_s,
       s.blocking_session
FROM   v$session s
WHERE  s.status = 'ACTIVE'
AND    s.type = 'USER'
AND    s.wait_class <> 'Idle'
ORDER  BY s.wait_time_micro DESC;

Since startup: the statements with the most time

SELECT sql_id, plan_hash_value, executions,
       ROUND(elapsed_time / 1e6)                         AS elapsed_s,
       ROUND(elapsed_time / NULLIF(executions, 0) / 1e3) AS ms_exec,
       ROUND(cpu_time / 1e6)                             AS cpu_s,
       buffer_gets, disk_reads,
       SUBSTR(sql_text, 1, 60)                           AS sql_text
FROM   v$sqlstats
ORDER  BY elapsed_time DESC
FETCH FIRST 15 ROWS ONLY;

V$SQLSTATS is cheaper to read than V$SQL and survives the cursor. Its numbers are totals since the statement entered the shared pool, so a statement that ran badly this morning hides behind one that has run all month. The answer is two readings and a difference, which is what a monitoring tool does every few minutes.

The last hour: system metrics with one-minute history

SELECT TO_CHAR(end_time, 'HH24:MI') AS minute,
       metric_name, ROUND(value, 1) AS value
FROM   v$sysmetric_history
WHERE  group_id = 2
AND    metric_name IN ('Average Active Sessions',
                       'Database Time Per Sec',
                       'Host CPU Utilization (%)',
                       'User Commits Per Sec',
                       'Physical Reads Per Sec')
ORDER  BY end_time DESC, metric_name;

V$SYSMETRIC_HISTORY keeps about an hour of one-minute values. It tells you that the database was busy at 14:10, not who made it busy.

A session sampler you can build

ASH is a job that copies the active sessions into a table every second. Without the pack, you can do the same every ten seconds with a scheduler job, in a schema of your own:

CREATE TABLE mon.session_samples (
  sample_time   TIMESTAMP,
  sid           NUMBER,
  serial#       NUMBER,
  username      VARCHAR2(128),
  sql_id        VARCHAR2(13),
  plan_hash     NUMBER,
  event         VARCHAR2(64),
  wait_class    VARCHAR2(64),
  blocking_sid  NUMBER,
  module        VARCHAR2(64));

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MON.SAMPLE_SESSIONS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'INSERT INTO mon.session_samples
        SELECT SYSTIMESTAMP, sid, serial#, username, sql_id,
               sql_plan_hash_value, event, wait_class,
               blocking_session, module
        FROM   v$session
        WHERE  status = ''ACTIVE'' AND type = ''USER''
        AND    wait_class <> ''Idle'';
        COMMIT;',
    repeat_interval => 'FREQ=SECONDLY; INTERVAL=10',
    enabled         => TRUE);
END;
/

A week later, the slow hour has an answer:

SELECT sql_id, event, COUNT(*) AS samples,
       ROUND(COUNT(*) * 10 / 60) AS approx_minutes
FROM   mon.session_samples
WHERE  sample_time >= TIMESTAMP '2026-11-02 14:00:00'
AND    sample_time <  TIMESTAMP '2026-11-02 15:00:00'
GROUP  BY sql_id, event
ORDER  BY samples DESC
FETCH FIRST 10 ROWS ONLY;

It works, and it is what a monitoring tool does better: the sampler runs inside the database it watches, so it stops when the database hangs, its table grows without a partition to drop, and each database needs its own copy, its own purge job and its own reports. Statspack, still shipped in $ORACLE_HOME/rdbms/admin, is the older answer to the same problem: hourly snapshots of the V$ totals with top SQL by difference, on any edition, with no sessions in it.

What the usual tools give SE2

Oracle Enterprise Manager monitors an SE2 database with its base features, which are free with any Oracle license. The limit is in Enterprise Manager's licensing guide: base alerting "excludes notifications which requires one or more licensed Packs", and the Diagnostics Pack holds not only Performance Hub, ASH Analytics and AWR but also notifications, metric and alert history, blackouts, monitoring templates and adaptive thresholds. The same guide says the packs "can be purchased only with Enterprise Edition". On SE2, Enterprise Manager shows a threshold being crossed in the console and cannot mail anyone about it. Scops vs Oracle Enterprise Manager has the detail.

Tools that collect from outside can serve SE2 only if they read no pack view, and most of their documentation does not say. The Zabbix Oracle template states that it "queries the V$ACTIVE_SESSION_HISTORY dynamic performance view which is part of the Oracle Diagnostics Pack", so on SE2 that item has to go. Scops states in its docs that monitoring reads no pack view. The Oracle monitoring tools compared lists what each one states.

How Scops monitors Standard Edition 2 fully

Scops was built on the assumption that the packs are not there. It connects to each database through the listener with one account that needs CREATE SESSION and SELECT_CATALOG_ROLE, installs nothing on the server and creates nothing in the database. Then it collects what the packs would have given you, from the outside:

A license switch guards the one feature that uses Oracle's packs, so an SE2 database can never touch a pack by accident. Everything in monitoring, alerts, reports, Radar Engine and Scops AI works the same on SE2 and Enterprise Edition, and the Community edition is free for 2 databases.

Questions

Can I use AWR or ASH on Oracle Standard Edition 2?
No. The Diagnostics Pack cannot be licensed for Standard Edition 2, and Oracle's licensing manual lists V$ACTIVE_SESSION_HISTORY and the DBA_HIST views as part of that pack. Set CONTROL_MANAGEMENT_PACK_ACCESS to NONE so nothing uses them by accident.

Is Statspack still available on 19c and later?
Yes. The scripts ship in $ORACLE_HOME/rdbms/admin on every edition. Statspack gives snapshots of cumulative statistics and top SQL by difference; it has no session samples, so it cannot say who was active at a given minute.

Does Standard Edition 2 have SQL plan baselines?
Yes, in a restricted form: one baseline per statement and no plan evolution, with no pack needed. It is enough to pin the plan that worked.

What does Scops need on an SE2 database?
One Oracle account with CREATE SESSION and SELECT_CATALOG_ROLE, reached through the listener. No agent, no objects, no pack. Monitoring works the same as on Enterprise Edition.

Related

Sources