DEV Community: SANDEEP KUMAR

Oracle SQL: Pseudo-Columns

SANDEEP KUMAR — Wed, 23 Sep 2026 03:03:55 +0000

1. Overview & Core Concepts

Definition: A pseudo-column behaves like a table column, but it is not actually stored on disk in the table.
Capabilities & Restrictions: You can SELECT from pseudo-columns, but you cannot perform INSERT, UPDATE, or DELETE operations on their values.

2. Common Oracle Pseudo-Columns

Oracle provides several built-in pseudo-columns for administrative, navigational, and sequence-based queries:

ROWID: Returns the unique physical address of a row within a database table.
ROWNUM: Assigns a sequential integer (starting from 1) to each row returned by a query result set.
NEXTVAL: Retrieves the next available value from a sequence object.
CURRVAL: Retrieves the current value of a sequence in the current session.
COLUMN_VALUE: Used primarily when querying collection types or table functions like XMLTABLE.
- *Example:*SQL
```
SELECT column_value
FROM (XMLTABLE('<a>123</a>'));
```
ORA_ROWSCN: Returns the conservative upper bound System Change Number (SCN) of the most recent change made to the row. Useful for tracking data staleness or building flashback implementations.
UID & USER: Returns the unique integer user ID and the username of the current user session.
- *Example Output from Dual Table:*SQL
```
SELECT uid, user FROM dual;
```
  UID USER
  
  102 HR
LEVEL: Used with the SELECT ... CONNECT BY hierarchical query clause to organize flat rows into a tree structure. It returns the current depth level of a node within the hierarchy.

UID	USER
102	HR

3. Hierarchical Queries & Tree Pseudo-Columns (`CONNECT BY`)

When working with hierarchical data (such as an employee-manager reporting line), Oracle provides specialized pseudo-columns to inspect tree structures:

Example: Hierarchical Query Structure

SQL

SELECT
    ename,
    empno,
    CONNECT_BY_ISLEAF,
    CONNECT_BY_ISCYCLE,
    LEVEL,
    SYS_CONNECT_BY_PATH(ename, '->')
FROM
    emp
CONNECT BY NOCYCLE
    PRIOR empno = mgr;

Explanation of Hierarchical Pseudo-Columns used in Example:

CONNECT_BY_ISLEAF: Returns 1 if the current row is a leaf node (has no children in the tree structure), and 0 otherwise.
CONNECT_BY_ISCYCLE: Returns 1 if the current row has a child that is an ancestor of itself (a loop in the hierarchy), and 0 otherwise. (Requires the NOCYCLE keyword in the CONNECT BY clause to avoid infinite loops).
SYS_CONNECT_BY_PATH(column, char): Returns the path of column values from the root to the current node, separated by the specified character string.

4. Advanced Interview Insights & Frequently Asked Questions

NOTE: Interviewers heavily test pseudo-columns, especially regarding execution order and limitations.

What is the evaluation order of ROWNUM in a query?

ROWNUM is assigned after the WHERE clause is evaluated, but before ORDER BY and GROUP BY clauses. This is why filtering with ROWNUM > 1 directly will always return zero rows (since row 1 fails the condition, it is discarded, and the next row becomes row 1). To handle pagination safely with ROWNUM, you must use an inline view or subquery that encapsulates the ORDER BY.

Can you create your own custom pseudo-columns?

No, pseudo-columns are system-defined by Oracle. However, virtual columns (introduced in Oracle 11g) allow you to define custom expression-based columns that are stored logically or physically as metadata.

What is the difference between ROWID and ROWNUM?

ROWID is a permanent, unique physical address of a row on disk (stable across transactions until the row is deleted or moved). ROWNUM is temporary, dynamic, and assigned on-the-fly to rows as they are fetched into the result set during statement execution.

Can you create an index on a ROWNUM or ROWID pseudocolumn?

Answer: You cannot index ROWNUM, but ROWID acts as the implicit physical primary address of every row and is inherently indexed via the rowid access path. You can, however, create indexes on Virtual Columns.

