New e-book: Oracle APEX 26.1 – AI that survives an audit

Which parts of your Oracle application are actually used? The queries to run before you scope a modernisation

Wojciech Bielawski

Head of Growth

  • November 18, 2026
12 min read

Contents

Every modernisation estimate starts with an inventory: so many screens, so many reports, so many PL/SQL packages, so many tables. The inventory is usually accurate. What it rarely says is which of those things anyone still uses. In a system that has grown for fifteen or twenty years, that is the number that matters, because every module you carry over has to be analysed, rebuilt, tested, migrated and then maintained, whether or not anyone opens it.

You do not need a new tool to start answering the question. Oracle Database and Oracle APEX already record a great deal about what runs, what changes and who opens what. This article covers seven sources of usage evidence, with the queries to read them, the licensing traps to avoid and the mistakes that turn a “dead” module into an incident after go-live. The examples assume an application schema called APP; replace it with yours.

Before you run anything: licences, time windows and snapshots

Check the Diagnostics Pack position first. AWR and ASH, including V$ACTIVE_SESSION_HISTORY and the DBA_HIST_ views, belong to the Oracle Diagnostics Pack, an extra-cost option for Enterprise Edition. Oracle’s licensing information is explicit that any way of using the pack, including direct access to the underlying data, requires a Diagnostics Pack licence. The trap is that the CONTROL_MANAGEMENT_PACK_ACCESS parameter defaults to DIAGNOSTIC+TUNING on Enterprise Edition, so the queries run whether or not you are licensed. On Standard Edition 2 the pack is not offered at all, and STATSPACK is the usual alternative for history.

SELECT value
FROM   v$parameter
WHERE  name = 'control_management_pack_access';

The same licensing information names six DBA_HIST_ views that can be used without the pack: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ and DBA_HIST_UNDOSTAT. Everything else in this article uses views outside the pack. If you want to try the AWR-based variants safely, Oracle Database Free includes the Diagnostics Pack, according to the Database Licensing Information User Manual.

Plan the time window. Most of the sources below describe a recent period only: cursors age out of the shared pool, instance statistics reset at restart, DBA_TAB_MODIFICATIONS counts only since statistics were last gathered, and the APEX activity log keeps 14 days by default. A module that runs at month-end, at quarter-end or for the annual report will look dead in a quiet week. Collect evidence across at least one month-end and one quarter-end, and list year-end processes separately.

Snapshot what disappears. For sources that live in memory, copy what you need into your own table on a schedule:

CREATE TABLE usage_sql_snap AS
  SELECT SYSDATE AS snap_time, parsing_schema_name, module, action,
         sql_id, executions, last_active_time
  FROM   v$sqlarea
  WHERE  1 = 0;

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'USAGE_SQL_SNAP_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[BEGIN
                            INSERT INTO usage_sql_snap
                            SELECT SYSDATE, parsing_schema_name, module, action,
                                   sql_id, executions, last_active_time
                            FROM   v$sqlarea
                            WHERE  parsing_schema_name = 'APP';
                            COMMIT;
                          END;]',
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE);
END;
/

The job owner needs the SELECT privilege on SYS.V_$SQLAREA, the view behind the public synonym V$SQLAREA; grant it to that user directly.

1. Which SQL runs

V$SQLAREA holds every statement currently in the shared pool, with the number of executions totalled over its child cursors. Grouped by module, it shows which parts of the application generate work. MODULE and ACTION are set by the application through DBMS_APPLICATION_INFO; APEX and many frameworks set them, while older clients often do not.

SELECT NVL(module, '(not set)') AS module,
       COUNT(*)                 AS statements,
       SUM(executions)          AS executions,
       MAX(last_active_time)    AS last_active
FROM   v$sqlarea
WHERE  parsing_schema_name = 'APP'
GROUP  BY NVL(module, '(not set)')
ORDER  BY executions DESC;

2. Which tables that SQL touches

Execution plans in V$SQL_PLAN show which tables the cached statements read. The query below maps index access back to the table, so a table read only through its indexes still counts as used, and then lists the tables that no cached statement touches:

WITH used AS (
  SELECT p.object_name AS table_name
  FROM   v$sql_plan p
  JOIN   dba_tables t
         ON t.owner      = p.object_owner
        AND t.table_name = p.object_name
  WHERE  p.object_owner = 'APP'
  UNION
  SELECT i.table_name
  FROM   v$sql_plan p
  JOIN   dba_indexes i
         ON i.owner      = p.object_owner
        AND i.index_name = p.object_name
  WHERE  i.table_owner = 'APP'
)
SELECT t.table_name, t.num_rows, t.last_analyzed
FROM   dba_tables t
WHERE  t.owner = 'APP'
AND    t.table_name NOT IN (SELECT table_name FROM used)
ORDER  BY t.num_rows DESC NULLS LAST;

