How to Log Every Executed Oracle SQL Statement with v$sql and Automated Jobs

Background

A production Oracle database exhibited an unexpected NULL value in a numeric column. After reviewing all application code, the team decided to audit every SQL statement executed by the database to locate the source of the problem.

Solution 1 – Direct Query of v$sql

The dynamic performance view v$sql (and its related views v$sqlarea and v$sqltext) contains the text of recently executed statements, execution counts, and timestamps. A simple query such as:

SELECT sql_id, sql_text, last_active_time, executions
FROM v$sql
WHERE last_active_time > SYSDATE - 1/24;  -- last hour

can reveal the offending statements. However, the data is stored in memory, is cleared on instance restart, and cannot be migrated to a new server, making it unsuitable for long‑term forensic analysis.

Solution 2 – Automated Persistent Logging

To retain a permanent record of every SQL statement, the article proposes creating a dedicated logging infrastructure and scheduling a PL/SQL job that copies the text from v$sql into a persistent table every ten minutes.

Step 1 – Create a Tablespace for the Log

CREATE TABLESPACE sql_log_ts
  DATAFILE '/u01/app/oracle/oradata/ORCL/sql_log_ts01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Step 2 – Create the Log Table

CREATE TABLE sqltextlog (
  log_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  sql_id        VARCHAR2(13),
  sql_text      CLOB,
  captured_at   TIMESTAMP DEFAULT SYSTIMESTAMP,
  executions    NUMBER
) TABLESPACE sql_log_ts;

Step 3 – Stored Procedure to Populate the Table

CREATE OR REPLACE PROCEDURE PRO_SQLTextLog IS
BEGIN
  INSERT INTO sqltextlog (sql_id, sql_text, executions)
  SELECT sql_id,
         sql_fulltext,
         executions
  FROM v$sql;
  COMMIT;
END PRO_SQLTextLog;
/

The procedure extracts the current contents of v$sql and stores each statement’s identifier, full text, and execution count.

Step 4 – Grant Access to v$sql

The user that runs the procedure must have SELECT privilege on the view:

GRANT SELECT ON v$sql TO logging_user;

Step 5 – Schedule the Job

Using the DBMS_SCHEDULER package (or the older DBMS_JOB), create a job that invokes the procedure every ten minutes. Example with DBMS_SCHEDULER:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_LOG_SQL_TEXT',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PRO_SQLTextLog',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',
    enabled         => TRUE,
    comments        => 'Persist SQL statements to sqltextlog every 10 minutes');
END;
/

If you prefer the legacy DBMS_JOB interface, the script must be executed in sqlplus (or PL/SQL Developer) and terminated with a slash ( /) rather than a semicolon.

Step 6 – Monitor Job Execution

Job status can be inspected with:

SELECT job_name, status, last_start_date, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'JOB_LOG_SQL_TEXT';

When the job is active, every SQL statement executed on the database is automatically inserted into sqltextlog. The table can later be queried to trace the origin of the erroneous NULL assignment, e.g.:

SELECT * FROM sqltextlog
WHERE sql_text LIKE '%YOUR_TABLE_NAME%'
ORDER BY captured_at DESC;

Key Considerations

The log table can grow quickly; implement a retention policy (e.g., purge rows older than 30 days) to manage space.

Granting SELECT on v$sql gives visibility into all statements, which may include sensitive data; restrict the logging user’s privileges accordingly.

Because v$sql stores only the most recent statements, the job interval should be short enough to avoid missing transient queries.