Who uses this Index ?

 TL;DR: One more way to "monitor an index". Checking v$sql_plan can get you to the relevant SQL, tell you what component runs the query, and give you a lot of details. Read, experiment, and choose the way you want Your monitoring done.

There are multiple ways to find out if an index is useful or not, and they all have pro- and con- arguments.

Firstly, a very blunt way is to make the index invisible, or even drop it, and just see what happens. This method has a downside: it can impact users hard+fast. 

Secondly, the official documentation will provide you with DBA_INDEX_USAGE, and a very good article on this by Tim -oracle-base-dot-com Hall is found Here (Do Read It!). This 2nd method is less intrusive, more subtle, but it is still more or less a Yes/No answer to the question. It does not give you an indication of how-many users/session/queries/users are hitting this index, or how-often.

We are going to add yet another method: also not perfect, but maybe more suitable for some use-cases. Read on if you like to Dig In Deep....

Take a look at v$sql_plan and at the AWR and Statspack equivalents: DBA_HIST_SQL_PLAN and STAT$_SQL_PLAN. If you filter by object_owner, object_name and object_type, you can see which SQL-statements have used your index in their plan, both in sql-cache and in AWR/Statspack.

This view stores the "explained plan" of the SQL that resides in the shared_pool, and is also used by dbms_xplan.display_cursur and by (auto-)explain to display the explain-plans.

At this moment, I am interested in any SQL that uses my index. And I can obtain the list of SQL_IDs like this: 

select p.sql_id

from v$sql_plan p

where  p.object_owner = 'SCOTT'

and    p.object_type  = 'INDEX' ;

and    p.object_name  like 'EMP%'/* idx name(s) here */ 

;

From the nr of sql_ids we already get an idea of how many and how often this index is used. 

To make sure you dont miss occurrences, you should also check the DBA_HIST and/or the STAT$ equivalent because not all SQL-stmtns will remain in the sql-cache and be visible via the v$ views.

Once you have the set of SQL_IDs, you can link them to v$sql or v$sqlarea to find out how often each of them is used, and by which users or programs. I tend to use something like this:

select a.sql_id

     , a.executions

     , a.parsing_schema_name

     , substr ( a.sql_text, 1, 25 ) as sqltxt

from v$sql_plan p

   , v$sqlarea a

where p.object_owner = 'SCOTT'

and   p.object_type  = 'INDEX'

and   p.object_name  like 'EMP%'  /* name(s) here...*/

and   a.sql_id = p.sql_id;

For even more detail, you can also check to see if the SQL_IDs occur in the v$active_session_history views: those will tell you which sessions, e.g. which programs/user/jobs have been running the queries that used your index.

By doing this little extra digging, you may obtain a much better view of who + when + what is using your index(es).

I would encourage you to use each of the methods described, and to "do your own digging", and then to make an informed decision on whether an index can be dropped or not.

As I've often done, I'll make the point again: All this information is available to you in Views and Tables, and can be viewed with just SQL, and you are free to use the tool of your choice: SQLDeveloper, SQLcl, SQL-Plus, or any other adhoc-query tool at your disposal. 

Everything is SQL, and SQL is Everything.

Go Experiment, Go Learn and Enjoy !