One run shows only what is in memory at that moment, so schedule it as well and keep the results. A table that never appears across a full business cycle is a candidate, not a verdict.

3. Which segments are read and written

V$SEGMENT_STATISTICS counts logical reads and block changes for each segment since the instance started. If the instance has been up across a month-end and a table still shows no logical reads, nobody read it in that period.

SELECT s.object_name,
       SUM(CASE WHEN s.statistic_name = 'logical reads'    THEN s.value END) AS logical_reads,
       SUM(CASE WHEN s.statistic_name = 'db block changes' THEN s.value END) AS block_changes
FROM   v$segment_statistics s
WHERE  s.owner = 'APP'
AND    s.object_type IN ('TABLE', 'TABLE PARTITION')
GROUP  BY s.object_name
ORDER  BY logical_reads NULLS FIRST;

SELECT startup_time FROM v$instance;

Segment statistics need STATISTICS_LEVEL set to TYPICAL, the default, or ALL; BASIC switches them off. The statistic names used in the query are listed in V$SEGSTAT_NAME. If your database keeps AWR snapshots, DBA_HIST_SEG_STAT and DBA_HIST_SEG_STAT_OBJ give the same figures per snapshot over a longer history, and Oracle’s licensing information lists both among the views that do not need the Diagnostics Pack.

4. Which tables change

DBA_TAB_MODIFICATIONS records approximate numbers of inserts, updates and deletes for each table since statistics were last gathered. First flush the in-memory monitoring counters to the dictionary with DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO:

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT m.table_name, m.inserts, m.updates, m.deletes,
       m.timestamp AS last_change, t.last_analyzed
FROM   dba_tab_modifications m
JOIN   dba_tables t
       ON t.owner      = m.table_owner
      AND t.table_name = m.table_name
WHERE  m.table_owner = 'APP'
AND    m.partition_name IS NULL
ORDER  BY m.timestamp;

The window starts at LAST_ANALYZED, so a table analysed yesterday tells you almost nothing. The view also sees only writes: reference and lookup tables are read all the time and rarely change, so they look dead here. Use it together with the read evidence from steps 2 and 3.

5. Which PL/SQL is called

Start with static dependencies. This query lists packages, procedures and functions that no other database object references:

SELECT o.object_name, o.object_type, o.last_ddl_time
FROM   dba_objects o
WHERE  o.owner = 'APP'
AND    o.object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION')
AND    NOT EXISTS (
         SELECT 1
         FROM   dba_dependencies d
         WHERE  d.referenced_owner = o.owner
         AND    d.referenced_name  = o.object_name
         AND    d.referenced_type  = o.object_type
         AND    NOT (d.owner = o.owner AND d.name = o.object_name))
ORDER  BY o.object_type, o.object_name;

Treat the result with care. DBA_DEPENDENCIES sees only references inside the database. Calls from APEX pages, from Java or .NET code, from ETL tools and from schedulers outside the database do not appear in it. For APEX, search the source of page processes and computations in the APEX dictionary views, such as APEX_APPLICATION_PAGE_PROC, for the package names.

To go below package level, use PL/Scope. PLSCOPE_SETTINGS defaults to IDENTIFIERS:NONE, so nothing is collected until you switch it on and recompile. Do this on a copy of the database rather than in production, because recompiling a schema invalidates dependent objects and package state in running sessions:

ALTER SESSION SET plscope_settings = 'IDENTIFIERS:ALL';
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'APP', compile_all => TRUE);

SELECT d.object_name, d.object_type, d.name AS subprogram, d.type
FROM   dba_identifiers d
WHERE  d.owner = 'APP'
AND    d.usage = 'DEFINITION'
AND    d.type IN ('PROCEDURE', 'FUNCTION')
AND    NOT EXISTS (
         SELECT 1
         FROM   dba_identifiers c
         WHERE  c.signature = d.signature
         AND    c.usage     = 'CALL')
ORDER  BY 1, 3;

COMPILE_SCHEMA adopts the current session settings by default, so the schema is recompiled with PL/Scope switched on. A subprogram’s declaration, definition and calls share one signature, which is what the query matches on. PL/Scope finds subprograms that no compiled code calls; it collects nothing for wrapped units, and dynamic SQL and callers outside the database still need the runtime evidence from the next step.

6. Confirming a suspect: unified auditing

Once steps 1–5 have produced a shortlist, confirm it with a narrow audit policy that records reads of the suspect tables and use of the suspect packages:

