... 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