CODEWITHSUNDEEP
oracle-databaseoracle11g
donstack
donstack

Reputation: 2715

get list of schemas using a particular package in oracle

Is there any way to find how many schema are using a particular package.

So that before altering a package I can know how many schema would be affected.

Upvotes: 0

Views: 1080

Answers (3)

Patrick Bacon
Patrick Bacon

Reputation: 4640

There are many similar question about object dependencies that have been answered on stackoverflow. I could not find an exact match (other scenarios are for objects of type view or table). Here is an example of a good answer (look at Ollie's answer Recursively list concents of Oracle's DBA_DEPENDENCIES view).

Here is my recursive query that I use for this type of question:

SELECT LEVEL DEP_LEVEL,
  DD.OWNER,
  DD.NAME,
  DD.REFERENCED_OWNER,
  DD.REFERENCED_NAME,
  DD.TYPE,
  DD.OWNER
  || '.'
  || DD.NAME OBJECT_NAME,
  DD.REFERENCED_OWNER
  || '.'
  || DD.REFERENCED_NAME REFERENCED_OBJECT_NAME
FROM DBA_DEPENDENCIES DD
  START WITH DD.OWNER      = '&package_owner'
AND DD.NAME                = '&package_name'
  CONNECT BY  NOCYCLE PRIOR DD.NAME =DD.REFERENCED_NAME
 AND  PRIOR DD.OWNER = DD.REFERENCED_OWNER

Upvotes: 1

donstack
donstack

Reputation: 2715

It was easy than expected:

select * from user_dependencies where name='object_name'

Upvotes: 1

neshkeev
neshkeev

Reputation: 6476

You can use the dba_tab_privs view:

SQL> ed
Wrote file afiedt.buf

  1  create package test_pkg
  2  as
  3    function get_one
  4    return number;
  5* end test_pkg;
SQL> /


SQL> ed
Wrote file afiedt.buf

  1  create or replace package body test_pkg
  2  as
  3    function get_one
  4    return number
  5    is
  6    begin
  7      return 1;
  8    end get_one;
  9* end test_pkg;
SQL> /

SQL> grant execute on test_pkg to hr
  2  /

Grant succeeded.

SQL> ed
Wrote file afiedt.buf

  1  SELECT grantee
  2    FROM dba_tab_privs
  3   WHERE table_name = 'TEST_PKG'
  4     AND privilege = 'EXECUTE'
  5  union
  6  SELECT owner
  7    FROM dba_tab_privs
  8*  WHERE table_name = 'TEST_PKG'
SQL> /

GRANTEE
------------------------------
HR
SYS

SQL>

Upvotes: 0

Related Questions