Tracking Ghost Tables in Oracle

While identifying idle tables occupying space in an Oracle database might seem straightforward, the decision to remove them requires a rigorous verification process. To find the answer to the question, 'When was this table last accessed?', it is essential to correctly read the database's history and memory.

In this article, we will step-by-step examine the techniques we use to track down tables that have been forgotten for months or even years. We will begin our investigation with the method that offers the most reliable proof.

If the Audit mechanism is enabled, this is one of the most guaranteed methods to check tables.

SHOW PARAMETER audit_trail;

SHOW PARAMETER audit_trail;

With the following query, you can query the DBA_AUDIT_TRAIL view to see if any read (SELECT) or write (DML) operations have been performed on the tables in the past and whether they were logged.

SELECT
    username AS kullanici,
    action_name AS islem_tipi,
    obj_name AS tablo_adi,
    timestamp AS islem_zamani
FROM
    dba_audit_trail
WHERE
    owner = 'SCHEMA_NAME'
    AND obj_name IN ('X_BCK', 'Y_BCK')
ORDER BY
    timestamp DESC;

SELECT
    username AS kullanici,
    action_name AS islem_tipi,
    obj_name AS tablo_adi,
    timestamp AS islem_zamani
FROM
    dba_audit_trail
WHERE
    owner = 'SCHEMA_NAME'
    AND obj_name IN ('X_BCK', 'Y_BCK')
ORDER BY
    timestamp DESC;

Checking Unified Auditing

Oracle 12c and later versions have moved towards "Unified Auditing." If your database is running in this mode, traditional queries on DBA_AUDIT_TRAIL might return incomplete data or remain empty. Even if SHOW PARAMETER audit_trail returns DB, your database might still be configured for Unified Auditing. Therefore, checking the UNIFIED_AUDIT_TRAIL is essential for a comprehensive analysis.

Check if Unified Auditing is enabled:

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

If you cannot find anything in the Audit logs, we can search for SQL queries containing the names of these tables in the database's instant memory (Shared Pool). This will show us who executed what recently (provided the server has not been restarted or the queries have not been aged out of the memory)

SELECT
    sql_id,
    sql_text AS calisan_sorgu,
    first_load_time AS ilk_calisma_zamani,
    last_active_time AS son_calisma_zamani,
    executions AS calistirilma_sayisi
FROM
    v$sql
WHERE
    upper(sql_text) LIKE '%X_BCK%'
    OR upper(sql_text) LIKE '%Y_BCK%'
ORDER BY
    last_active_time DESC;

SELECT
    sql_id,
    sql_text AS calisan_sorgu,
    first_load_time AS ilk_calisma_zamani,
    last_active_time AS son_calisma_zamani,
    executions AS calistirilma_sayisi
FROM
    v$sql
WHERE
    upper(sql_text) LIKE '%X_BCK%'
    OR upper(sql_text) LIKE '%Y_BCK%'
ORDER BY
    last_active_time DESC;

You can find older queries that have been aged out of the memory in the AWR (Automatic Workload Repository) tables. How old the data in these tables will be depends on the AWR retention period.

To find out the AWR Retention Period:

SELECT MIN(begin_interval_time) oldest_snapshot,
    MAX(end_interval_time) newest_snapshot
FROM dba_hist_snapshot;

SELECT MIN(begin_interval_time) oldest_snapshot,
    MAX(end_interval_time) newest_snapshot
FROM dba_hist_snapshot;

To find the tables from AWR:

SELECT
    h.sample_time AS calisma_zamani,
    s.sql_text AS sorgu,
    h.user_id
FROM
    dba_hist_active_sess_history h
JOIN
    dba_hist_sqltext s ON h.sql_id = s.sql_id
WHERE
    upper(s.sql_text) LIKE '%X_BCK%'
    OR upper(s.sql_text) LIKE '%Y_BCK%'
ORDER BY
    h.sample_time DESC;

