Oracle 23ai (and the upcoming 26ai) introduced Materialized Generated Columns (using the syntax GENERATED ALWAYS AS (...) MATERIALIZED). Unlike traditional virtual columns, these columns are physically stored on disk and updated automatically whenever the dependent columns change.

However, this creates a metadata challenge. If you query standard dictionary views like USER_TAB_COLS, both a column with a standard DEFAULT value and a MATERIALIZED generated column show up with VIRTUAL_COLUMN = 'NO' and their expression/value populated in DATA_DEFAULT. This makes them look identical on the surface.

In this article, we will explore two robust ways to distinguish them: a high-performance DBA method using SYS.COL$, and a developer-friendly method using DBMS_METADATA.

The Problem Illustrated

Consider the following table definition:

CREATE TABLE T (  
    C1 NUMBER,  
    C2 NUMBER DEFAULT 2,   
    C3 NUMBER GENERATED ALWAYS AS (3) VIRTUAL,   
    C4 NUMBER GENERATED ALWAYS AS (4) MATERIALIZED  
);

If you query USER_TAB_COLS:

SELECT COLUMN_NAME, VIRTUAL_COLUMN, DATA_DEFAULT   
FROM USER_TAB_COLS  
WHERE TABLE_NAME = 'T';

Both C2 and C4 return VIRTUAL_COLUMN = 'NO', and both have their expressions stored in DATA_DEFAULT. Here is how you can tell them apart.

---

Solution 1: The High-Performance DBA Approach (Querying SYS.COL$)

If you have access to the data dictionary base tables (or have SELECT_CATALOG_ROLE / DBA privileges), you can inspect the PROPERTY column of SYS.COL$. Oracle uses specific bitwise flags to store column properties:

  • Virtual Column: Bit 0x00000008 (Decimal 8)
  • Standard Default Value: Bit 0x00000020 (Decimal 32)
  • Materialized Generated Column: Bit 0x800000000 (Decimal 34,359,738,368)

You can use BITAND to decode these properties cleanly:

SELECT c.name AS column_name,
       CASE 
         WHEN BITAND(c.property, 8) = 8 THEN 'VIRTUAL'
         WHEN BITAND(c.property, 34359738368) = 34359738368 THEN 'MATERIALIZED'
         WHEN BITAND(c.property, 32) = 32 THEN 'DEFAULT'
         ELSE 'STANDARD'
       END AS column_behavior
FROM sys.col$ c
JOIN sys.obj$ o ON c.obj# = o.obj#
JOIN sys.user$ u ON o.owner# = u.user#
WHERE o.name = 'T'
  AND u.name = sys_context('USERENV', 'CURRENT_SCHEMA');

Output:

COLUMN_NAME COLUMN_BEHAVIOR
C1 STANDARD
C2 DEFAULT
C3 VIRTUAL
C4 MATERIALIZED
---

Solution 2: The Developer Approach (Using DBMS_METADATA XML)

If you do not have privileges to query SYS.COL$, you can parse the XML representation of the table definition using DBMS_METADATA.GET_XML. This view is safe to run as any schema owner.

Oracle's XML metadata tags materialized columns with a specific <PROPERTY> value or explicitly marks the generation expression. You can parse this directly using XMLTABLE:

SELECT xt.column_name,
       CASE 
         WHEN xt.is_virtual = 1 THEN 'VIRTUAL'
         WHEN xt.is_materialized = 1 THEN 'MATERIALIZED'
         WHEN xt.has_default = 1 THEN 'DEFAULT'
         ELSE 'STANDARD'
       END AS column_behavior
FROM xmltable(
  '/ROW/TABLE/COL_LIST/COL_LIST_ITEM'
  passing xmltype(dbms_metadata.get_xml('TABLE', 'T'))
  columns 
    column_name varchar2(128) path 'NAME',
    is_virtual number path 'count(VIR_COL_EXPR)',
    is_materialized number path 'count(GEN_COL_EXPR)',
    has_default number path 'count(DEFAULT_VAL)'
) xt;

Why this works:

  • VIR_COL_EXPR is only present for purely virtual columns (like C3).
  • GEN_COL_EXPR is present when a column is expression-generated but physically stored (like C4).
  • DEFAULT_VAL is present for standard default value constraints (like C2).
---

Summary

While Oracle's standard USER_TAB_COLS view hasn't yet been updated to explicitly expose a MATERIALIZED_COLUMN flag, you can easily bypass this limitation. Use the SYS.COL$ bitwise query for high-performance scripting, or the DBMS_METADATA XML parsing approach for application deployment where SYS-level access is unavailable.