List all computed columns in Oracle database

Query below lists all computed columns in Oracle database.

Query

select owner as table_schema,
       table_name,
       column_name,
       data_type,
       data_default as generation_expression
from sys.all_tab_cols
where virtual_column != 'NO'
      and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN', 
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST',
      'WKSYS','WKPROXY','WMSYS','XDB','APEX_040000','APEX_PUBLIC_USER',
      'DIP','FLOWS_30000','FLOWS_FILES','MDDATA','ORACLE_OCM','XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
      and data_default is not null
order by table_schema,
         table_name,
         column_name;

Columns

  • table_schema - schema name containing table
  • table_name - table name
  • column_id - id of column in table
  • column_name - name of the column
  • data_type - data type of column
  • generation_expression - computing formula

Rows

  • One row represents one column
  • Scope of rows: represents all computed columns from all databases
  • Ordered by schema name, table name and column name

Sample results

0
There are no comments. Click here to write the first comment.