List indexes which you named intentionally

Here’s a nice Oracle SQL script that lists indexes which you named on purpose.

SELECT index_name 
FROM all_indexes 
WHERE table_owner = 'SCHEME' --schema name
AND table_name = 'TABLE' --table name
AND index_name NOT LIKE 'SYS%';

if you comment out this part, this script lists all indexes.

AND index_name NOT LIKE 'SYS%';

Oracle prefixes “SYS_” constraints and indexes when u dont give names to them. That’s why we add the condition.

Find the tables referencing a specific table by foreign key

SELECT a.owner || '.' || a.table_name "Referenced Table", 
 b.owner || '.' || b.table_name "Referenced by", 
 b.constraint_name "Foreign Key", 
FROM all_constraints a, 
 all_constraints b 
WHERE b.constraint_type = 'R' 
AND a.constraint_name = b.r_constraint_name 
AND a.table_name = 'TABLE1' -- Table name
AND a.owner = b.r_owner