Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Nag.

Asked: March 28, 2002 - 11:02 pm UTC

Last updated: March 27, 2013 - 5:11 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom

Do you have a sql which will give child tables for a given Parent table.
or
A sql which will give all the related tables for a given table.

And finally, how can I find out with a sql all the tables involved in a view.

Thank you




and Tom said...

Look at *_DEPENDENCIES, where * = user, all and dba.

For constraints, look at *_constraints.

For example, given a parent table T you can:


select table_name
from user_constraints
where (r_constraint_name) in ( select constraint_name
from user_constraints
where table_name = 'T'
and constraint_type in ( 'P', 'U' ) );


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table p ( x int primary key, y int unique );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table c1 ( x references p );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table c2 ( y int references p(y) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select table_name
2 from user_constraints
3 where (r_constraint_name) in ( select constraint_name
4 from user_constraints
5 where table_name = 'P'
6 and constraint_type in ( 'P', 'U' ) );

TABLE_NAME
------------------------------
C1
C2



Rating

  (27 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 29, 2002 - 2:20 pm UTC


Need more info

Gerry Abreu, June 13, 2002 - 1:57 pm UTC

Hello,
The SQL is fine though I would need SQL for many more levels of dependencies not just two.
If you would please email me at gerry.abreu@eds.com if you have more info.
Thanks.

Tom Kyte
June 13, 2002 - 2:14 pm UTC

I would make a copy of user_dependencies then (for performance reasons mostly) and use connect by ( a sql clause -- see the sql language manual).

Just watch out for recursive dependencies ;)



question about USER_DEPENDENCIES

James Su, June 30, 2004 - 6:02 pm UTC

hi Tom,

Please tell me why I can see two NON-EXISTENT objects in USER_DEPENDENCIES (procedure WHO is compiled without errors)

Thank you!

SQL> select REFERENCED_NAME,REFERENCED_OWNER,REFERENCED_TYPE from USER_DEPENDENCIES WHERE NAME  = 'WHO';

REFERENCED_NAME                 REFERENCED_OWNER       REFERENCED_TYPE
------------------------------- ---------------------- -----------------
STANDARD                        SYS                    PACKAGE
DATABASE_PROPERTIES             SYS                    VIEW
DATABASE_PROPERTIES             PUBLIC                 SYNONYM
SYS_STUB_FOR_PURITY_ANALYSIS    SYS                    PACKAGE
DBMS_OUTPUT                     SYS                    PACKAGE
DBMS_OUTPUT                     PUBLIC                 SYNONYM
DATABASE_PROPERTIES             JAMES                  NON-EXISTENT
DBMS_OUTPUT                     JAMES                  NON-EXISTENT

8 rows selected. 

Tom Kyte
June 30, 2004 - 8:16 pm UTC

they are anti-objects created when you reference a public synonym. for the invalidations/dependency to work -- we need something to hook onto. These non-existent (i call them anti-objects) provide that.


that way, if you were to create your own dbms_output -- the procedure dependent on the PUBLIC SYNONYM dbms_output would be identified and invalidated, your dbms_output object (james.dbms_output) would anhilate this anti-object and cause the invalidation.

Nice

Gerhard, May 11, 2005 - 1:25 pm UTC

Hello Tom,
While querying sys.dependency$ I find the columns namely
d_object#,p_object#
What do they represent??

I think as d_object# -> Dependent Object#
p_object# -> Parent object#
Am I right?

Any x$tables exist which provide information
about object dependencies??
I know about dba_dependencies and ideptree.
Any other views exist??
Please let me know.
Bye!

Tom Kyte
May 11, 2005 - 7:19 pm UTC

I don't know, never reversed engineered them, they are not documented.

Dependency?

Jairo Ojeda, July 15, 2005 - 11:43 am UTC

Tom, I need to list from a specific owner all tables with the same column name --like TIMESTAMP, and their dependencies because we need to rename it, but my query is very slow and don’t get the tables with no dependencies. I have change the query to know the elapsed time: getting 1 row --3:30 min. but I have about 196 rows that match.

This is the base query I did:
SELECT dep.tree||obj.owner owner, obj.object_type, obj.object_name, obj.status
FROM DBA_OBJECTS obj,
(SELECT dep.object_id, RPAD('*',2*LEVEL,'*') tree
FROM PUBLIC_DEPENDENCY dep,
(SELECT object_id
FROM DBA_OBJECTS
WHERE owner = :owner AND
object_name = :object_name AND
object_type = :object_type) obj
CONNECT BY PRIOR dep.object_id = dep.referenced_object_id
START WITH dep.referenced_object_id = obj.object_id
ORDER SIBLINGS BY dep.object_id DESC) dep
WHERE dep.object_id = obj.object_id;

And this is what I tried:
SELECT dep.owner, dep.table_name, dep.tree||obj.owner dependency,
obj.object_type, obj.object_name, obj.status
FROM DBA_OBJECTS obj,
(SELECT RPAD('*',2*LEVEL,'*') tree, dep.object_id, obj.owner,
obj.table_name
FROM PUBLIC_DEPENDENCY dep,
(SELECT /*+ FIRST_ROWS */ *
FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM r
FROM (SELECT b.object_id, a.owner, a.table_name
FROM DBA_TAB_COLUMNS a, DBA_OBJECTS b
WHERE b.owner = a.owner AND
b.object_name = a.table_name AND
a.owner = :owner AND
b.object_type = :object_type AND
a.column_name = :column_name
ORDER BY a.table_name) A
WHERE ROWNUM <= :MAX)
WHERE r >= :MIN) obj
CONNECT BY PRIOR dep.object_id = dep.referenced_object_id
START WITH dep.referenced_object_id = obj.object_id
ORDER SIBLINGS BY dep.object_id DESC) dep
WHERE dep.object_id = obj.object_id;

