DBA_VIEWS - Query Oracle View Definitions & Text

DBA_VIEWS

Overview

DBA_VIEWS describes every view in the database, including the full SQL text used to define it, its compilation status, and whether it uses read-only or check-option constraints. It is the authoritative source for view source code in the database and is consulted whenever DBAs need to audit view definitions, investigate invalid views after a schema change, extract DDL programmatically, or understand the dependency chain before modifying an underlying table.

Because the TEXT column is declared as LONG, some tools may require conversion to CLOB before it can be manipulated in SQL. Oracle provides TO_LOB() for this conversion in newer releases. The view does not expose execution plans; for that, rely on EXPLAIN PLAN on the view’s query directly.

View Type: Static Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_VIEWS, SELECT_CATALOG_ROLE, or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the view
VIEW_NAME	VARCHAR2(128)	Name of the view
TEXT_LENGTH	NUMBER	Length of the view’s defining SQL text in characters
TEXT	LONG	Full SQL text of the view’s SELECT statement
TEXT_VC	VARCHAR2(4000)	First 4000 characters of TEXT as a VARCHAR2, available from Oracle 12c
TYPE_TEXT_LENGTH	NUMBER	Length of the type clause for typed views; NULL for regular views
TYPE_TEXT	VARCHAR2(4000)	Type clause for typed (object-type) views
OID_TEXT_LENGTH	NUMBER	Length of the OID clause for typed views
OID_TEXT	VARCHAR2(4000)	WITH OBJECT IDENTIFIER clause for typed views
VIEW_TYPE_OWNER	VARCHAR2(128)	Owner of the type for typed views
VIEW_TYPE	VARCHAR2(128)	Type name for typed views
SUPERVIEW_NAME	VARCHAR2(128)	Name of the superview if this view is a subview in an object-view hierarchy
EDITIONING_VIEW	VARCHAR2(1)	Y if this is an editioning view (used in Edition-Based Redefinition)
READ_ONLY	VARCHAR2(1)	Y if the view was created WITH READ ONLY

Essential Queries

Basic Usage

Retrieve the definition of a specific view:

SELECT
    owner,
    view_name,
    text_length,
    text_vc
FROM
    dba_views
WHERE
    owner     = 'HR'
    AND view_name = 'EMP_DETAILS_VIEW'
ORDER BY
    view_name;

Monitoring Query

Find all invalid views across the database — typically caused by an underlying table or referenced object being dropped, renamed, or altered:

SELECT
    o.owner,
    o.object_name   AS view_name,
    o.status,
    o.last_ddl_time,
    v.text_length
FROM
    dba_objects o
    JOIN dba_views v ON v.owner     = o.owner
                     AND v.view_name = o.object_name
WHERE
    o.object_type = 'VIEW'
    AND o.status      = 'INVALID'
    AND o.owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
ORDER BY
    o.owner,
    o.object_name;

Combined with Other Views

Use DBA_DEPENDENCIES to find all views that depend on a specific table, so you can assess the impact before modifying the table:

SELECT
    d.owner,
    d.name          AS view_name,
    d.referenced_owner,
    d.referenced_name AS depends_on,
    d.referenced_type,
    o.status        AS view_status
FROM
    dba_dependencies d
    JOIN dba_objects o ON o.owner       = d.owner
                       AND o.object_name = d.name
                       AND o.object_type = 'VIEW'
WHERE
    d.referenced_owner = 'HR'
    AND d.referenced_name  = 'EMPLOYEES'
    AND d.referenced_type  = 'TABLE'
    AND d.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY
    d.owner,
    d.name;

Large View Inventory

Identify the largest view definitions by text length, which can indicate overly complex views that may be candidates for refactoring or materializing:

SELECT
    owner,
    view_name,
    text_length,
    ROUND(text_length / 1024, 1)    AS text_kb,
    read_only,
    editioning_view
FROM
    dba_views
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORACLE_OCM')
ORDER BY
    text_length DESC
FETCH FIRST 20 ROWS ONLY;

View Search by Keyword

Search for views whose definition references a particular table or column name (using TEXT_VC for the first 4000 characters):

SELECT
    owner,
    view_name,
    text_length
FROM
    dba_views
WHERE
    UPPER(text_vc) LIKE '%SALARY%'
    AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY
    owner,
    view_name;

Common Use Cases

• Impact analysis before DDL — Query DBA_DEPENDENCIES joined with DBA_VIEWS to find all views that will become invalid if a table column is dropped or renamed
• Invalid view remediation — After a schema change, identify all invalid views and recompile them with DBMS_UTILITY.COMPILE_SCHEMA or individual ALTER VIEW … COMPILE statements
• DDL extraction for migration — Retrieve TEXT for all views in a schema to reconstruct the view layer on a target database without relying on Data Pump
• Read-only view audit — Filter READ_ONLY = ‘Y’ to confirm which views enforce read-only access, especially important in data warehousing schemas
• Editioning view discovery — Filter EDITIONING_VIEW = ‘Y’ to identify views used in Edition-Based Redefinition, which require special handling during upgrades
• View complexity monitoring — Track TEXT_LENGTH over time to detect views that are growing excessively complex, which can degrade optimizer plan stability

Related Views

• DBA_OBJECTS — Contains STATUS (VALID/INVALID) and LAST_DDL_TIME for every view; join on OWNER and VIEW_NAME
• DBA_TAB_COLUMNS — Describes the columns of a view as the database sees them after compilation
• DBA_SYNONYMS — Views are often accessed via synonyms; join to trace synonym → view → table chains
• DBA_DEPENDENCIES — Maps the full dependency graph between views and their underlying tables, other views, and PL/SQL objects
• DBA_UPDATABLE_COLUMNS — Identifies which columns of a view are updatable via DML, complementing the READ_ONLY flag in DBA_VIEWS

Version Notes

• Oracle 9i: Typed view columns (VIEW_TYPE_OWNER, VIEW_TYPE) added for object-relational views
• Oracle 11g: EDITIONING_VIEW column added to support Edition-Based Redefinition introduced in 11g Release 2
• Oracle 12c: TEXT_VC column added as a VARCHAR2(4000) convenience column to avoid LONG datatype handling in SQL; widely used in modern queries
• Oracle 19c: No structural changes; view behavior with APPROX_COUNT_DISTINCT and other approximate query functions is reflected in execution plans, not in DBA_VIEWS
• Oracle 23ai: Views created over SQL Domains or using new JSON-Relational Duality syntax appear normally in DBA_VIEWS; JSON Duality Views have a distinct object type in DBA_OBJECTS (DUALITY VIEW)