... The task at hand is to identify potential redundant procedures or functions across different packages. ...
that seems like a "whacky" way - I don't know of anything other than documenting what you have to determine that. Just because I use emp and dept and you use emp and dept and she uses emp and dept - doesn't mean we are doing the same thing.
And further, it would seem to me that it would miss almost everything. Say all three of us use EMP and DEPT - but we also use dozens of other things - all mutually exclusive from eachother (all my object dependencies start with 'A', yours with 'B' and hers with 'C' - nothing in common). Also - suppose I stole your routines dealing with emp and dept, after you stole them from her. We have that redundant code - but you'll never know.
In short - because we share common dependencies - doesn't mean anything. Because we don't share many common dependencies - doesn't mean anything. You have to look at and catalog both.
could I write a query that gives you this information? Sure, we could set up a threshold that "if N dependent objects match - we are to be considered 'similar'" and pass in N - but I don't see the point.
And it would be a very expensive query to compute.
here is a start for you...
ops$tkyte%ORA10GR2> with t
2 as
3 (
4 select owner, name, referenced_owner, referenced_name, referenced_type ,
5 count(*) over (partition by owner, name, type) cnt
6 from all_dependencies
7 where owner <> 'SYS'
8 and referenced_owner <> 'SYS'
9 and type = 'PACKAGE BODY'
10 )
11 select a.owner||'.'||a.name a,
12 b.owner||'.'||b.name b,
13 a.cnt, b.cnt, count(*) cnt
14 from t a, t b
15 where a.referenced_owner = b.referenced_owner
16 and a.referenced_name = b.referenced_name
17 and a.referenced_type = b.referenced_type
18 and (a.owner <> b.owner or a.name <> b.name)
19 group by a.owner, a.name,
20 b.owner, b.name,
21 a.cnt, b.cnt
22 having count(*) >= 15
23 order by a.owner, a.name,
24 b.owner, b.name
25 /
A B CNT CNT CNT
------------------------------ ------------------------------ ---------- ---------- ----------
CTXSYS.CTX_CLS CTXSYS.DRVDDL 21 42 15
CTXSYS.CTX_CLS CTXSYS.DRVODM 21 33 18
CTXSYS.CTX_DDL CTXSYS.DRISGP 24 20 15
CTXSYS.CTX_DDL CTXSYS.DRISPL 24 22 17