How to find oracle calculated/computed column list using data dictionary views

oracle

I would like to find computed column list in oracle database using Oracle Data Dictionary Views.

I would like to add more information. Suppose I have following computed/calculated column in database.

ALTER TABLE HR.EMPLOYEES
 ADD FULL_NAME AS ( FIRST_NAME || ' ' || LAST_NAME);

I can select this column value in Select Statements.

SELECT EMPLOYEE_ID,FULL_NAME FROM HR.EMPLOYEES;

Results

…
174 Ellen Abel
…

I can see this column in TAB_COLUMNS view.

 select * from all_tab_columns   C 
 WHERE
 1 = 1
 AND  C.table_name = 'EMPLOYEES'
 AND C.OWNER = 'HR'
 AND COLUMN_NAME = 'FULL_NAME'
 ;

I would like to find given schema, table and column find that if this column is calculated/computed?

Wrongly I thought that INDEXES view gives me this information. But following select returns no rows. This gives only functional indexes. 

SELECT index_name,index_Type,I.*
FROM ALL_indexes I
WHERE 
1 = 1
AND INDEX_TYPE LIKE 'FUNCTION-BASED%'
AND I.OWNER = 'HR'

Best Answer

The column USER_TAB_COLS.virtual_column = 'YES' indicates if it's a virtual column.

You can get the expressions for each column with:

SELECT * 
FROM user_tab_columns
WHERE data_default IS NOT NULL;
Related Question