Oracle SQL for showing index information

select index_name, rtrim(xmlagg(xmlelement(c,column_name || ',')).extract ('//text()'), ',') columns
from all_ind_columns
where table_name = 'F0101' and owner = 'TESTDTA' group by index_name

The above is going to show you all the columns in the indexes for F0101.  The rows are going to look like:

F0101_7
ABAC01,ABDC,ABAC03

F0101_8
ABAC01,ABDC,ABAC02

F0101_9
ABAC01,ABDC

 

Which is very cool

But I only want the definition of the unique indexes for the tables that I list, so that SQL gets a little more bespoke:

 

select   t1.index_name, rtrim(xmlagg(xmlelement(c,t1.column_name || ',')).extract ('//text()'), ',') columns
from all_ind_columns t1, all_indexes t2
where t1.table_name = 'F0101'
and t1.table_owner = 'TESTDTA'
and t2.table_owner = 'TESTDTA'
and t1.index_name = t2.index_name
and t2.uniqueness = 'UNIQUE'
group by t1.index_name

So above is the SQL to show me the unique indexes.