In two earlier posts, about 11.1.0.6 oddities and my last one about 11.1.0.7 I mentioned not being able to find the column dependency information used in fine grained dependency tracking in the regular USER_% views of the data dictionary. I still haven't, but on the second post, Toon Koppelaars commented:I did some research in this area a few months ago. The FGDT information is stored in sys.dependency$.d_attrs. In the value Oracle seems to bitwise encode the columns that are used.
For instance if you have table T with three columns C1, C2 and C3. And you create view V as select C1,C3 from T. Then you are using the first and third column. This is encoded as power(2,1) + power(2,3) which is 10, which (in RAW) maps to hexidecimal value 'A'. You will see a raw-value in d_attrs that ends with 'A'.
I have not yet discovered what the role of the d_reason column value is playing in this area. Would be nice if Oracle would document this...
Let's see for ourselves:
rwijk@ORA11GR1> desc sys.dependency$
Naam Null? Type
----------------------------------------- -------- ----------------------------
D_OBJ# NOT NULL NUMBER
D_TIMESTAMP NOT NULL DATE
ORDER# NOT NULL NUMBER
P_OBJ# NOT NULL NUMBER
P_TIMESTAMP NOT NULL DATE
D_OWNER# NUMBER
PROPERTY NOT NULL NUMBER
D_ATTRS RAW(2000)
D_REASON RAW(2000)
The last two columns are not used in DBA_DEPENDENCIES. To see how d_attrs is used, let's create a table with 30 columns:
rwijk@ORA11GR1> create table mytable
2 ( col1 int
3 , col2 int
4 , col3 int
5 , col4 int
6 , col5 int
7 , col6 int
8 , col7 int
9 , col8 int
10 , col9 int
11 , col10 int
12 , col11 int
13 , col12 int
14 , col13 int
15 , col14 int
16 , col15 int
17 , col16 int
18 , col17 int
19 , col18 int
20 , col19 int
21 , col20 int
22 , col21 int
23 , col22 int
24 , col23 int
25 , col24 int
26 , col25 int
27 , col26 int
28 , col27 int
29 , col28 int
30 , col29 int
31 , col30 int
32 )
33 /
Tabel is aangemaakt.
And create a view using just two columns, col1 and col3:
rwijk@ORA11GR1> create view myview as select col1, col3 from mytable
2 /
View is aangemaakt.
If we use the query that's underneath the DBA_DEPENDENCIES view, and extend it with the columns d_attrs and d_reason (printed in bold), we see this:
rwijk@ORA11GR1> select u.name owner, o.name name,
2 decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
3 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
4 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
5 11, 'PACKAGE BODY', 12, 'TRIGGER',
6 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
7 28, 'JAVA SOURCE', 29, 'JAVA CLASS',
8 32, 'INDEXTYPE', 33, 'OPERATOR',
9 42, 'MATERIALIZED VIEW', 43, 'DIMENSION',
10 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA',
11 59, 'RULE', 62, 'EVALUATION CONTXT',
12 92, 'CUBE DIMENSION', 93, 'CUBE',
13 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
14 'UNDEFINED') type,
15 decode(po.linkname, null, pu.name, po.remoteowner) referenced_owner,
16 po.name referenced_name,
17 decode(po.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
18 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
19 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
20 11, 'PACKAGE BODY', 12, 'TRIGGER',
21 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
22 28, 'JAVA SOURCE', 29, 'JAVA CLASS',
23 32, 'INDEXTYPE', 33, 'OPERATOR',
24 42, 'MATERIALIZED VIEW', 43, 'DIMENSION',
25 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA',
26 59, 'RULE', 62, 'EVALUATION CONTXT',
27 92, 'CUBE DIMENSION', 93, 'CUBE',
28 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
29 'UNDEFINED') referenced_type,
30 po.linkname referenced_link_name,
31 decode(bitand(d.property, 3), 2, 'REF', 'HARD') dependency_type
32 , d.d_attrs
33 , d.d_reason
34 from sys."_CURRENT_EDITION_OBJ" o, sys.disk_and_fixed_objects po,
35 sys.dependency$ d, sys.user$ u, sys.user$ pu
36 where o.obj# = d.d_obj#
37 and o.owner# = u.user#
38 and po.obj# = d.p_obj#
39 and po.owner# = pu.user#
40 and o.name = 'MYVIEW'
41 /
OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
DEPE D_ATTRS
---- -------------------------------------------------
D_REASON
--------------------------------------------------------------------------------
RWIJK MYVIEW VIEW
RWIJK
MYTABLE
TABLE
HARD 000100000A
1 rij is geselecteerd.
d_attrs contains the raw value "000100000A". Indeed the 'A' value Toon mentioned. But what's up with those first characters? And if this a hexadecimal value, how will it behave when the 30th column is referenced, for example? This is what I did to find out:
rwijk@ORA11GR1> create procedure myproc1 as l_col1 mytable.col1%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc2 as l_col2 mytable.col2%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc3 as l_col3 mytable.col3%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc4 as l_col4 mytable.col4%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc5 as l_col5 mytable.col5%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc6 as l_col6 mytable.col6%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc7 as l_col7 mytable.col7%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc8 as l_col8 mytable.col8%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc9 as l_col9 mytable.col9%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc10 as l_col10 mytable.col10%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc11 as l_col11 mytable.col11%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc12 as l_col12 mytable.col12%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc13 as l_col13 mytable.col13%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc14 as l_col14 mytable.col14%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc15 as l_col15 mytable.col15%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc16 as l_col16 mytable.col16%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc17 as l_col17 mytable.col17%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc18 as l_col18 mytable.col18%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc19 as l_col19 mytable.col19%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc20 as l_col20 mytable.col20%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc21 as l_col21 mytable.col21%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc22 as l_col22 mytable.col22%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc23 as l_col23 mytable.col23%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc24 as l_col24 mytable.col24%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc25 as l_col25 mytable.col25%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc26 as l_col26 mytable.col26%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc27 as l_col27 mytable.col27%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc28 as l_col28 mytable.col28%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc29 as l_col29 mytable.col29%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> create procedure myproc30 as l_col30 mytable.col30%type; begin null; end;
2 /
Procedure is aangemaakt.
rwijk@ORA11GR1> select o.name name, d.d_attrs attrs
2 from sys."_CURRENT_EDITION_OBJ" o, sys.disk_and_fixed_objects po,
3 sys.dependency$ d, sys.user$ u, sys.user$ pu
4 where o.obj# = d.d_obj#
5 and o.owner# = u.user#
6 and po.obj# = d.p_obj#
7 and po.owner# = pu.user#
8 and o.name like 'MYPROC%'
9 and d.d_attrs is not null
10 order by to_number(substr(name,7))
11 /
NAME ATTRS
------------------------------ -------------------------------------------------
MYPROC1 0001000002
MYPROC2 0001000004
MYPROC3 0001000008
MYPROC4 0001000010
MYPROC5 0001000020
MYPROC6 0001000040
MYPROC7 0001000080
MYPROC8 000100000001
MYPROC9 000100000002
MYPROC10 000100000004
MYPROC11 000100000008
MYPROC12 000100000010
MYPROC13 000100000020
MYPROC14 000100000040
MYPROC15 000100000080
MYPROC16 00010000000001
MYPROC17 00010000000002
MYPROC18 00010000000004
MYPROC19 00010000000008
MYPROC20 00010000000010
MYPROC21 00010000000020
MYPROC22 00010000000040
MYPROC23 00010000000080
MYPROC24 0001000000000001
MYPROC25 0001000000000002
MYPROC26 0001000000000004
MYPROC27 0001000000000008
MYPROC28 0001000000000010
MYPROC29 0001000000000020
MYPROC30 0001000000000040
30 rijen zijn geselecteerd.
The first 8 characters are always "00010000". They probably have some meaning, but not for the purpose of finding out the column dependency information. And the rest is not a regular number in hexadecimal format. The bits are grouped byte wise, but in reverse order. With this information we can create our own dba_dependency_columns view:
rwijk@ORA11GR1> conn sys/... as sysdba
Verbonden.
sys@ORA11GR1> grant select on "_CURRENT_EDITION_OBJ" to rwijk
2 /
Toekennen is geslaagd.
sys@ORA11GR1> grant select on disk_and_fixed_objects to rwijk
2 /
Toekennen is geslaagd.
sys@ORA11GR1> grant select on dependency$ to rwijk
2 /
Toekennen is geslaagd.
sys@ORA11GR1> grant select on user$ to rwijk
2 /
Toekennen is geslaagd.
sys@ORA11GR1> grant select on col$ to rwijk
2 /
Toekennen is geslaagd.
sys@ORA11GR1> conn rwijk/...
Verbonden.
rwijk@ORA11GR1> set linesize 80
rwijk@ORA11GR1> create view dba_dependency_columns
2 as
3 select d.u_name owner
4 , d.o_name name
5 , decode
6 ( d.o_type#
7 , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER'
8 , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE'
9 , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT'
10 , 11, 'PACKAGE BODY', 12, 'TRIGGER'
11 , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY'
12 , 28, 'JAVA SOURCE', 29, 'JAVA CLASS'
13 , 32, 'INDEXTYPE', 33, 'OPERATOR'
14 , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION'
15 , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA'
16 , 59, 'RULE', 62, 'EVALUATION CONTXT'
17 , 92, 'CUBE DIMENSION', 93, 'CUBE'
18 , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS'
19 , 'UNDEFINED'
20 ) type
21 , nvl2( d.po_linkname, d.po_remoteowner, d.pu_name) referenced_owner
22 , d.po_name referenced_name
23 , decode
24 ( d.po_type#
25 , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER'
26 , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE'
27 , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT'
28 , 11, 'PACKAGE BODY', 12, 'TRIGGER'
29 , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY'
30 , 28, 'JAVA SOURCE', 29, 'JAVA CLASS'
31 , 32, 'INDEXTYPE', 33, 'OPERATOR'
32 , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION'
33 , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA'
34 , 59, 'RULE', 62, 'EVALUATION CONTXT'
35 , 92, 'CUBE DIMENSION', 93, 'CUBE'
36 , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS'
37 , 'UNDEFINED'
38 ) referenced_type
39 , d.po_linkname referenced_link_name
40 , c.name referenced_column
41 , decode(bitand(d.d_property, 3), 2, 'REF', 'HARD') dependency_type
42 from ( select obj#
43 , u_name
44 , o_name
45 , o_type#
46 , pu_name
47 , po_name
48 , po_type#
49 , po_remoteowner
50 , po_linkname
51 , d_property
52 , colpos
53 from sys."_CURRENT_EDITION_OBJ" o
54 , sys.disk_and_fixed_objects po
55 , sys.dependency$ d
56 , sys.user$ u
57 , sys.user$ pu
58 where o.obj# = d.d_obj#
59 and o.owner# = u.user#
60 and po.obj# = d.p_obj#
61 and po.owner# = pu.user#
62 and d.d_attrs is not null
63 model
64 return updated rows
65 partition by
66 ( po.obj# obj#
67 , u.name u_name
68 , o.name o_name
69 , o.type# o_type#
70 , po.linkname po_linkname
71 , pu.name pu_name
72 , po.remoteowner po_remoteowner
73 , po.name po_name
74 , po.type# po_type#
75 , d.property d_property
76 )
77 dimension by (0 i)
78 measures (0 colpos, substr(d.d_attrs,9) attrs)
79 rules iterate (1000)
80 until (iteration_number = 4 * length(attrs[0]) - 2)
81 ( colpos[iteration_number+1]
82 = case bitand
83 ( to_number
84 ( substr
85 ( attrs[0]
86 , 1 + 2*trunc((iteration_number+1)/8)
87 , 2
88 )
89 ,'XX'
90 )
91 , power(2,mod(iteration_number+1,8))
92 )
93 when 0 then null
94 else iteration_number+1
95 end
96 )
97 ) d
98 , sys.col$ c
99 where d.obj# = c.obj#
100 and d.colpos = c.col#
101 /
View is aangemaakt.
rwijk@ORA11GR1> desc dba_dependency_columns
Naam Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(18)
REFERENCED_OWNER VARCHAR2(30)
REFERENCED_NAME VARCHAR2(64)
REFERENCED_TYPE VARCHAR2(18)
REFERENCED_LINK_NAME VARCHAR2(128)
REFERENCED_COLUMN NOT NULL VARCHAR2(30)
DEPENDENCY_TYPE VARCHAR2(4)
The view lists exactly the same columns as the DBA_DEPENDENCIES view, with only one extra column called REFERENCED_COLUMN. Information in this view only shows up when column dependencies are present, so if you ever want to join this one with DBA_DEPENDENCIES, be sure to use an outer join.
A short explanation for the query itself: the inner select statement with the model clause, extends the result set with just as many rows as there are columns. The columns that are dependent have a non null colpos value, corresponding to the column position according to d_attrs. At the outer level this set is joined to sys.col$ to get the corresponding real column names. The rows that have a null value for colpos will leave the result set.
Now let's see how this works for another view:
rwijk@ORA11GR1> create view myview2 as select col2, col3, col15, col28 from mytable
2 /
View is aangemaakt.
rwijk@ORA11GR1> select *
2 from dba_dependencies
3 where name = 'MYVIEW2'
4 /
OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
DEPE
----
RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE
HARD
1 rij is geselecteerd.
rwijk@ORA11GR1> select *
2 from dba_dependency_columns
3 where name = 'MYVIEW2'
4 order by to_number(substr(referenced_column,4))
5 /
OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
REFERENCED_COLUMN DEPE
------------------------------ ----
RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE
COL2 HARD
RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE
COL3 HARD
RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE
COL15 HARD
RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE
COL28 HARD
4 rijen zijn geselecteerd.
Great! And for a procedure:
rwijk@ORA11GR1> create procedure p2
2 as
3 l_col18 mytable.col18%type;
4 begin
5 select col16
6 into l_col18
7 from mytable
8 where col30 = 1
9 and col4 = 23
10 ;
11 update myview2
12 set col15 = 3
13 where col2 = 4
14 ;
15 end p2;
16 /
Procedure is aangemaakt.
rwijk@ORA11GR1> select *
2 from dba_dependencies
3 where name = 'P2'
4 /
OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
DEPE
----
RWIJK P2 PROCEDURE
SYS
SYS_STUB_FOR_PURITY_ANALYSIS
PACKAGE
HARD
RWIJK P2 PROCEDURE
SYS
STANDARD
PACKAGE
HARD
RWIJK P2 PROCEDURE
RWIJK
MYVIEW2
VIEW
HARD
RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE
HARD
4 rijen zijn geselecteerd.
rwijk@ORA11GR1> select *
2 from dba_dependency_columns
3 where name = 'P2'
4 order by referenced_name
5 , to_number(substr(referenced_column,4))
6 /
OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
REFERENCED_COLUMN DEPE
------------------------------ ----
RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE
COL4 HARD
RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE
COL16 HARD
RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE
COL18 HARD
RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE
COL30 HARD
RWIJK P2 PROCEDURE
RWIJK
MYVIEW2
VIEW
COL2 HARD
RWIJK P2 PROCEDURE
RWIJK
MYVIEW2
VIEW
COL15 HARD
6 rijen zijn geselecteerd.
Good as well. No guarantees are given however that this view will always give the right results, but for these simple examples, it does.
Cool stuff. I enjoyed your adventure into the internals.
ReplyDelete8-)
Thanks for a really good job on discovering the initial attribute logic!
ReplyDeleteI recently (sorry, in Dulcian we are slow at adopting 11g) decided to take it one step further and get fine-grained PL/SQL dependencies out of the same D_ATTRS. What I was able to discover is that modifying your example to join to SYS.PROCEDUREINFO$ instead of SYS.COL$ does the job with one huge IF.
The package spec should contain only procedure/function declarations. Any package-level type declarations or global variables completely mess up the count. I tried to search Oracle's data dictionary, but without any success.
I also found a couple of D_ATTRS templates (like rowtype references or count(*) without any column reference), but I start to feel that I am reinventing the bicycle!
Just wondering, did you ever continue discovery of internals of fine-grained invalidation? Or did you hear about anybody else digging in that direction?
If not - I think, we should talk to Bryn at OpenWorld. It is just too powerful of a feature (for anybody who likes repository-based solutions) just to keep it in a "black-bock" mode.
Michael,
ReplyDeleteNo i did not continue my discovery, nor do I know of anybody who did. This functionality will sooner or later be part of the data dictionary, undoubtedly. Talking to Bryn about this, is a good idea.
Regards,
Rob.
Rob,
ReplyDeleteGreat information here, sad that not much more has come from this from Oracle since you initial post. I have been looking at this recently as well and have found that what you have above does not include objects referencing a synonym instead of the underlying table. Was curious if you have seen this behavior as well and if you have already made changes to this that you can share?
Thanks,
Sean
Just used this to full satisfaction of the person requesting the information.
ReplyDeleteThank you very much :)
The DBA_DEPENDENCY_COLUMNS works as expected and didn't work for some views (nothing is selected). The root cause was because the sys.col$ had nothing in it for those objects. Is there a quick solution to that?
ReplyDeleteThanks,
Eric
@Eric: More details are required for me to give a meaningful answer
ReplyDeleteWhat kind of details would you need? The sys.col$ has some missing information for the views needed to be analysed, so the view DBA_DEPENDENCY_COLUMNS doesn't return anything. I have open an Oracle SR to help me, but we are only in the early stage of the troubleshooting.
ReplyDeleteI have figured out a workaround. I have replaced the v$col reference with DBA_TAB_COLS. Thanks for the wonderful solution (99.9% of the solution was there)!
ReplyDeleteMore logic needed, it's not handling complex views (views that call functions, which themselves select new columns). Also complex views can have X number of levels. Let me know if you have ideas on how to extend this solution.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi Rob,
ReplyDeleteSad that not much more has come from this from Oracle since you initial post. May be the SR created by Eric Morneault solved the problem? Can we obtain an updated version of the view DBA_DEPENDENCY_COLUMNS ?
Thanks a lot! Great job!
Rob, thanks a lot for this article!
ReplyDeleteFor everyone who wants this feature: You can vote now on OTN
https://community.oracle.com/ideas/12317
This comment has been removed by the author.
ReplyDeleteVery nice. Unfortunately, it does not work if the view references a synonym:
ReplyDeletecreate or replace view myview as select col1, col3 from mytable;
select name,referenced_name,referenced_column from dba_dependency_columns where name = 'MYVIEW';
MYVIEW MYTABLE COL1
MYVIEW MYTABLE COL3
create synonym mysyn for mytable;
create or replace view myview as select col1, col3 from mysyn;
select name,referenced_name,referenced_column from dba_dependency_columns where name = 'MYVIEW';
no rows selected.
Some comments, bearing in mind, of course, that the post was originally written in 2008 and referred to 11gR1:
ReplyDelete1. D_ATTRS contains values other than "000100000A". I've observed this in a basic 12c install and a production 11gR2 install
2. D_ATTRS is commented in $ORACLE_HOME/rdbms/admin/dcore.bsq as "/* Finer grain attr. numbers if finer grained */"
3. D_REASON is commented in $ORACLE_HOME/rdbms/admin/dcore.bsq as "/* Reason mask of attrs causing invalidation */". On my basic 12c installation, all rows contain NULL for this value although on a production 11gR2 database I observed a handful of rows with values in this column.
Also, in 12c (12.1.0.2) for a VIEW: a GROUP BY column, if that column name shows up in another table of the view (even though not referenced), it is flagged as a dependency for any other tables with that column:
ReplyDeletecreate table t1 (c1 number, c2 number);
create table t2 (c1 number, c2 number);
create or replace view rjb.test_dep2 as
select count(1) col
from
rjb.t1 tab1
left outer join rjb.t2 tab2
on tab1.c1 = tab2.c1
group by
tab1.c2;
So, t2.c2 (tab2.c2) shows up in the dependency list even though it's not referenced anywhere.
AND it's tied to the LEFT OUTER JOIN, an inner join does not trigger the issue.
DeleteHi Tom,
ReplyDeleteThis is great solution and helped me. Is there any way to find the kind of Type of Operations on Columns present in SQL Procedure.
Ex: SELECT , UPDATE, MERGE
Hi Rob,
ReplyDeleteIt's brilliant!
We work on this problem find dependecies on column level. This time we can find and draw operations between fields(variables), SQL keywords and it's meaning. So we know almost full data flow graph of an oracle database.
https://craftilytools.com/desql-details/
This has saved me a lot of work doing an impact analysis. Thanks a lot. Good job! When will Oracle awake and incorporate this in their next version?
ReplyDelete