Home » RDBMS Server » Performance Tuning » Problem with Object Dependencies
| Problem with Object Dependencies [message #173685] |
Wed, 24 May 2006 00:47  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
I Want to see dependencies of object hierarchically (only procedures),
i am using user_dependencies which shows only one level for each object
e.g. proc1 p3 calls p2 which in turn calls p1, still there will be only one entry for p3 showing its dependency with p2 and not with p1.
This needs querying user_dependencies with connect by kind of query to get hierchical query.
utldeptree shows complete required information, however it needs "deptree_fill" be called individually for each object followed by call to deptree.
Is it possible to use deptree for all objects at a time?
Thanks in Advance,
Pratap
|
|
|
|
|
|
| Re: Problem with Object Dependencies [message #173865 is a reply to message #173788] |
Wed, 24 May 2006 23:26  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi Mahesh,
Many thanks for your advice.
i will try to do what you have described,
However what about following..
i executed following quey which gave me error (may be due to recursive calls to data dictionary)
ERROR:
ORA-01436: CONNECT BY loop in user data
SELECT A.* FROM(select decode(SUBSTR(NAME,3,1),'_',SUBSTR(NAME,1,2),SUBSTR(NAME,1,3)) MODULE, NAME,TYPE,decode(SUBSTR(referenced_NAME,3,1),'_',SUBSTR(referenced_NAME,1,2),SUBSTR(referenced_NAME,1,3)) REFERRED_MODULE,
REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where TYPE IN ('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE' ) AND
REFERENCED_TYPE IN('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE') AND SUBSTR(NAME,1,3) !=
SUBSTR(REFERENCED_NAME,1,3) AND REFERENCED_OWNER = 'HSASYS') A CONNECT BY PRIOR REFERENCED_NAME = NAME;
then i ran the same query for each aplhabet separately
SELECT A.* FROM(select decode(SUBSTR(NAME,3,1),'_',SUBSTR(NAME,1,2),SUBSTR(NAME,1,3)) MODULE, NAME,TYPE,decode(SUBSTR(referenced_NAME,3,1),'_',SUBSTR(referenced_NAME,1,2),SUBSTR(referenced_NAME,1,3)) REFERRED_MODULE,
REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where TYPE IN ('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE' ) AND
REFERENCED_TYPE IN('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE') AND SUBSTR(NAME,1,3) !=
SUBSTR(REFERENCED_NAME,1,3) AND REFERENCED_OWNER = 'HSASYS' AND NAME LIKE 'C%') A CONNECT BY PRIOR REFERENCED_NAME = NAME;
Please suggest if this approach is also correct?
Thanks and Regards,
Pratap
|
|
|
|
| Re: Problem with Object Dependencies [message #173973 is a reply to message #173788] |
Thu, 25 May 2006 07:29 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi Mahesh,
Your suggestion has motivated to create procedure like following
however, i have 2 questions regarding this.
FOR X IN(SELECT DECODE(PROCEDURE_NAME,NULL,OBJECT_NAME,PROCEDURE_NAME) NAME FROM
dba_procedures where object_name like 'C%' and oWNer='PRATAPZ')
LOOP
EXECUTE deptree_fill ('PROCEDURE', 'PRATAPZ', X.NAME)
1)How do i know the object type (procedure in above case) in advance since the type of subroutine in package is not reflected in any view.
2)How i will append the data for each iteration of "SELECT * FROM deptree;"
Do i need to use utl_file here?
Please suggest.
Thanks in Advance,
Pratap
|
|
|
|
|
|
|
|
| Re: Problem with Object Dependencies [message #174231 is a reply to message #173865] |
Fri, 26 May 2006 11:49 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Meanwhile,your first query works fine.
So, it is to do with your data.
scott@9i > break on name;
scott@9i > 1
1* select name,text from user_source where name in ('P1','P2','SOMEPROCEDURE') order by name
scott@9i >
scott@9i > /
NAME TEXT
---------------------------------------- ------------------------------------------------------------
P1 procedure p1 as
begin
someprocedure;
end;
P2 procedure p2 as
begin
p1;
end;
SOMEPROCEDURE procedure someprocedure
as
begin
null;
end;
13 rows selected.
scott@9i > get p1
1 SELECT A.* FROM(select decode(SUBSTR(NAME,3,1),'_',SUBSTR(NAME,1,2),SUBSTR(NAME,1,3)) MODULE,
2 NAME,TYPE,decode(SUBSTR(referenced_NAME,3,1),'_',SUBSTR(referenced_NAME,1,2),
3 SUBSTR(referenced_NAME,1,3)) REFERRED_MODULE,
4 REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where TYPE IN ('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE' ) AND
5 REFERENCED_TYPE IN('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE') AND SUBSTR(NAME,1,3) !=
6* SUBSTR(REFERENCED_NAME,1,3) AND REFERENCED_OWNER = 'SCOTT') A CONNECT BY PRIOR REFERENCED_NAME = NAME
7
scott@9i > /
MOD NAME TYPE REF REFERENCED_NAME REFERENCED_TYPE
--- ---------------------------------------- ----------------- --- ------------------------------ -----------------
P1 P1 PROCEDURE SOM SOMEPROCEDURE PROCEDURE
P2 P2 PROCEDURE P1 P1 PROCEDURE
P1 P1 PROCEDURE SOM SOMEPROCEDURE PROCEDURE
|
|
|
|
| Re: Problem with Object Dependencies [message #174241 is a reply to message #173973] |
Fri, 26 May 2006 13:20 |
|
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
This is not the best solution.
I still like the idea of tweaking the utldtree.sql and get the results like we want.
That will be much effient code.
But for now, i am just worked my way around without changing any existing code.
You can make this any fancy you like.
--
-- I created a new table (same as deptree view with just one more column).
-- i also create a view called deptree
--
scott@9i > desc deptree_shadow
Name Null? Type
----------------------------------- -------- ------------------------
NESTED_LEVEL NUMBER
TYPE VARCHAR2(18)
SCHEMA VARCHAR2(30)
NAME VARCHAR2(30)
SEQ# NUMBER
FOR_OBJECT VARCHAR2(30)
scott@9i > Select view_name,text from user_views where view_name='DEPTREE_VIEW';
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
DEPTREE_VIEW select "NESTED_LEVEL","TYPE","SCHEMA","NAME","SEQ#" from deptree order by seq#
--
-- This is a simple script, that takes one input (object_type)
-- I execute deptree_fill procedure.
-- and archive the records in deptree view to deptree_shadow table
-- I use deptree_view to presort the data.
-- Do the same for every object in a loop
-- Now Deptree_shadow has the information you want for all objects.
-- You can spool the output/ do whatever you want and remove them later.
--
oracle@mutation#cat get_dependencies
#!/usr/bin/bash
sqlplus -s scott/tiger <<EOF
declare
cursor c1 is select distinct object_name from user_objects where object_type=upper('$1');
begin
dbms_output.enable('1000000');
DBMS_OUTPUT.PUT_LINE ('Following $1 were found');
for mag in c1 loop
exit when c1%notfound;
DBMS_OUTPUT.PUT_LINE(mag.object_name);
deptree_fill('$1','SCOTT',mag.object_name);
insert into deptree_shadow(select NESTED_LEVEl,type,SCHEMA,NAME,SEQ#,mag.object_name from deptree_view);
end loop;
commit;
end;
/
set linesize 1000;
break on for_object;
select for_object,NESTED_LEVEl,type,SCHEMA,NAME,SEQ# from deptree_shadow order by for_object;
truncate table deptree_shadow;
exit;
EOF
oracle@mutation#get_dependencies procedure
Following procedure were found
ANOTHER_PROCEDURE
DEPTREE_FILL
P1
P2
SOMEPROCEDURE
PL/SQL procedure successfully completed.
FOR_OBJECT NESTED_LEVEL TYPE SCHEMA NAME SEQ#
------------------------------ ------------ ------------------ ------------------------------ ------------------------------ ----------
ANOTHER_PROCEDURE 0 PROCEDURE SCOTT ANOTHER_PROCEDURE 0
DEPTREE_FILL 0 PROCEDURE SCOTT DEPTREE_FILL 0
P1 0 PROCEDURE SCOTT P1 0
1 PROCEDURE SCOTT P2 59
P2 0 PROCEDURE SCOTT P2 0
SOMEPROCEDURE 0 PROCEDURE SCOTT SOMEPROCEDURE 0
1 PROCEDURE SCOTT P1 60
2 PROCEDURE SCOTT P2 61
8 rows selected.
Table truncated.
oracle@mutation#get_dependencies view
Following view were found
DEPTREE
DEPTREE_VIEW
DVIEW
IDEPTREE
PL/SQL procedure successfully completed.
FOR_OBJECT NESTED_LEVEL TYPE SCHEMA NAME SEQ#
------------------------------ ------------ ------------------ ------------------------------ ------------------------------ ----------
DEPTREE 0 VIEW SCOTT DEPTREE 0
1 VIEW SCOTT IDEPTREE 62
1 VIEW SCOTT DEPTREE_VIEW 63
DEPTREE_VIEW 0 VIEW SCOTT DEPTREE_VIEW 0
DVIEW 0 VIEW SCOTT DVIEW 0
IDEPTREE 0 VIEW SCOTT IDEPTREE 0
6 rows selected.
Table truncated.
|
|
|
|
| Re: Problem with Object Dependencies [message #174934 is a reply to message #173685] |
Wed, 31 May 2006 01:28 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi Mahesh,
i tried as you have described.
It works!Thanks!
However, is there a way to get (list) all the procedures in a package / package body(although not dependency)?
Thanks and Regards,
Pratap
|
|
|
|
| Re: Problem with Object Dependencies [message #174941 is a reply to message #174934] |
Wed, 31 May 2006 01:52 |
|
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
One simple method is already know is to describe 'em.
scott@9i > desc dbms_output
PROCEDURE DISABLE
PROCEDURE ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BUFFER_SIZE NUMBER(38) IN DEFAULT
PROCEDURE GET_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 OUT
STATUS NUMBER(38) OUT
PROCEDURE GET_LINES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINES TABLE OF VARCHAR2(255) OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN
PROCEDURE PUT_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
PROCEDURE PUT_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 15:56:08 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