Tom Kyte
July 15, 2005 - 6:03 pm UTC

this looks like a one time report.

rather than try to optimize anything, just copy of a dba_tab_columns those things with timestamp.

report them.
and then show a report of their dependents.



OK!

Jairo Ojeda, July 15, 2005 - 6:21 pm UTC


rename a column and

David, August 17, 2005 - 3:33 pm UTC

I need to rename a column of an important table that is referred in several pl sql procedures. Is there a query that could give me what are all the procedures/functions/triggers that are dependent on this column that I am going to rename? Thanks.

Tom Kyte
August 17, 2005 - 5:13 pm UTC

not on the column, it is not tracked to that level.

however, when you do this in TEST (because you will right.......), you'll find all of the procedures that go invlalid, you'll recompile them and the ones that don't compile -- they are the ones that relied on that column.

rename a column...

David, August 17, 2005 - 10:30 pm UTC

ok. if it is not tracked on the column level, at the least, is it possible to find using data dictionary, the dependent plsql procs/functions/triggers that refer this table? Thanks for your time.

Tom Kyte
August 18, 2005 - 3:39 pm UTC

dba|all|user_dependencies

SQl PLUS Querry

ranjeet kumar, September 10, 2005 - 3:28 am UTC

i want some query about the sql-plus and database querry

Tom Kyte
September 10, 2005 - 8:17 am UTC

ok?

Get columns too

Iain Hunter, November 28, 2005 - 11:30 am UTC

Thanks Tom, I modified your original query slightly as I found it useful to be able to see the dependant table AND associated column. Hope others find this useful:

SELECT UPPER(:table_name) "Parent Table",
iv.r_constraint_name "Parent Constraint",
(SELECT column_name FROM user_cons_columns WHERE constraint_name = iv.r_constraint_name) "Parent Column",
iv.table_name "Dependent Table",
iv.constraint_name "Constraint Name",
ucc.column_name "Dependent Column"
FROM user_cons_columns ucc,
(SELECT *
FROM user_constraints
WHERE r_constraint_name in (SELECT constraint_name
FROM user_constraints
WHERE table_name = UPPER(:table_name)
AND constraint_type IN ('P', 'U'))) iv
WHERE iv.constraint_name = ucc.constraint_name

Package dependencies

Vinayak, November 07, 2006 - 11:29 am UTC

Hi Tom,

I would like to write a query or package(if not possible in a query) that will list all the dependent objects of a package till the last level. User_dependencies gives us 1 level of dependency but I am unable to think of a way to call this recursively and I beleive this can be accomplished with a query using connect by clause.

An example would be:
PKG1 uses 2 tables(T1 & T2) and 2 packages (PKG2 & PKG3). PKG2 uses just 1 table(T3) and PKG3 uses 1 tables(T4) and 1 package PKG4. PKG4 uses 3 tables (T5,T6,T7).

