How to find out virtual index from database?

Virtual index can be created in oracle database which doesn't has any physical body and location. It can create with NOSEGMENT clause for testing purpose.

SQL> create table test11 (a number,b number);

Table created.

SQL> create index v_test11 on test11(a) nosegment;

Index created.

SQL> select index_name,owner from dba_indexes 
where index_name='V_TEST11' and owner='SYS';

no rows selected

SQL> select index_owner,index_name,column_name,table_name from dba_ind_columns
2 where index_owner='SYS' and index_name='V_TEST11';

INDEX_OWNER INDEX_NAME COLUMN_NAME TABLE_NAME
SYS V_TEST11 A TEST11

SQL> select index_name from dba_ind_columns
2 minus
3 select index_name from dba_indexes;

INDEX_NAME
------------------------------
AAA_V
V_T1_A
V_TEST11
V_TEST1_A

SQL> select owner,object_id
2 from dba_objects
3 where object_name='V_TEST11';

OWNER OBJECT_ID
------------------------------ ----------
SYS 7718

SQL> select owner,object_id,object_type,status,temporary from dba_objects
2 where object_name='V_TEST11';

OWNER OBJECT_ID OBJECT_TYPE STATUS T
------------------------------ ---------- ------------------ ------- -
SYS 7718 INDEX VALID N

SQL> create index test11_b on test11(b);

Index created.

SQL>select object_name,object_id,object_type from dba_objects where object_type='INDEX' and owner='SYS' and object_name like '%TEST%'

OBJECT_NAM OBJECT_ID OBJECT_TYPE
---------- ---------- ------------------
TEST11_B 7730 INDEX
V_TEST11 7718 INDEX

SQL> select obj#,ts#,file#,block#,type#,flags,property from ind$ where obj# in (7718,7730);

OBJ# TS# FILE# BLOCK# TYPE# FLAGS PROPERTY
7730 0 1 15832 1 0 0
7718 0 0 0 1 4096 0
 

Above query shows that in the IND$ flags of virtual index is 4096 and for other index is 0. That means we can find out the VIRTUAL INDEX FROM following queries ONLY.

SQL> select index_name from dba_ind_columns
2 minus
3 select index_name from dba_indexes;
AND
SQL> select obj#,ts#,file#,block#,type#,flags,property from ind$ where flags=4096