What is the purpose of CONNECT_BY_ISCYCLE?

Answer: When performing hierarchical queries with loops in the data, CONNECT_BY_ISCYCLE returns 1 if the current row has a child that is also its ancestor (a loop), preventing infinite loops when paired with the NOCYCLE clause.

IMPORTANT NOTES FOR INTERVIEW:

Virtual columns do not consume storage space for data (unlike regular columns), but metadata is stored in the dictionary, and they can be indexed to improve query performance.

ROWNUM is assigned before any sorting or aggregation happens in a query. If you use ORDER BY with ROWNUM, rows are numbered arbitrarily unless wrapped in a subquery.

💡 Practical Code Example: Hierarchical Queries with Pseudocolumns

SQL

SELECT
    ename,
    empno,
    CONNECT_BY_ISLEAF,
    CONNECT_BY_ISCYCLE,
    LEVEL,
    SYS_CONNECT_BY_PATH(ename, '->') AS hierarchy_path
FROM emp
CONNECT BY NOCYCLE PRIOR empno = mgr;

⚖️ Difference: Pseudocolumn vs. Virtual Column

Feature	Pseudocolumn	Virtual Column
Table Storage	Not part of the table definition/storage.	Physically part of the table metadata structure, though values are derived on-the-fly.
Value Derivation	Generated dynamically by the query engine (e.g., `ROWNUM`, `LEVEL`).	Derived from expressions/other columns of the same table.
Indexing	Cannot be indexed directly.	Can be indexed (including B-tree and function-based indexes).
DML Operations	Selection only (No INSERT/UPDATE/DELETE).	Selection only; values are managed automatically by the database via expressions (No direct DML on the virtual column).

🏗️ Table Structure Example: Virtual Columns

CREATE TABLE Test (
    a   NUMBER,
    b   NUMBER,
    SUM AS (a + b),  -- Virtual Column
    Sub AS (a - b),  -- Virtual Column
    Mul AS (a * b),  -- Virtual Column
    Div AS (a / b)   -- Virtual Column
);

Oracle SQL: Read-Only Tables

SANDEEP KUMAR — Wed, 23 Sep 2026 02:52:55 +0000

1. Overview & Core Concepts

Definition: Oracle allows you to restrict tables to read-only mode, blocking all Data Modification Language (DML) operations (such as INSERT, UPDATE, DELETE) and certain Data Definition Language (DDL) modifications while permitting SELECT queries.
Primary Use Cases: Protecting reference data, historical archives, compliance data retention, and locking master tables during maintenance or migration windows.

2. Methods to Make a Table Read-Only or Restrict Data Modifications

There are multiple approaches in Oracle to enforce read-only behavior on table data, ranging from native table states to permissions, views, and triggers.

Method 1: Using Native Table Read-Only State (`ALTER TABLE ... READ ONLY`)

This is the cleanest, built-in feature introduced in Oracle to directly set a table's state to read-only.

Syntax:SQL
```
ALTER TABLE emp READ ONLY;
```
Reverting to Read-Write:SQL
```
ALTER TABLE emp READ WRITE;
```
Behavior & Error Handling:
- If you attempt to insert or update records on a read-only table, Oracle throws a direct error:
  - SQL Error: ORA-12081: update operation not allowed on table "EMP"

Method 2: Granting Restricted Privileges Across Schemas

You can isolate the master table in a dedicated schema (Schema1 / User-A) and grant only SELECT privileges to other users (Schema2 / User-B).

Setup & Granting Privilege:SQL

-- Connected as Schema1 (User-A)
GRANT SELECT ON emp TO user_b;

Behavior from User-B's Schema:SQL
```
-- Connected as Schema2 (User-B)
INSERT INTO user_a.emp VALUES (..., ...);
```
- Error Output: ORA-01031: insufficient privileges (or general insufficient privileges error).

Method 3 & 4: Creating Views (Standard or Read-Only Views)