SELECT
    h.sample_time AS calisma_zamani,
    s.sql_text AS sorgu,
    h.user_id
FROM
    dba_hist_active_sess_history h
JOIN
    dba_hist_sqltext s ON h.sql_id = s.sql_id
WHERE
    upper(s.sql_text) LIKE '%X_BCK%'
    OR upper(s.sql_text) LIKE '%Y_BCK%'
ORDER BY
    h.sample_time DESC;

Finding the Last Time the Data in the Table Changed (ORA_ROWSCN)

You cannot see data reads (SELECT) from the table, but you can approximate when a row in the table was last updated or inserted (INSERT/UPDATE) by converting the SCN (System Change Number) in the data blocks to a timestamp.

SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) AS son_veri_degisiklik_zamani FROM SCHEMA_NAME.X_BCK;

SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) AS son_veri_degisiklik_zamani FROM SCHEMA_NAME.X_BCK;

Oracle does not store the mapping between SCN (System Change Number) values and actual timestamps forever. This mapping data is kept in the database's SYS.SMON_SCN_TIME table and is stored retroactively for approximately 5 days (120 hours) by default. If the MAX(ORA_ROWSCN) value of the table is even further back than this 5-day window (or a window of a few weeks depending on the database load), Oracle cannot resolve which date this SCN corresponds to and throws this error.

Limiting ORA_ROWSCN to Find the Approximate Time

If the SCN mapping of the table has been completely erased, the following query will also throw an error. However, by at least finding the oldest SCN date kept by the database, you can prove that the table is older than that date.

-- Let's find the oldest time the database can currently resolve
SELECT MIN(time_dp) AS en_eski_cozulebilen_zaman FROM sys.smon_scn_time;

-- Let's find the oldest time the database can currently resolve
SELECT MIN(time_dp) AS en_eski_cozulebilen_zaman FROM sys.smon_scn_time;

What Does This Output Tell Us?

The oldest record the database holds for the SCN-Time mapping is June 9, 2026. If the SCHEMA_NAME.X_BCK table had seen a DML (INSERT/UPDATE/DELETE) operation after this date, the SCN_TO_TIMESTAMP function would run without error and return a pinpoint date.

Since this function threw ORA-08181, we can make the following deduction with 100% certainty:

Conclusion: No data has been written to or updated in the SCHEMA_NAME.X_BCK table for at least 6 days. (Since it is at the edge of the retroactive window, this period is actually much longer; it has likely been dormant for months).

Checking for Dependencies

Even if you have proven that a table has been idle for months, it is crucial to perform a dependency check before dropping it. The table might be referenced by other database objects such as Views, Procedures, Functions, or Triggers. Dropping a table that has active dependencies will render these objects "INVALID" and cause application errors.

You can use the DBA_DEPENDENCIES view to check if any objects reference your tables:

SELECT
    owner AS bagimli_obje_sahibi,
    name AS bagimli_obje_adi,
    type AS bagimli_obje_tipi
FROM
    dba_dependencies
WHERE
    referenced_owner = 'SCHEMA_NAME'
    AND referenced_name IN ('X_BCK', 'Y_BCK')
    AND referenced_type = 'TABLE';

SELECT
    owner AS bagimli_obje_sahibi,
    name AS bagimli_obje_adi,
    type AS bagimli_obje_tipi
FROM
    dba_dependencies
WHERE
    referenced_owner = 'SCHEMA_NAME'
    AND referenced_name IN ('X_BCK', 'Y_BCK')
    AND referenced_type = 'TABLE';

Critical Warning: Check Before You Drop!

Even if you have proven that a table has not received any data modifications, do not execute the DROP command before checking its dependencies. A table might not receive new data, but it could still be actively queried (SELECT) by a reporting view or a critical business procedure. Running the query above will protect you from potential system outages and ensure that you are not breaking any hidden logic. If the query returns no results, you can be confident that the table is not referenced by any other objects within the database.