My desired output would be a heirarchical display like:
PKG1
T1
T2
PKG2
T3
PKG3
T4
PKG4
T5
T6
T7

Thanks
Vinayak

Tom Kyte
November 07, 2006 - 4:41 pm UTC

yes, you can in fact do this readily and easily with connect by.

I'll let you give it the "old college try" first on your own - it won't hurt you.

getting an error

Vinayak, November 08, 2006 - 8:57 am UTC

Hi Tom,
Thanks for giving me a try.Here is the query that I wrote but it is giving "ORA-01436: CONNECT BY loop in user data".

select lpad(' ',2*(level-1)) || to_char(name) Objects
from user_dependencies
start with (name='PKG1' and referenced_name='PKG1')
connect by prior referenced_name=name

I understand this error is due to the fact that PKG1 occurs multiple times in the table. But don't know how to solve it.
here is the full script for the test:

create table T1 (a integer);
create table T2 (a integer);
create table T3 (a integer);
create table T4 (a integer);
create table T5 (a integer);
create table T6 (a integer);
create table T7 (a integer);

CREATE OR REPLACE package PKG4 IS
procedure do_nothing;
END PKG4;
/
CREATE OR REPLACE package body PKG4 IS
var1 integer;
var2 integer;
var3 integer;
procedure do_nothing IS
begin
select a into var1 from t5;
select a into var1 from t6;
select a into var1 from t7;
END;
END PKG4;
/
CREATE OR REPLACE package PKG3 IS
procedure do_nothing;
END PKG3;
/
CREATE OR REPLACE package body PKG3 IS
var1 integer;
procedure do_nothing IS
begin
select a into var1 from t4;
pkg4.do_nothing;
END;
END PKG3;
/
CREATE OR REPLACE package PKG2 IS
procedure do_nothing;
END PKG2;
/
CREATE OR REPLACE package body PKG2 IS
var1 integer;
procedure do_nothing IS
begin
select a into var1 from t3;
END;
END PKG2;
/
CREATE OR REPLACE package PKG1 IS
procedure do_nothing;
END PKG1;
/
CREATE OR REPLACE package BODY PKG1 IS
var1 integer;
var2 integer;
procedure do_nothing IS
begin
select a into var1 from t1;
select a into var2 from t2;
pkg2.do_nothing;
pkg3.do_nothing;
END;
END PKG1;
/



Tom Kyte
November 08, 2006 - 7:41 pm UTC

think about it....

look at your connect by (is connecting by NAME sufficient??)

look at your start with, you are starting with a package body, that connects to pkg1 the spec only.

you have not thought this through...

I have given it a chance !!!

Vinayak, November 13, 2006 - 10:58 am UTC

Tom,
Thanks for being a hawk-eyed mentor. I had tried using the old good emp heirarchy style. I have converted my data into the same way and processed it in the similar fashion and there it is. I have got the answer.

select lpad(' ',3*level-1) || to_char(empname) Objects,emptype Objecttype FROM(
select empname,emptype,mgrname,mgrtype from (
select 1,ud.REFERENCED_NAME empname,ud.REFERENCED_TYPE emptype,NULL mgrname,NULL mgrtype from user_dependencies ud where ud.REFERENCED_name='PKG1'
union all
select 2,ud.REFERENCED_NAME empname,ud.REFERENCED_TYPE emptype,ud.NAME mgrname,ud.TYPE mgrtype from user_dependencies ud where ud.REFERENCED_OWNER!='SYS'
and ud.REFERENCED_NAME!=ud.NAME
ORDER By 1))
start with mgrname is null
connect by prior empname=mgrname;

But Tom I myself is not convinced with this query. I beleive there must be a much better way to do this.But the problem is it works in my case but if I ran it in my actual development environment with actual package name, it again throughs the same error: ORA-01436: CONNECT BY loop in user data.
Please share what am I missing here.

Tom Kyte
November 14, 2006 - 4:05 am UTC

you could well have a recursive relationship that goes into an infinite loop. you could use "connect by nocycle" if you are using current software.

and please, the "key" of user dependencies is NOT just the name (that is the point, what is the "key" of user dependencies, you need to connect by THE KEY, not just part of it)

still unclear !!!!

Vinayak, November 14, 2006 - 6:15 am UTC

Tom, I am still unbale to understand the key that you are referring to. Please explain.

Tom Kyte
November 14, 2006 - 7:19 am UTC