You can revoke direct DML access to the base table and expose data strictly via a view.
Starting in Oracle, you can explicitly create a read-only view to block DML directly through the view
```
CREATE OR REPLACE VIEW emp_ro_view AS
SELECT * FROM emp
WITH READ ONLY;
```

Method 5: Using Statement-Level Triggers

You can implement a database trigger to intercept and block any DML transactions at the statement level.

Syntax & Example:SQL

CREATE OR REPLACE TRIGGER tr_block_dml
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Table is read-only. DML operations are not allowed.');
END;
/

3. Summary Comparison of Read-Only Approaches

Method	Pros	Cons / Limitations
`ALTER TABLE READ ONLY`	Native, high-performance, dictionary-enforced, prevents accidental DML by table owners/admins without switching mode.	Requires exclusive table access lock to change state.
Schema Privileges	Clean separation of duties; non-owners cannot write.	Owner of the schema can still perform DML.
Read-Only Views (`WITH READ ONLY`)	Granular column/row filtering while keeping base table secured.	Users with direct table access can still modify the base table.
Triggers	Highly customizable; can include conditional logic (e.g., allow specific users).	Overhead on every DML statement; can be disabled by users with `ALTER TRIGGER` privileges.

4. Advanced Interview Insights & Frequently Asked Questions

NOTE: Interviewers frequently probe into the metadata behavior and edge cases of read-only tables.

Can you drop or truncate a read-only table?

No. DDL operations that modify the table structure or contents (like DROP TABLE or TRUNCATE TABLE) are blocked when a table is in READ ONLY mode. You must first alter the table back to READ WRITE (ALTER TABLE tab_name READ WRITE;) before dropping or truncating.

Can indexes be created or rebuilt on a read-only table?

Dropping or creating indexes is generally restricted while the table is read-only because it modifies the table's segment metadata.

How can you verify if a table is currently in read-only mode?

You can query the READ_ONLY column from data dictionary views like USER_TABLES, ALL_TABLES, or DBA_TABLES

    ```
    SELECT table_name, read_only
    FROM user_tables
    WHERE table_name = 'EMP';
    ```

Oracle SQL: Unused Columns & Virtual Columns

SANDEEP KUMAR — Wed, 23 Sep 2026 02:50:36 +0000

1. Unused Columns (`SET UNUSED`)

Overview & Concept

Definition: Marking a column as UNUSED is a form of logical deletion. The column metadata is hidden, but the physical data remains untouched in the table blocks.
Performance Benefit: Unlike a traditional DROP COLUMN (which locks the table and rewrites every data block to reclaim space immediately), SET UNUSED is an instant metadata-only operation. This prevents long locks and heavy I/O overhead on large production tables.

Key Characteristics

Metadata Hiding: Once a column is marked as unused, it cannot be viewed via the DESC command or standard SELECT * queries.
Reusing Column Names: Because Oracle internally renames unused columns in the data dictionary with system-generated names (e.g., SYS_00001_...), you can immediately create a new column with the same name on the table.
Reversibility: You cannot directly "un-set" an unused column using a simple undo command. To reclaim space permanently, the column must eventually be dropped.

Core SQL Operations & Examples

1. Mark a Column as Unused:SQL

