J. Laurindo -- Thanks for the question regarding "List the tables with DML Dependencies", version 10.2.0

Submitted on 17-Feb-2008 9:51 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 18-Feb-2008 13:54

You Asked

Using DBA_DEPENDENCIES, we can find all the dependents inside the db (procedures, triggers, packages, etc) for a given table. But now my developers want to know where (inside the db) a given table is inserted/updated/deleted - to discover that, I was thinking aBout, for each line in DBA_DEPENDENCIES to the given table, read DBA_SOURCE searching fot INSERT or UPDATE or DELETE : question is, the DML command can be "broken" in many lines, or can have a lot of comments, and so on, this logic can be daunting. Any other options ?

Regards,

Chiappa

and we said...

not only can the DML be "broken" over many lines - you could have an insert (or update or delete) that references the table but doesn't modify it (giving you false positives)

procedure p
is
begin
insert into t1
select * from t2;
end;

p would be dependent on T2 and T1 - but only insert into T1, and read T2.

the information about what a procedure does or might do to a table - is not recorded in that fashion. In fact with dynamic sql, it would be a rather incomplete list as well.

Reviews

February 18, 2008 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Jose Chiappa

Yes, for sure we can have dependents not suffering DMLs, we can have synonyms (what demands a 
"translation" to find the real table), and other cases, all of them must be covered - yes, this 
will be a quite envolving routine to code.... But, due to limitations of the tool being used, my 
developer really need to know it, if no other way exists, I will unfortunately limited to 
do-it-myself..... Anyway, thank you for the answer.

Regards,

  Chiappa

Followup February 18, 2008 - 1pm Central time zone:

you know, if you used something to design your system.... hmmm, wouldn't documentation be so cool.

A possible trick February 19, 2008 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Gary from Sydney, Aus

One suggestion.
In a COPY of the database (import/export without rows).
Drop the table. Create a table with the same columns/structure in a different user and grant SELECT 
on that to the original user.
Create a synonym in the original user to the new table. Recompile all the procedures. Any 
procedures that don't compile okay require either INSERT/UPDATE/DELETE privileges on that table or 
calls something that does and which no longer compiles.
Cumbersome, but an opportunity to create some of that missing documentation. Doesn't help with 
dynamic SQL though.

February 20, 2008 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jose Chiappa

Tom, I agreed, and the worst of all is : the tool HAVE a documentation module, and the developers 
don´t used it.... 
  "Oh, the pain... the pain!"

Gary, interesting idea, and I´m a user of the freeware DDL Wizard ( http://www.ddlwizard.com/ ) , 
with it I can "clean" easily a full .dmp file removing what I don´t need in this situation (like 
indexes, tablespaces specs, other things aside pl/sqls) rapidly... Yes, it could be work...

Regards,


 Chiappa

Write a Review