ops$tkyte%ORA9IR2> desc user_dependencies
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 NAME                                     NOT NULL VARCHAR2(30)
 TYPE                                              VARCHAR2(17)
 REFERENCED_OWNER                                  VARCHAR2(30)
 REFERENCED_NAME                                   VARCHAR2(64)
 REFERENCED_TYPE                                   VARCHAR2(17)
 REFERENCED_LINK_NAME                              VARCHAR2(128)
 SCHEMAID                                          NUMBER
 DEPENDENCY_TYPE                                   VARCHAR2(4)



name+type

referenced_owner+referenced_name+referenced_type


those are two of the keys, you are connecting by -name, insufficient. 

user_dependencies

Alejandro Vega, February 21, 2007 - 1:56 pm UTC

create table t(x number,y number);

Table created.


create or replace procedure pt1 is
l_n number;
begin
select count(*) into l_n from t;
end;
/

Procedure created.

create or replace procedure pt2 is
begin
pt1;
end;
/

Procedure created.


create or replace procedure pt3 is
begin
pt2;
end;
/

Procedure created.


column name format a10
column type format a10
column referenced_name format a25
column referenced_type format a25

select name,type,referenced_name,referenced_type
from user_dependencies
where referenced_name in ('T','PT1','PT2','PT3');

NAME TYPE REFERENCED_NAME REFERENCED_TYPE
---------- ---------- ------------------------- -------------------------
PT2 PROCEDURE PT1 PROCEDURE
PT3 PROCEDURE PT2 PROCEDURE
PT1 PROCEDURE T TABLE

3 rows selected.


select name,type,referenced_name,referenced_type
from user_dependencies
connect by prior trim(name) = trim(referenced_name) and
prior trim(type) = trim(referenced_type)
start with referenced_name = 'T';

no rows selected


create table t_user_dependencies as select * from user_dependencies;

Table created.


select name,type,referenced_name,referenced_type
from t_user_dependencies
connect by prior trim(name) = trim(referenced_name) and
prior trim(type) = trim(referenced_type)
start with referenced_name = 'T';

NAME TYPE REFERENCED_NAME REFERENCED_TYPE
---------- ---------- ------------------------- -------------------------
PT1 PROCEDURE T TABLE
PT2 PROCEDURE PT1 PROCEDURE
PT3 PROCEDURE PT2 PROCEDURE

3 rows selected.




If I use user_dependencies I don't see rows, but when I use t_user_dependencies I see rows.
Am I loosing anything or is it a bug ?



Tom Kyte
February 21, 2007 - 3:17 pm UTC

I cannot reproduce

ops$tkyte%ORA10GR2> select name,type,referenced_name,referenced_type
  2  from  user_dependencies
  3  connect by prior trim(name) = trim(referenced_name) and
  4              prior trim(type) = trim(referenced_type)
  5                          start  with referenced_name = 'T';

NAME       TYPE       REFERENCED_NAME           REFERENCED_TYPE
---------- ---------- ------------------------- -------------------------
PT1        PROCEDURE  T                         TABLE
PT2        PROCEDURE  PT1                       PROCEDURE
PT3        PROCEDURE  PT2                       PROCEDURE

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t_user_dependencies as select * from user_dependencies;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select name,type,referenced_name,referenced_type
  2  from  t_user_dependencies
  3  connect by prior trim(name) = trim(referenced_name) and
  4              prior trim(type) = trim(referenced_type)
  5                          start  with referenced_name = 'T';

NAME       TYPE       REFERENCED_NAME           REFERENCED_TYPE
---------- ---------- ------------------------- -------------------------
PT1        PROCEDURE  T                         TABLE
PT2        PROCEDURE  PT1                       PROCEDURE
PT3        PROCEDURE  PT2                       PROCEDURE



it looks like a "bug", you likely have different optimizer settings than I - so you will want to work this via support. I tried in my 9ir2 and 10gr2 databases and it worked OK in both.

table dependency in procedure inside package

A reader, February 26, 2008 - 12:38 pm UTC

Hi Tom,

Is it possible to find the dependency of a table for procedures inside a package ? I ran following query to get the list of package names but dont know how many procedures are using the table inside it.

SELECT * FROM USER_DEPENDENCIES WHERE REFERENCED_NAME in
( %LIST OF TABLE_NAME%)
AND TYPE IN ('PACKAGE BODY');

Thanks in advance,
Tom Kyte
February 26, 2008 - 3:10 pm UTC

