Q:

Indexes in Oracle

Can you tell us how to find out which indexes are fragmented in Oracle?  Is there a script to get a list of fragmented indexes? 
And what should be done with such indexes to get performance improvement? Should they be rebuilt? 

A:

I'm not sure what you mean by a "fragmented" index, but in general there is almost never any need to rebuild indexes in Oracle - at least, not to improve performance.
See this article by Jonathan Lewis on when to rebuild indexes, and Tom Kyte's views here - quote:

My opinion -- 99.9% of all reorgs, rebuilds, etc are a total and utter
  waste of time and  energy.  We spend way way way too much time losing
  sleep over this non-event.

