CREATE TABLE "SYSDBA"."T1"
(
"V1" INT NOT NULL,
"V2" CHAR(10),
NOT CLUSTER PRIMARY KEY("V1")) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE TABLE "SYSDBA"."T2"
(
"V2" INT NOT NULL,
NOT CLUSTER PRIMARY KEY("V2")) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE BITMAP INDEX "INX_01" ON "SYSDBA"."T1"( V1) FROM SYSDBA.T1,SYSDBA.T2 WHERE SYSDBA.T1.V1 = SYSDBA.T2.V2 STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE BITMAP INDEX "index_02" ON "SYSDBA"."T1"("V2" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE TABLE "SYSDBA"."T2"
(
"V2" INT NOT NULL,
NOT CLUSTER PRIMARY KEY("V2")) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE BITMAP INDEX "INX_01_14433_14435" ON "SYSDBA"."T2"( V1) FROM SYSDBA.T1,SYSDBA.T2 WHERE SYSDBA.T1.V1 = SYSDBA.T2.V2 STORAGE(ON "MAIN", CLUSTERBTR) ;
select OWNER,INDEX_NAME,INDEX_TYPE from (
select O1.NAME OWNER, O4.NAME INDEX_NAME,
CASE WHEN I.XTYPE & 0X00000002 = 0X00000002 THEN 'FUNCTION-BASED NORMAL' --'FBI'
WHEN I.XTYPE & 0X00001000 = 0X00001000 THEN 'BITMAP'
WHEN I.XTYPE & 0X00004000 = 0X00004000 THEN 'BITMAP JOIN VIRTUAL' --'BITMAP_JOIN_VIRTUAL'
WHEN I.XTYPE & 0X00002000 = 0X00002000 THEN 'BITMAP JOIN' --'BITMAP_JOIN'
WHEN I.XTYPE & 0X00000020 = 0X00000020 THEN 'FLAT'
WHEN I.XTYPE & 0X00000001 = 0x00000000 THEN 'CLUSTER'
WHEN I.FLAG & 0x00000002 = 0x00000002 THEN 'VIRTUAL'
ELSE
'NORMAL'
END INDEX_TYPE,I.XTYPE from SYS.SYSOBJECTS O1,
SYS.SYSOBJECTS O3,
SYS.SYSOBJECTS O4 LEFT JOIN SYS.SYSINDEXES I ON O4.ID = I.ID left join (SELECT DISTINCT * FROM SYS.V$TABLESPACE) TS ON TS.ID = I.GROUPID
WHERE O3.ID = O4.PID AND O1.ID = O3.SCHID
AND O4.SUBTYPE$ ='INDEX'
AND ((I.XTYPE & 0X00000001) <> 0 OR (O3.INFO3 & 0x3F) NOT IN (19, 24, 25, 26, 27, 28, 29)))
where (XTYPE & 0X00001000 = 0X00001000) or (XTYPE & 0X00004000 = 0X00004000) or (XTYPE & 0X00002000 = 0X00002000);
dba_indexes视图中暂时没对位图索引进行详细区分，可以通过自定义sql 进行区分。