not tracked at that that, only tracked at the schema object level, which is the package.

the package either uses it or not, that is all we care about.

How to find dependencies on Objects of Remote database

Bhavesh Adatiya, December 22, 2009 - 2:06 am UTC

We are missing something in this discussion. Just wanted to Add to it and would also like to know.

How can we find out depencenies on the Objects of a remote database. Let's assume I have created a view and i am referring to a table of Remote database using DB_LINK.
How can i find out these kind of dependencies. I tried to user DBA_DEPENDENCIES.

DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.




Tom Kyte
December 23, 2009 - 7:16 am UTC

Your object is dependent on the database link - and that is all.

I like to use views, as they import the metadata of the remote table and it is pretty clear what the view refers to to (single table view). It makes the compilation of the procedure a bit speedier as well since the procedure is dependent on the view, not on the database link behind the view - we don't need to even open the link to compile the procedure.

dba_dependencies referenced_link_name

Ulfet, February 15, 2010 - 4:39 am UTC

Hello Tom,

Could you tell me why some remote objects do not observe on dba_dependencies?

Let me explain my question.

When I select :

select * from dba_dependencies where name='PKG_PREPAID_HISTORY'; -- PKG_PREPAID_HISTORY is package and in pack body there are remote objects used

This result does not shows me remote object(s)

I know and I can see when I execute below SQL statement:

select * from dba_source where name='PKG_PREPAID_HISTORY' and owner='SUBDEALER' and lower(text) like lower('%@%') and rownum <3;

OWNER,NAME,TYPE,LINE,TEXT
SUBDEALER,PKG_PREPAID_HISTORY,PACKAGE BODY,20, FROM bgw_usr.adjustment_cs3@onsubs_airdr a, prepaid_history b

SUBDEALER,PKG_PREPAID_HISTORY,PACKAGE BODY,32, FROM bgw_usr.tickler_cs3@onsubs_airdr a, prepaid_history b



But when execute:
select * from dba_dependencies where name='PKG_PREPAID_HISTORY' and referenced_link_name is not null;

No rows returned!!!