CREATE AUDIT POLICY usage_check_pol
  ACTIONS SELECT  ON app.rate_history,
          SELECT  ON app.old_invoices,
          EXECUTE ON app.legacy_report_pkg;

AUDIT POLICY usage_check_pol;

-- after the observation period
SELECT object_name, action_name, dbusername,
       COUNT(*)             AS events,
       MAX(event_timestamp) AS last_seen
FROM   unified_audit_trail
WHERE  unified_audit_policies LIKE '%USAGE_CHECK_POL%'
GROUP  BY object_name, action_name, dbusername;

NOAUDIT POLICY usage_check_pol;

Keep the list short. Every audited access writes a record, so a policy on a busy table adds storage and some overhead. For a package, Oracle records EXECUTE when the package is instantiated in a session, not on every call, so read those counts as sessions that used it (CREATE AUDIT POLICY). Unified auditing works in Standard Edition 2 as well as Enterprise Edition; fine-grained auditing (DBMS_FGA), which can record access to particular columns or rows, is not available on Standard Edition 2. A suspect that shows no events across a full business cycle moves from “probably unused” to evidence you can put in front of its business owner.

7. Which APEX pages people open

For APEX applications, the activity log records a row for every page view, with the user and the time. Before you rely on it, extend its retention. By default APEX switches between two log tables every 14 days, so the views show between 14 and 28 days of history; the maximum interval is 180 days. As an instance administrator:

BEGIN
  APEX_INSTANCE_ADMIN.SET_LOG_SWITCH_INTERVAL(
    p_log_name              => 'ACTIVITY',
    p_log_switch_after_days => 180);
  COMMIT;
END;
/

Then count page views per page in APEX_ACTIVITY_LOG, the view Oracle documents for custom activity reports. It covers the current workspace and also records developer activity, so leave developer accounts out of the count:

SELECT flow_id                  AS application_id,
       step_id                  AS page_id,
       MAX(component_attribute) AS page_title,
       COUNT(*)                 AS page_views,
       COUNT(DISTINCT userid)   AS users,
       MAX(time_stamp)          AS last_view
FROM   apex_activity_log
WHERE  flow_id = 100
GROUP  BY flow_id, step_id
ORDER  BY page_views, step_id;

Pages missing from the result were not opened during the log window; compare the list with the application’s pages in App Builder. Logging is switched on or off per application, so check that it is enabled for the application you measure. The two log tables rotate, so to keep a longer history, copy the rows into your own table on a schedule, as Oracle’s documentation recommends.

Reading the results without fooling yourself

  • Batch jobs. Check DBA_SCHEDULER_JOBS and any external scheduler: a module can be used every night by a job and by no person at all.
  • The calendar. Regulatory reports, audits and annual closes may run once a year. Record when each candidate was last used, not only whether it was used this month.
  • Other consumers. ETL processes, BI tools, interfaces and other databases read tables directly. Sample V$SESSION (PROGRAM, MACHINE and MODULE) during the observation period to see who connects.
  • Shared code. A package that one live path calls stays alive in full, so decide at the level of procedures, not packages.
  • Unused is not the same as unneeded. Data nobody reads may still have to be kept for legal or audit reasons. That decision is “retain and archive”, not “delete”.

Then go through the list with the business owner of each module. Evidence turns the conversation from “we might need it” into “nobody has opened it since March”.

From evidence to decisions

What the evidence shows Typical decision
No reads, writes or calls across a full business cycle Retire; archive the data if retention rules require it
Used only by a yearly or regulatory process Retain, or replace with a simpler report; do not rebuild the user interface
Used heavily and stable Retain, or modernise with as little change as possible
Used heavily, changed often and expensive to maintain Modernise or replace first

This is the usage part of the evidence we gather before a modernisation is scoped, alongside the code and the business process. Oracle Database features in 69 of Pretius’s 145 documented projects, and on Oracle estates this evidence is cheap to collect, because the data is already in the database. It also changes how a rebuild is planned: Sweco’s platform was rebuilt module by module, and our article on phased legacy modernisation describes how to sequence that kind of work. If you would like a second pair of eyes on your usage evidence before the scope is fixed, book a discovery call.

Looking for a software development company?

Work with a team that already helped dozens of market leaders. Book a discovery call to see:

  • How our products work
  • How you can save time & costs
  • How we’re different from other solutions

footer-contact-steps

We keep your data safe: ISO certified

We operate in accordance with the ISO 27001 standard, ensuring the highest level of security for your data.
certified dekra 27001
© 2026 Pretius. All rights reserved.