Oracle Table Usage Across Stored Procedures

If I understand this correctly, you're trying to search for occurrence of a table in all stored procs. In that case, you can use this query:

When searching for occurrences of SP in your schema

SELECT * FROM user_source WHERE text LIKE '%tab_name%';

When searching for occurrences of SP in all schemas

SELECT * FROM all_source WHERE text LIKE '%tab_name%';

Two things, in PL/SQL there are some changes which will require the recompilation of pl/sql object, other don't. To see the first one, you have the ALL_DEPENDENCIES view. Or DBA_ if you prefer.

If you just want to see where the table name appears in all the pl/sql code, whether a change to the table will require recompilation or not, you can use ALL_SOURCE using a upper and %, but it might take some time.

I use PLSQL Developer, in which you can browse to a table (or other object), and view 'Referenced by', to see all objects that refer to the table. That's about as easy as it gets. I can imagine other tools have similar features.

I don't know if this pre-parsed information is readily available in Oracle, but I can imagine so, since those tools seem to work pretty fast.

This information is available in the viewAll_DEPENDENCIES, which these tools probably use.

The source of stored procedures can be found in the USER_SOURCE (or ALL_SOURCE) view, in which the structure of the entire database is stored. Nevertheless, fetching and parsing the code from there would be quite cumbersome.