Please describe me why above (at least) sql no shows?
Have a lot of packages, procedures etc which calls remote object but I can`t see them on dba_dependencies.


Oracle version: 10g

Tom Kyte
February 16, 2010 - 5:03 pm UTC

can you run a small test for me

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create database link ora10gr2@loopback connect to ops$tkyte identified by foobar using 'ora10gr2';

Database link created.

ops$tkyte%ORA10GR2> select * from dual@ora10gr2@loopback;

D
-
X

ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          for x in ( select * from t@ora10gr2@loopback )
  5          loop
  6                  dbms_output.put_line( x.x );
  7          end loop;
  8  end;
  9  /

Procedure created.

ops$tkyte%ORA10GR2> select * from user_dependencies where name = 'P' and referenced_link_name is not null;

NAME                           TYPE              REFERENCED_OWNER
------------------------------ ----------------- ------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
REFERENCED_LINK_NAME
-------------------------------------------------------------------------------
  SCHEMAID DEPE
---------- ----
P                              PROCEDURE         OPS$TKYTE
T
TABLE
ORA10GR2.COM@LOOPBACK
       592 HARD





*_DEPENDENCIES

Prakash, February 18, 2010 - 12:18 am UTC

Tom,

In USER_DEPENDENCIES we have DEPENDENCY_TYPE varchar2(4).
I often see 'HARD' value in it. What is this mean?
Is there any other values other than 'HARD'?

thanks,
Prakash
Tom Kyte
February 18, 2010 - 9:19 am UTC

ops$tkyte%ORA11GR2> select distinct dependency_type from dba_dependencies;

DEPE
----
HARD
REF



http://docs.oracle.com/docs/cd/E11882_01/server.112/e10820/statviews_1067.htm#REFRN20053

has the values listed as well.

ops$tkyte%ORA10GR2> create function f( x in number ) return varchar2
  2  deterministic
  3  as
  4  begin
  5          return to_char(x,'999999999');
  6  end;
  7  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x number );

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(f(x));

Index created.

ops$tkyte%ORA10GR2> create trigger t
  2  before insert on t
  3  begin
  4          null;
  5  end;
  6  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select name, type, referenced_name, referenced_type, dependency_type
  2    from user_dependencies
  3  /

NAME  TYPE       REFERENCED REFERENCED DEPE
----- ---------- ---------- ---------- ----
F     FUNCTION   STANDARD   PACKAGE    HARD
T_IDX INDEX      F          FUNCTION   REF
T     TRIGGER    T          TABLE      HARD

Missing information in DBA_DEPENDENCIES

Shafi, August 13, 2010 - 5:51 am UTC

Hi Tom,

In our production database there are some procedures which use some remote tables but in DBA_DEPENDENCIES some of this tables are not shown. This missing information is so dangerous for us, because we have to know all dependencies. What can be reason of this missing information?
Tom Kyte
August 19, 2010 - 12:35 am UTC

I'd need to see a "for example", show me a valid plsql routine with missing information (keyword = valid)

Missing information in DBA_DEPENDENCIES

A reader, September 03, 2010 - 6:24 am UTC

This situation exist between 10g and 9i databases, in some circumstances such, let say, in db10g database user A has table X, and also in db9i A user has X table. If in db10g a procedure uses A.X@db9i table, this dependency is not shown in dba_dependencies.
Thank you in advance.

Missing information in DBA_DEPENDENCIES

A reader, September 03, 2010 - 6:32 am UTC

CREATE OR REPLACE procedure CMDB.syn_dupl
as

f integer;

begin

select t into f
from cmdb.x@db_link
where rownum = 1;

end;
/

select owner, name, referenced_owner, referenced_name from dba_dependencies where name = 'SYN_DUPL';


OWNER NAME REFERENCED_OWNER REFERENCED_NAME

CMDB SYN_DUPL SYS STANDARD
CMDB SYN_DUPL SYS SYS_STUB_FOR_PURITY_ANALYSIS


As shown cmdb.x@db_link table doesn't shown in dba_dependencies;
Tom Kyte
September 09, 2010 - 7:04 pm UTC

please utilize support - although if this a 9i specific issue, it is doubtful that unless there is a patch already that it would be/could be fixed.

Dpendency over Columns

A reader, April 12, 2011 - 5:59 am UTC

Hi Tom,

Can we track the dependency over the column used in specific views in Oracle 10G.

I saw we can track through DBA_DEPENDENCY_COLUMN in 11G but is there any possiblity in 10G.

I can see it through DBA_DEPENDENCIES but only tables. Could you help.
Tom Kyte
April 13, 2011 - 9:15 am UTC

That mapping did not exist prior to 11g when dependency tracking was pushed down to the column level

find call path from a_pkg to b_pkg

A reader, February 07, 2012 - 11:41 pm UTC

Hi, Tom,

I know the a_pkg call b_pkg via sql trace file.
But how to find its call path?
From this thread, I know the information is stored in dba_depencencies.
But the difficulties are:
1. using connect by prior referenced_owner=owner and prior referenced_name=name and prior referenced_type=type will not work because 'package' and 'package body' types may both reference a 'package' type with same owner and name.
2. after i changed connect by with some like prior referenced_owner=owner and prior referenced_name=name
and (prior referenced_type='PACKAGE' and type in ('PACKAGE BODY','PACKAGE') or prior referenced_type!='PACKAGE' and prior referenced_type=type), it shows ora-1436 to me.
3. then i use connect by nocycle, but it returns too many rows that the level is more than 100(i am using an EBS database).

Could you please give me your SQL for my reference?

Thanks,
William
Tom Kyte
February 08, 2012 - 1:39 am UTC

From this thread, I know the information is stored in dba_depencencies.


not really, that would show all possible static paths (there could be a huge number of those), but it would never show the dynamic (at runtime) paths - using dynamic sql calls.

we do not keep this information anywhere - except at runtime. At runtime, b_pkg could log, if it wanted to, the call stack that resulted in its being called.

short of that, we don't track it - and you cannot really get it from the *_dependencies tables. I don't know how you deduced this from a trace file either - the trace file shows SQL and dynamically executed PL/SQL, but not every function call made.


I don't have any sql - since you cannot really extract this information.

Split based on Column Value

Veera, February 08, 2012 - 3:15 am UTC

I have a table like follows

Name Gender
-------------
Arun M
Anitha F
Bala M
Banu F

I need the output as follows

Male Female
-----------------
Arun Anitha
Bala Banu

What are the ways can we generate the above query (Please provide any Advanced SQL concepts)

Thanks
Veera
Tom Kyte
February 08, 2012 - 5:09 am UTC

I've assumed

a) there could be more M than F's or vice versa
b) you want the output sorted alphabetically in each column
c) you want two columns - there is no spacing, hard to say

in the future, provide create tables and inserts - make it EASY for the person you are asking a question of to provide an answer.
ops$tkyte%ORA11GR2> create table t ( nm varchar2(10), gender varchar2(1) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 'Arun','M' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'Anitha','F' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'Bala','M' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'Banu','F' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'Tom','M' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with
  2  male as (select nm, row_number() over (order by nm) rn from t where gender = 'M'),
  3  female as (select nm, row_number() over (order by nm) rn from t where gender = 'F')
  4  select male.nm, female.nm
  5    from male full outer join female on (male.rn = female.rn)
  6   order by coalesce( male.rn, female.rn)
  7  /

NM         NM
---------- ----------
Arun       Anitha
Bala       Banu
Tom


find call path from a_pkg to b_pkg

A reader, February 08, 2012 - 4:32 am UTC

Hi, Tom,

Thank you for your reply.

I know if there is any call from 'dynamic sql call' we have no information about it in dba_dependencies.
But I know there is no dynamic call in the path from a_pkg to b_pkg. So, the information in dba_dependencies may work.

Why I need to know the call path from a_pkg to b_pkg is because we found something wrong(e.g. some very strange update statement which should not appear) in the trace file.
Then we want to comfirm if b_pkg is called by a_pkg indeed and what's the calling path so that we can analysis the main calling logic(it's too complex of a_pkg to read all the codes of it).

So, I need your help to write a sql to find out the calling path and cover my 3 difficulties in my last post.

Thanks,
William
Tom Kyte
February 08, 2012 - 5:19 am UTC

But I know there is no dynamic call in the path from a_pkg to b_pkg. So, the
information in dba_dependencies may work.


and it might not, and it could provide many paths from A to B, some of them infinite in size, and .. and .. and ...

this will print out a tree

select rpad('*',2*level,'*')||referenced_owner||'.'||referenced_name||'.'||referenced_type nm, name, type
  from user_dependencies
 start with referenced_name = 'PKG_A' and referenced_type = 'PACKAGE' and referenced_owner = user
connect by prior referenced_owner = user and referenced_name = prior name and referenced_type = prior type
/


you might need to throw in some NOCYCLE logic to prevent infinite loops, you'll probably want to stop connecting if you hit "PKG_B"


need help depedencies

A reader, March 26, 2013 - 8:24 pm UTC

Hey Tom , I'm working on Oracle developper , I need help , well I need to create a table WORK_ODI_DBA_DEPENDENCIES that import data from dba_dependencies , I dont know why my procedure doesn't work , it show me : Source does not have a runnable target /// plz does anyone know why ?
here is my code :
----------------------------------------
CREATE OR REPLACE PROCEDURE PRC_ALIMENTERDEP IS
BEGIN

delete from WORK_ODI_DBA_DEPENDENCIES;
insert into WORK_ODI_DBA_DEPENDENCIES (referenced_owner, referenced_name, type, name)
select
referenced_owner, referenced_name, type, name
from
SELECT referenced_owner, referenced_name, type, name from dba_dependencies WHERE
referenced_owner IN ('ODS', 'DWH') and referenced_type = 'TABLE';

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END PRC_ALIMENTERDEP;
----------------------------------
I need to create this table to use it as source table in ODI
Thank u in advance ^^
Tom Kyte
March 27, 2013 - 5:11 pm UTC

EXCEPTION 
WHEN OTHERS THEN 
ROLLBACK;


I hate your code - hate it, simply despise it. I would never let it into production in a billion years. Just upon seeing that.

asktom.oracle.com/Misc/when-others-then-null-redux.html
http://asktom.oracle.com/Misc/dreaded-others-then-null-strikes-again.html


How I wish plsql did not have rollback and commit.... sigh... just an all around bad practice.


that said.

I have no idea why your code doesn't work, "Source does not have
a runnable target" means nothing to me.

perhaps if you got rid of that exception block so we could see the ACTUAL ERROR MESSAGE - we'd know.

the problem with your code is that it ALWAYS SUCCEEDS, even when it fails. Think about it - if you run this code - the thing calling this code - can it determine if

a) this code succeeded
b) this code failed?


actually - it can - it can determine that this code succeeds 100% of the time, it can never fail - because of that horrible when others "logic" you have there.