DBA_TAB_COLUMNS - Query Oracle Column Metadata & Types

DBA_TAB_COLUMNS

Overview

DBA_TAB_COLUMNS describes every column of every table, view, and cluster in the database. It is the primary source for column-level metadata: data type, length, precision, scale, nullability, default value, and optimizer statistics such as the number of distinct values and low/high value estimates. DBAs and developers query it constantly when auditing schema changes, validating ETL mappings, building dynamic SQL, or investigating data type mismatches between systems.

The USER_ and ALL_ equivalents (USER_TAB_COLUMNS, ALL_TAB_COLUMNS) restrict the result set to the current user’s own objects or objects accessible to the current user respectively. DBA_TAB_COLUMNS requires DBA privileges or SELECT ANY DICTIONARY to query.

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

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the table or view
TABLE_NAME	VARCHAR2(128)	Name of the table or view
COLUMN_NAME	VARCHAR2(128)	Name of the column
COLUMN_ID	NUMBER	Ordinal position of the column within the table (1-based)
DATA_TYPE	VARCHAR2(128)	Data type: NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, etc.
DATA_LENGTH	NUMBER	Maximum length in bytes for character and RAW types
DATA_PRECISION	NUMBER	Decimal precision for NUMBER columns; NULL for other types
DATA_SCALE	NUMBER	Digits to the right of the decimal for NUMBER columns
NULLABLE	CHAR(1)	Y if the column allows NULLs; N if it has a NOT NULL constraint
DATA_DEFAULT	LONG	Default value expression as stored in the data dictionary
NUM_DISTINCT	NUMBER	Optimizer statistic: estimated number of distinct values
LOW_VALUE	RAW(32)	Optimizer statistic: lowest column value (encoded)
HIGH_VALUE	RAW(32)	Optimizer statistic: highest column value (encoded)
DENSITY	NUMBER	Optimizer statistic: selectivity estimate for equality predicates
NUM_NULLS	NUMBER	Optimizer statistic: estimated number of NULL values
AVG_COL_LEN	NUMBER	Average length of the column value in bytes
LAST_ANALYZED	DATE	Date and time column statistics were last gathered
CHAR_USED	CHAR(1)	B = byte semantics, C = character semantics (for VARCHAR2/CHAR)
VIRTUAL_COLUMN	VARCHAR2(3)	YES if this is a virtual (computed) column; NO otherwise
HIDDEN_COLUMN	VARCHAR2(3)	YES if this is a hidden system-generated column

Essential Queries

Basic Usage

List all columns for a specific table, ordered by position:

SELECT
    column_id,
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale,
    nullable,
    data_default
FROM
    dba_tab_columns
WHERE
    owner      = 'HR'
    AND table_name = 'EMPLOYEES'
ORDER BY
    column_id;

Monitoring Query

Find all NOT NULL columns across a schema that have no statistics gathered, which can indicate missing DBMS_STATS coverage:

SELECT
    owner,
    table_name,
    column_name,
    data_type,
    last_analyzed
FROM
    dba_tab_columns
WHERE
    owner        = 'HR'
    AND nullable     = 'N'
    AND last_analyzed IS NULL
ORDER BY
    table_name,
    column_id;

Combined with Other Views

Join DBA_TAB_COLUMNS with DBA_CONSTRAINTS and DBA_CONS_COLUMNS to show which columns are part of primary key, unique, or foreign key constraints:

SELECT
    tc.table_name,
    tc.column_name,
    tc.data_type,
    tc.nullable,
    LISTAGG(cc.constraint_name || '(' || c.constraint_type || ')', ', ')
        WITHIN GROUP (ORDER BY cc.constraint_name)  AS constraints
FROM
    dba_tab_columns  tc
    LEFT JOIN dba_cons_columns cc ON cc.owner       = tc.owner
                                  AND cc.table_name  = tc.table_name
                                  AND cc.column_name = tc.column_name
    LEFT JOIN dba_constraints  c  ON c.owner         = cc.owner
                                  AND c.constraint_name = cc.constraint_name
                                  AND c.constraint_type IN ('P', 'U', 'R')
WHERE
    tc.owner = 'HR'
    AND tc.table_name = 'EMPLOYEES'
GROUP BY
    tc.table_name,
    tc.column_id,
    tc.column_name,
    tc.data_type,
    tc.nullable
ORDER BY
    tc.column_id;

Stale or Missing Statistics