ALTER TABLE cust_details
SET UNUSED (cust_account_number

2. View Unused Columns:SQL

To inspect unused columns in a table, query data dictionary views such as DBA_UNUSED_COL_TABS, USER_UNUSED_COLS, or use session settings:
```
SET COLINVISIBLE ON;
DESC cust_details;
*(Alternatively, query `USER_UNUSED_COLS` to see table names and counts of unused columns).*
```
3. Drop All Unused Columns Permanently:SQL

This command physically removes the data and reclaims disk space. It can be resource-intensive on large tables:
```
ALTER TABLE cust_details
DROP UNUSED COLUMNS;
```

2. Virtual Columns

Overview & Concept

Definition: A virtual column is a derived column defined in a table with an underlying expression that computes values from other physical columns in the same row.
Storage Behavior: Virtual columns do not store data physically on disk by default. Instead, the database evaluates the expression dynamically at runtime. (Note: Oracle also supports STORED virtual columns if persistence is required for indexing or performance).

Rules, Limitations, and Restrictions

DML Restrictions: You cannot perform direct DML operations (INSERT, UPDATE, DELETE) on virtual columns. Their values are managed entirely by the underlying expression.
Table Type Support: Virtual columns can only be defined on Heap-organized tables. They are not supported for external tables, Index-Organized Tables (IOT), clustered tables, or temporary tables.
Dependencies: A virtual column cannot reference another virtual column; it must reference physical columns within the same table.
Data Type: The expression for a virtual column must evaluate to a scalar data type only.
Partitioning: Virtual columns can be used as partition keys (e.g., partitioning a table by a year extracted from a date column).

Indexing Virtual Columns

You can define indexes on virtual columns.
These indexes behave identically to Function-Based Indexes, allowing the optimizer to pre-calculate and index the results of the virtual column expression for faster retrieval.

SQL Syntax & Example

SQL

CREATE TABLE t1 (
    id   NUMBER,
    col1 NUMBER,
    col2 NUMBER,
    col3 NUMBER GENERATED ALWAYS AS (col1 * col2) VIRTUAL
);

3. Senior Architect & Interview Masterclass

What is the difference between DROP COLUMN and SET UNUSED?
- DROP COLUMN immediately removes the column definition and physically purges the data from all blocks, which can cause severe locking and performance degradation on large tables.
- SET UNUSED instantly updates the data dictionary metadata, marking the column as dead without touching the physical blocks. Space reclamation is deferred until ALTER TABLE ... DROP UNUSED COLUMNS is executed manually (often scheduled during maintenance windows)[cite: 5].
Can you add a primary key constraint or unique constraint on a Virtual Column?
- Yes! If the underlying expression guarantees uniqueness or non-null behavior, you can add constraints (like NOT NULL or UNIQUE) and create indexes on virtual columns, making them extremely powerful for enforcing business rules without duplicating storage.
What happens to indexes when you mark a column as UNUSED?
- If the column was part of any indexes, those indexes become unusable/invalid and must be dropped or rebuilt after dropping the unused columns permanently.

DEV Community: SANDEEP KUMAR

Oracle SQL: Pseudo-Columns

1. Overview & Core Concepts

2. Common Oracle Pseudo-Columns

3. Hierarchical Queries & Tree Pseudo-Columns (CONNECT BY)

Example: Hierarchical Query Structure

Explanation of Hierarchical Pseudo-Columns used in Example:

4. Advanced Interview Insights & Frequently Asked Questions

💡 Practical Code Example: Hierarchical Queries with Pseudocolumns

⚖️ Difference: Pseudocolumn vs. Virtual Column

🏗️ Table Structure Example: Virtual Columns

Oracle SQL: Read-Only Tables

1. Overview & Core Concepts

2. Methods to Make a Table Read-Only or Restrict Data Modifications

Method 1: Using Native Table Read-Only State (ALTER TABLE ... READ ONLY)

Method 2: Granting Restricted Privileges Across Schemas

Method 3 & 4: Creating Views (Standard or Read-Only Views)

Method 5: Using Statement-Level Triggers

3. Summary Comparison of Read-Only Approaches

4. Advanced Interview Insights & Frequently Asked Questions

Oracle SQL: Unused Columns & Virtual Columns

1. Unused Columns (SET UNUSED)

Overview & Concept

Key Characteristics

Core SQL Operations & Examples

2. Virtual Columns

Overview & Concept

Rules, Limitations, and Restrictions

Indexing Virtual Columns

SQL Syntax & Example

3. Senior Architect & Interview Masterclass

3. Hierarchical Queries & Tree Pseudo-Columns (`CONNECT BY`)

Method 1: Using Native Table Read-Only State (`ALTER TABLE ... READ ONLY`)

1. Unused Columns (`SET UNUSED`)