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.
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.
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;
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.
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.
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.
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.
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.
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.
DBA_SCHEDULER_JOBS and any external scheduler: a module can be used every night by a job and by no person at all.V$SESSION (PROGRAM, MACHINE and MODULE) during the observation period to see who connects.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”.
| 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.