Identify columns with stale statistics (analyzed more than 7 days ago) in tables that are being actively used — a common source of suboptimal execution plans:

SELECT
    tc.owner,
    tc.table_name,
    COUNT(*)                                          AS total_columns,
    SUM(CASE WHEN tc.last_analyzed IS NULL THEN 1 ELSE 0 END) AS unanalyzed,
    SUM(CASE WHEN tc.last_analyzed < SYSDATE - 7
             AND tc.last_analyzed IS NOT NULL
        THEN 1 ELSE 0 END)                            AS stale_7d,
    MAX(tc.last_analyzed)                             AS most_recent_analysis
FROM
    dba_tab_columns tc
WHERE
    tc.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN')
GROUP BY
    tc.owner,
    tc.table_name
HAVING
    SUM(CASE WHEN tc.last_analyzed IS NULL THEN 1 ELSE 0 END) > 0
    OR SUM(CASE WHEN tc.last_analyzed < SYSDATE - 7 THEN 1 ELSE 0 END) > 0
ORDER BY
    unanalyzed DESC,
    stale_7d DESC;

Schema Comparison

Compare column definitions between two schemas (e.g., production vs. test) to identify differences:

SELECT
    COALESCE(p.table_name, t.table_name)    AS table_name,
    COALESCE(p.column_name, t.column_name)  AS column_name,
    p.data_type                             AS prod_type,
    t.data_type                             AS test_type,
    p.data_length                           AS prod_len,
    t.data_length                           AS test_len,
    p.nullable                              AS prod_null,
    t.nullable                              AS test_null
FROM
    (SELECT table_name, column_name, data_type, data_length, nullable
     FROM dba_tab_columns WHERE owner = 'PROD_SCHEMA') p
    FULL OUTER JOIN
    (SELECT table_name, column_name, data_type, data_length, nullable
     FROM dba_tab_columns WHERE owner = 'TEST_SCHEMA') t
    ON  t.table_name  = p.table_name
    AND t.column_name = p.column_name
WHERE
    p.data_type  != t.data_type
    OR p.data_length != t.data_length
    OR p.nullable    != t.nullable
    OR p.table_name  IS NULL
    OR t.table_name  IS NULL
ORDER BY
    table_name, column_name;

Common Use Cases

• ETL mapping validation — Confirm that source and target column data types and lengths are compatible before loading data, avoiding implicit conversion errors
• Schema documentation — Generate data dictionaries by querying column names, types, nullability, and default values for all tables in a schema
• Finding VARCHAR2 overflow risk — Filter for columns where AVG_COL_LEN is close to DATA_LENGTH to identify columns at risk of hitting the length limit
• Locating columns by data type — Search across all schemas for CLOB or LONG columns before a migration to flag objects requiring special handling
• Virtual column auditing — Filter VIRTUAL_COLUMN = ‘YES’ to inventory computed columns before upgrading, as not all tools handle them correctly
• Statistics freshness monitoring — Identify tables with missing or stale column statistics that may be causing the optimizer to generate inefficient execution plans

Related Views

• DBA_TABLES — Parent view for table-level metadata; join on OWNER and TABLE_NAME
• DBA_IND_COLUMNS — Shows which columns are indexed; cross-reference with DBA_TAB_COLUMNS to find unindexed NOT NULL columns
• DBA_CONSTRAINTS — Join to identify constraint types applied to each column
• DBA_COL_COMMENTS — Stores developer-supplied comments for each column; join on OWNER, TABLE_NAME, COLUMN_NAME
• DBA_TAB_COL_STATISTICS — Extended column statistics including histograms; more detail than the statistics columns in DBA_TAB_COLUMNS

Version Notes

• Oracle 9i: Extended precision and scale support for TIMESTAMP types added to the view
• Oracle 11g: VIRTUAL_COLUMN and HIDDEN_COLUMN columns added to distinguish computed and system-generated columns
• Oracle 12c: IDENTITY_COLUMN column added (YES/NO) for columns defined with GENERATED AS IDENTITY; EVALUATION_EDITION column added for edition-based redefinition
• Oracle 19c: Extended statistics (column groups) reflected via DBA_TAB_COL_STATISTICS; DBA_TAB_COLUMNS itself unchanged structurally
• Oracle 23ai: Domain column support — columns associated with SQL Domains may show additional metadata via the DOMAIN_NAME and DOMAIN_OWNER columns in extended catalog views