auto_indexes.sql

This feature in 19c  

Automatic Indexing autocreates indexes as invisible first and then tests the index impact against SQL statements. If the impact is positive—if the SQL statements perform better with an index—the index is made visible, as  if not, that index is marked unusable.

-- -----------------------------------------------------------------------------------
-- File Name    : auto_indexes.sql
-- Author       : Amir DBA
-- Description  : Displays auto indexes for the specified schema or all schemas.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @auto_indexes (schema-name or all)
-- -----------------------------------------------------------------------------------
SET VERIFY OFF LINESIZE 200

COLUMN owner FORMAT A30
COLUMN index_name FORMAT A30
COLUMN table_owner FORMAT A30
COLUMN table_name FORMAT A30

SELECT owner,
       index_name,
       index_type,
       table_owner,
       table_name
       table_type
FROM   dba_indexes
WHERE  owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'))
AND    auto = 'YES'
ORDER BY owner, index_name;