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.
