物化视图在这个视图里会有个MTAB$_%开头的表，可以关联查询下

select * FROM ALL_TABLES where TABLE_NAME like 'MTAB$_%'

with  a as (SELECT SUBSTR(TABLE_NAME, INSTR(TABLE_NAME, 'MTAB$_') + 6) AS view_name FROM ALL_TABLES  where TABLE_NAME  like 'MTAB$%_' )
select  '物化视图',* from  DBA_OBJECTS,a  where owner ='SYSDBA' and OBJECT_TYPE ='VIEW'   and OBJECT_NAME in  (a.view_name)
union 
select  '普通视图',* from  DBA_OBJECTS,a  where owner ='SYSDBA' and OBJECT_TYPE ='VIEW'   and OBJECT_NAME  not  in (a.view_name);

试试这个办法是否可行

SELECT SCH.NAME AS SCHEMA_NAME
      ,OBJ.NAME AS VIEW_NAME
      ,CASE WHEN OBJ.INFO1 & 0x0200 > 0 THEN 'MVIEW' ELSE 'VIEW' END AS VIEW_TYPE
  FROM SYSOBJECTS OBJ
      ,SYSOBJECTS SCH
 WHERE OBJ.TYPE$ = 'SCHOBJ'
   AND OBJ.SUBTYPE$ = 'VIEW'
   AND OBJ.SCHID = SCH.ID
   AND SCH.TYPE$ = 'SCH'