You Asked
I'm trying to find all the objects that my view uses like this:
select lpad(' ',(level-1)*2)||name||'('|| type || ')' from dba_dependencies
where level <2
connect by prior referenced_owner=owner
and referenced_name=name
start with name = 'DBA_VIEWS'
but oracle tell's me that there is a loop in the data. Clearly, it executes hierarchical query first, so that even putting a condition, say, "level < 4" doesn't help. Any workaround?
Also, any query against dba_dependencies is so slow, espetially on large database. Is dba_dependencies tuned at all?
and Tom said...
Well, all of the objects you view uses is the first level only (and there can be cyclical dependencies in this view, its not a good candidate for this)
It is a slow view. If you look at it, is a view of some other views, some of which are union all views of many tables. It is tuned -- its just a VERY hard question you are asking (the dba_dependencies question is a hard question to answer). The data dictionary is stored for operational access, not "data mining", so this one's performance is impacted by that.
So, the question becomes: can we satisfy your desired goal in some way without changing the data dictionary, they way oracle works etc..... Yes, I believe we can:
ops$tkyte@ORA9I.WORLD> create or replace type myScalarType as object
2 ( lvl number,
3 rname varchar2(30),
4 rowner varchar2(30),
5 rtype varchar2(30)
6 )
7 /
Type created.
ops$tkyte@ORA9I.WORLD> create or replace type myTableType as table of myScalarType
2 /
Type created.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> create or replace
2 function depends( p_name in varchar2,
3 p_type in varchar2,
4 p_owner in varchar2 default USER,
5 p_lvl in number default 1 ) return myTableType
6 AUTHID CURRENT_USER
7 as
8 l_data myTableType := myTableType();
9
10 procedure recurse( p_name in varchar2,
11 p_type in varchar2,
12 p_owner in varchar2,
13 p_lvl in number )
14 is
15 begin
16 if ( l_data.count > 1000 )
17 then
18 raise_application_error( -20001, 'probable connect by loop, aborting' );
19 end if;
20
21 for x in ( select /*+ first_rows */ referenced_name,
22 referenced_owner,
23 referenced_type
24 from dba_dependencies
25 where owner = p_owner
26 and type = p_type
27 and name = p_name )
28 loop
29 l_data.extend;
30 l_data(l_data.count) :=
31 myScalarType( p_lvl, x.referenced_name,
32 x.referenced_owner, x.referenced_type );
33 recurse( x.referenced_name, x.referenced_type,
34 x.referenced_owner, p_lvl+1);
35 end loop;
36 end;
37 begin
38 l_data.extend;
39 l_data(l_data.count) := myScalarType( 1, p_name, p_owner, p_type );
40 recurse( p_name, p_type, p_owner, 2 );
41 return l_data;
42 end;
43 /
Function created.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> set timing on
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> select * from table( cast(depends('DBA_VIEWS','VIEW','SYS') as myTableType ) );
LVL RNAME ROWNER RTYPE
--- --------------- --------- ---------------
1 DBA_VIEWS SYS VIEW
2 TYPED_VIEW$ SYS TABLE
2 USER$ SYS TABLE
2 VIEW$ SYS TABLE
2 OBJ$ SYS TABLE
2 SUPEROBJ$ SYS TABLE
2 STANDARD SYS PACKAGE
7 rows selected.
Elapsed: 00:00:00.42
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> drop table t;
Table dropped.
Elapsed: 00:00:00.13
ops$tkyte@ORA9I.WORLD> create table t ( x int );
Table created.
Elapsed: 00:00:00.03
ops$tkyte@ORA9I.WORLD> create or replace view v1 as select * from t;
View created.
Elapsed: 00:00:00.06
ops$tkyte@ORA9I.WORLD> create or replace view v2 as select t.x xx, v1.x yy from v1, t;
View created.
Elapsed: 00:00:00.06
ops$tkyte@ORA9I.WORLD> create or replace view v3 as select v2.*, t.x xxx, v1.x yyy from v2, v1, t;
View created.
Elapsed: 00:00:00.07
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> select lpad(' ',lvl*2,' ') || rowner || '.' || rname || '(' || rtype || ')' hierarchy
2 from table( cast(depends('V3','VIEW') as myTableType ) );
HIERARCHY
-----------------------------------------------------------------------------------------------------------------------------------
OPS$TKYTE.V3(VIEW)
OPS$TKYTE.T(TABLE)
OPS$TKYTE.V1(VIEW)
OPS$TKYTE.T(TABLE)
OPS$TKYTE.V2(VIEW)
OPS$TKYTE.T(TABLE)
OPS$TKYTE.V1(VIEW)
OPS$TKYTE.T(TABLE)
8 rows selected.
Elapsed: 00:00:00.47
ops$tkyte@ORA9I.WORLD>
(note: this in was in Oracle9i -- in 8i, the query doesn't go quite as fast, I didn't tune it for that release, that would be an exercise for the reader...)
Rating
(2 ratings)
We're not taking comments currently, so please try again later if you want to add a comment.