select * from 
(   
	select -- 排除默认隐藏聚集(ROWID)和系统索引
	     sf_get_schema_name_by_id(i.schid) as owner, -- 模式名
	     sf_get_tablename_by_id(pid) as table_name, -- 表名
	     i.name as index_name, -- 索引名
	     i.valid as is_valid, -- 索引有效性
	     indexdef(s.id, 1) as index_ddl -- 索引定义DDL语句
	from sysobjects i  -- index object
	join sysindexes s on s.id = i.id and i.subtype$='INDEX' -- index abstract
	where dm_bit_test(s.flag, 1) = 0 	
	
	UNION ALL	
	
	select  -- 主键索引(索引）、唯一约束（索引）
	     sf_get_schema_name_by_id(i.schid) as owner,
	     t.name as table_name,
	     i.name as index_name,
	     i.valid as is_valid,
	     convert(varchar, dbms_metadata.get_ddl('INDEX', i.name, sf_get_schema_name_by_id(i.schid))) as index_ddl
	from syscons c -- constraint outline
	join sysobjects i on c.indexid = i.id and i.subtype$='INDEX' and c.type$ in ('P', 'U')  -- constraint (pimary & unique)-- index object
	join sysobjects t on t.id = c.tableid and t.subtype$='UTAB' -- table object
)
-- 外层过滤条件，根据自身需求而定
where owner='SYSDBA'   -- 模式名
--and table_name='XXXXX' ; -- 表名 
order by 1,2;