Oracle ORA-02030 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02030: Can Only Select from Fixed Tables/Views

ORA-02030 is an Oracle error that occurs when a user attempts to perform a DML operation (INSERT, UPDATE, or DELETE) on a fixed table or fixed view, such as X$ internal tables or V$ dynamic performance views. These objects are read-only, kernel-managed structures that reflect Oracle's internal memory state and cannot be modified by any user, including SYS. This error is commonly encountered by DBAs writing monitoring scripts or developers who mistake dynamic performance views for regular tables.

Top 3 Causes

1. Attempting DML on V$ or X$ Objects

The most frequent cause is trying to modify V$SESSION, V$SQL, or X$BH tables directly with INSERT, UPDATE, or DELETE statements.

-- ❌ This will trigger ORA-02030
UPDATE V$SESSION
SET STATUS = 'INACTIVE'
WHERE SID = 100;

-- ❌ This will also trigger ORA-02030
DELETE FROM V$SQL
WHERE LAST_ACTIVE_TIME < SYSDATE - 7;

-- ✅ Correct usage - SELECT only
SELECT SID, SERIAL#, USERNAME, STATUS, LAST_CALL_ET
FROM V$SESSION
WHERE STATUS = 'ACTIVE'
AND USERNAME IS NOT NULL;

2. Insufficient Privileges on Fixed Views

Regular users cannot access X$ tables directly, and V$ views require explicit SELECT grants via V_$ synonyms. Trying to access these objects without proper privileges can trigger ORA-02030 alongside ORA-01031.

-- Grant correct privileges (run as SYS)
GRANT SELECT ON V_$SESSION TO app_user;
GRANT SELECT ON V_$SQL TO app_user;

-- Alternatively, grant the catalog role
GRANT SELECT_CATALOG_ROLE TO app_user;

-- Verify current grants
SELECT GRANTEE, PRIVILEGE, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME IN ('V_$SESSION', 'V_$SQL')
AND GRANTEE = 'APP_USER';

3. Using Fixed Views as a Base for Writable Views

Developers sometimes create regular views or synonyms on top of V$ or X$ objects and then attempt DML through them. Since the underlying object is fixed, the error propagates regardless of the wrapper.

-- ❌ Creating a view on V$ and attempting DML still causes ORA-02030
CREATE OR REPLACE VIEW my_sessions AS
SELECT SID, SERIAL#, USERNAME, STATUS FROM V$SESSION;

-- This will fail
UPDATE my_sessions SET STATUS = 'DONE' WHERE SID = 1;

-- ✅ Solution: Use a regular snapshot table instead
CREATE TABLE session_snapshot AS
SELECT SID, SERIAL#, USERNAME, STATUS,
       MACHINE, SYSDATE AS SNAP_TIME
FROM V$SESSION WHERE 1=0;

INSERT INTO session_snapshot
SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE, SYSDATE
FROM V$SESSION WHERE USERNAME IS NOT NULL;
COMMIT;

-- Now DML works fine on the snapshot table
UPDATE session_snapshot
SET STATUS = 'REVIEWED'
WHERE SNAP_TIME < SYSDATE - 1;

Quick Fix Solutions

Replace DML with SELECT — Always use SELECT-only queries against V$ and X$ objects.
Grant correct privileges — Use GRANT SELECT ON V_$<view_name> TO <user> from SYS.
Use snapshot tables — Collect data periodically into regular tables using DBMS_SCHEDULER.

-- Identify if an object is a fixed table before writing scripts
SELECT NAME, TYPE FROM V$FIXED_TABLE
WHERE NAME = UPPER('&your_object_name');

Prevention Tips

Establish a coding standard: Document that V$ and X$ objects are strictly read-only. Add a pre-check in your scripts to verify object types before executing any DML.
Use AWR or custom snapshot tables: Instead of querying live fixed views repeatedly and risking incorrect DML, leverage Oracle's AWR (DBMS_WORKLOAD_REPOSITORY) or schedule periodic inserts into dedicated snapshot tables for historical analysis and reporting.

Related Errors

ORA-01031 – Insufficient privileges, often seen alongside ORA-02030 when accessing X$ tables without SYS rights.
ORA-00942 – Table or view does not exist, thrown when a non-privileged user tries to directly query X$ tables.
ORA-04063 – View has errors, related when a V$-based view becomes invalid after an upgrade.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.