In my data integration work, I’ve done a lot with Oracle databases lately. In getting familiar with a data layout, it helps to see foreign keys, search for columns with the same name, or find stored procedures that reference a specified object. Here are some queries I’ve put together to return this type of information.
I’ve used *Name* as a stand in for the object name, and *Owner* for the object owner name.

Dependency_Type: HARD or REF
— Find references to an object

SELECT DISTINCT Owner, Name, Type, Dependency_Type
FROM ALL_DEPENDENCIES
WHERE LOWER(Referenced_Name) LIKE '*Name*'
  AND REFERENCED_OWNER = '*Owner*'
ORDER BY Owner, Name, Type;

— Gives object name, object type and line
— Find specified text within DB objects

SELECT *
FROM ALL_source
WHERE LOWER(text) LIKE '%*Name*%'
AND Owner = '*Owner*' 
AND Type = 'PROCEDURE';

— Query for column names in all tables (case sensitive)

SELECT Owner, Table_Name, Column_Name
FROM ALL_TAB_COLS
WHERE LOWER(Column_Name) LIKE '*Name*'
ORDER BY Owner, Table_Name;

— For for a name over all objects

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, OBJECT_ID, DATA_OBJECT_ID
FROM ALL_OBJECTS
WHERE OWNER = '*Owner*'
AND OBJECT_NAME = '*Name*';

— Look for a specific index

SELECT OWNER, Index_Name, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE
FROM ALL_INDEXES
WHERE Index_Name = '*Name*'