Displays all PL/SQL code objects

To displays all PL/SQL code objects created by user OE that reference any table or view owned by other users except SYS and SYSTEM, use this code

   
SELECT 
owner || '.' || NAME refs_table
, referenced_owner || '.' || referenced_name AS table_referenced
FROM all_dependencies
WHERE owner = USER
AND TYPE IN ('PACKAGE', 'PACKAGE BODY','PROCEDURE', 'FUNCTION')
AND referenced_type IN ('TABLE', 'VIEW')
AND referenced_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, NAME, referenced_owner, referenced_name;