For all Dutch readers interested in the SQL model clause (I wonder how selective those two predicates are ...): the last part of the SQL Model Clause Tutorial is published in the winter edition 2008 of OGh Visie. The subject is "Practicalities of the SQL Model Clause" and it tries to answer the question for which kind of problems a model clause query is best applied.
For all non Dutch readers interested in the SQL model clause: a translation of this article will appear on this blog very soon.
Pages
▼
Monday, December 29, 2008
Wednesday, December 17, 2008
Plan a schedule with the SQL model clause
My DBA colleague Ronald Rood asked an interesting question on OTN's SQL and PL/SQL forum. I'll repeat the question here. First the setup of the table:
And the accompanying question:
In real life I would never implement a SQL only solution for such a problem, because the resulting SQL will be quite hard to maintain, even when properly documented. But it sure is fun to do it using only SQL.
If you clicked the link in the beginning of the post, you might have seen a solution already. In this post I will try to explain how I solved this problem which may look very difficult at first. I think it demonstrates the power of the SQL model clause very well.
The first thing to do here, is to normalize the table, by splitting the comma separated string into separate rows, one row for each day. Without such a normalized set, a solution would be much harder. To split the string into several rows, I use the technique that resembles the one described in this post, particularly the SQL model clause variant. This query does this part of the job:
This query puts every row in its own partition, and indexes this row with dimension i set to 0. These rows are the original ones, which we won't return, because of the use of the keyword RETURN UPDATED ROWS. The model creates new cells with dimension values from 1 and upwards. In each partition the number of commas is calculated with regexp_count(days[0],','). The number of commas plus one is the number of rows that have to be generated. For each new row, regexp_substr(days[0],'[^,]+',1,cv(i)) gives the cv(i)-th element in the string.
The less trickier part of the query is to change some datatypes: days is set to varchar2(3) for a prettier layout only, and the start_time and end_time should be dates for easier calculating. You see that a default date is chosen, being the first day of the current month. This part of the date is irrelevant. Using intervals (numtodsinterval) I can now easily add the number of minutes with the start_time to calculate the end_time.
This was the easy part :-). Next challenge is to process all rows with an empty start_time and allocate a time slot to them. The question does not describe an algorithm how to do this, so I choose an easy one: process all rows with an empty start time and start with the longest one. For each of these rows, assign the row to the largest gap in time, at the beginning of the interval. A few challenges here: determining the gaps, adjusting the gaps after an item has been assigned in a gap and detecting when an item doesn't fit in any gap.
For these challenges I put up another model, partitioned by day. The rows inside each partition are indexed by a number with the row_number analytic function, the rows with an empty start_time first. Like this I have a nice dimension I can iterate over AND I can use the UNTIL clause to only iterate over the ones with a empty start_time. For this I have to introduce the cnt measure, that counts the number of empty start_times in a partition. So in each partition there will be as many iterations as there are cells with empty start_times.
Let's first show the resulting query and then explain what's going on:
In the with clause you see the normalized query discussed earlier. The part I'm discussing here, starts at line 18. For each iteration I have to choose which of the rows has the largest open time slot attached at the end. For this the auxiliary measure next_start_time is introduced, calculated with the lead analytic function. The index of the row with the largest open time slot is stored in the auxiliary measure rn_with_largest_gap. Each iteration starts with calculating this value again. All subsequent rules of the models use this rn_with_largest_gap measure. Rules 3 and 4 calculate the start_time and end_time of the rows that started out with an empty start_time. The last two rules adjust the next_start_time measures to the new situation: since the new allocated time slot is adjacent to the existing time slot, the next_start_time of the original one is set to null, and the next_start_time of the new time slot is the original next_start_time.
Last part of the solution is to addition of the fit measure. With this measure and the second rule, I can check whether the largest open time slot is large enough for the item. If it is not large enough, I put in a text "doesn't fit" in this cell. All subsequent rules effectively do nothing when a non null value is encountered in this cell. With the current data, everything fits, but if you click on the link to the original question, you'll see a situation where two rows don't fit.
If you have not given up and have read up through here, you maybe agree with me that this is another nice example of a complex algorithm that can be solved with only SQL. Although in this category, nothing beats this one of course. But remember kids: don't try this at work ;-)
rwijk@ORA11GR1> create table sschedule (
2 item varchar2(10)
3 , days varchar2(30)
4 , fixed_start_time varchar2(5)
5 , minutes number
6 )
7 /
Tabel is aangemaakt.
rwijk@ORA11GR1> insert into sschedule (item,days, fixed_start_time, minutes)
2 select 'lunch', 'mon,tue,wed,thu,fri','12:00',60 from dual union all
3 select 'a', 'tue,fri','10:00',60 from dual union all
4 select 'b', 'mon,wed',null, 120 from dual union all
5 select 'opening','mon,tue,wed,thu,fri','08:55', 5 from dual union all
6 select 'close','mon,tue,wed,thu','20:00', 5 from dual union all
7 select 'close','fri','16:00', 5 from dual union all
8 select 'c', 'mon,wed,fri',null, 20 from dual union all
9 select 'd', 'mon,wed,fri',null, 20 from dual union all
10 select 'diner','mon,tue,wed,thu','18:00', 60 from dual union all
11 select 'e','tue,thu,fri',null, 40 from dual union all
12 select 'keynote', 'mon','09:00', 120 from dual union all
13 select 'bye', 'fri','14:00', 120 from dual union all
14 select 'f','tue,thu,fri',null, 40 from dual union all
15 select 'g', 'mon,wed',null, 120 from dual
16 /
14 rijen zijn aangemaakt.
rwijk@ORA11GR1> select * from sschedule
2 /
ITEM DAYS FIXED MINUTES
---------- ------------------------------ ----- ----------
lunch mon,tue,wed,thu,fri 12:00 60
a tue,fri 10:00 60
b mon,wed 120
opening mon,tue,wed,thu,fri 08:55 5
close mon,tue,wed,thu 20:00 5
close fri 16:00 5
c mon,wed,fri 20
d mon,wed,fri 20
diner mon,tue,wed,thu 18:00 60
e tue,thu,fri 40
keynote mon 09:00 120
bye fri 14:00 120
f tue,thu,fri 40
g mon,wed 120
14 rijen zijn geselecteerd.
And the accompanying question:
"I have a table that contains a list of items that are to be discussed in a meeting, all taking place in the same room. For some meetings a guest speaker is invited, those meetings have a fixed start time. All meetings have a fixed duration in minutes. The days all start with opening and end with close. How can we generate the start times of the items that have no fixed start time set?
for friday this gives:ITEM FIXED MINUTES
------- ----- -------
opening 08:55 5
a 10:00 60
lunch 12:00 60
bye 14:00 120
close 16:00 5
c 20
d 20
e 40
f 40
e can start at 09:00 to fill the gap between opening and a.
c can start at 09:40 to fill the gap between e and a.
f can start at 11:00 to fill the gap between a and lunch.
d can start at 11:40 to fill the gap between f and lunch.
more combinations are very legal, as long as the fixed_start_times are honoured.
How can this list be generated in 1 sql?
When a day becomes overbooked the items that fall must get something like 'does not fit' to signal it's too busy that day."
In real life I would never implement a SQL only solution for such a problem, because the resulting SQL will be quite hard to maintain, even when properly documented. But it sure is fun to do it using only SQL.
If you clicked the link in the beginning of the post, you might have seen a solution already. In this post I will try to explain how I solved this problem which may look very difficult at first. I think it demonstrates the power of the SQL model clause very well.
The first thing to do here, is to normalize the table, by splitting the comma separated string into separate rows, one row for each day. Without such a normalized set, a solution would be much harder. To split the string into several rows, I use the technique that resembles the one described in this post, particularly the SQL model clause variant. This query does this part of the job:
rwijk@ORA11GR1> select item
2 , cast(days as varchar2(3)) day
3 , to_date(fixed_start_time,'hh24:mi') start_time
4 , minutes
5 , to_date(fixed_start_time,'hh24:mi')
6 + numtodsinterval(minutes,'minute') end_time
7 from sschedule
8 model
9 return updated rows
10 partition by (item,fixed_start_time,minutes)
11 dimension by (0 i)
12 measures (days)
13 ( days [for i from 1 to regexp_count(days[0],',') + 1 increment 1]
14 = regexp_substr(days[0],'[^,]+',1,cv(i))
15 )
16 order by decode(day,'mon',1,'tue',2,'wed',3,'thu',4,'fri',5)
17 , start_time
18 /
ITEM DAY START_TIME MINUTES END_TIME
---------- --- ------------------- ---------- -------------------
opening mon 01-12-2008 08:55:00 5 01-12-2008 09:00:00
keynote mon 01-12-2008 09:00:00 120 01-12-2008 11:00:00
lunch mon 01-12-2008 12:00:00 60 01-12-2008 13:00:00
diner mon 01-12-2008 18:00:00 60 01-12-2008 19:00:00
close mon 01-12-2008 20:00:00 5 01-12-2008 20:05:00
b mon 120
d mon 20
c mon 20
g mon 120
opening tue 01-12-2008 08:55:00 5 01-12-2008 09:00:00
a tue 01-12-2008 10:00:00 60 01-12-2008 11:00:00
lunch tue 01-12-2008 12:00:00 60 01-12-2008 13:00:00
diner tue 01-12-2008 18:00:00 60 01-12-2008 19:00:00
close tue 01-12-2008 20:00:00 5 01-12-2008 20:05:00
f tue 40
e tue 40
opening wed 01-12-2008 08:55:00 5 01-12-2008 09:00:00
lunch wed 01-12-2008 12:00:00 60 01-12-2008 13:00:00
diner wed 01-12-2008 18:00:00 60 01-12-2008 19:00:00
close wed 01-12-2008 20:00:00 5 01-12-2008 20:05:00
b wed 120
g wed 120
d wed 20
c wed 20
opening thu 01-12-2008 08:55:00 5 01-12-2008 09:00:00
lunch thu 01-12-2008 12:00:00 60 01-12-2008 13:00:00
diner thu 01-12-2008 18:00:00 60 01-12-2008 19:00:00
close thu 01-12-2008 20:00:00 5 01-12-2008 20:05:00
e thu 40
f thu 40
opening fri 01-12-2008 08:55:00 5 01-12-2008 09:00:00
a fri 01-12-2008 10:00:00 60 01-12-2008 11:00:00
lunch fri 01-12-2008 12:00:00 60 01-12-2008 13:00:00
bye fri 01-12-2008 14:00:00 120 01-12-2008 16:00:00
close fri 01-12-2008 16:00:00 5 01-12-2008 16:05:00
c fri 20
d fri 20
e fri 40
f fri 40
39 rijen zijn geselecteerd.
This query puts every row in its own partition, and indexes this row with dimension i set to 0. These rows are the original ones, which we won't return, because of the use of the keyword RETURN UPDATED ROWS. The model creates new cells with dimension values from 1 and upwards. In each partition the number of commas is calculated with regexp_count(days[0],','). The number of commas plus one is the number of rows that have to be generated. For each new row, regexp_substr(days[0],'[^,]+',1,cv(i)) gives the cv(i)-th element in the string.
The less trickier part of the query is to change some datatypes: days is set to varchar2(3) for a prettier layout only, and the start_time and end_time should be dates for easier calculating. You see that a default date is chosen, being the first day of the current month. This part of the date is irrelevant. Using intervals (numtodsinterval) I can now easily add the number of minutes with the start_time to calculate the end_time.
This was the easy part :-). Next challenge is to process all rows with an empty start_time and allocate a time slot to them. The question does not describe an algorithm how to do this, so I choose an easy one: process all rows with an empty start time and start with the longest one. For each of these rows, assign the row to the largest gap in time, at the beginning of the interval. A few challenges here: determining the gaps, adjusting the gaps after an item has been assigned in a gap and detecting when an item doesn't fit in any gap.
For these challenges I put up another model, partitioned by day. The rows inside each partition are indexed by a number with the row_number analytic function, the rows with an empty start_time first. Like this I have a nice dimension I can iterate over AND I can use the UNTIL clause to only iterate over the ones with a empty start_time. For this I have to introduce the cnt measure, that counts the number of empty start_times in a partition. So in each partition there will be as many iterations as there are cells with empty start_times.
Let's first show the resulting query and then explain what's going on:
rwijk@ORA11GR1> with schedule_normalized as
2 ( select item
3 , cast(days as varchar2(3)) day
4 , to_date(fixed_start_time,'hh24:mi') start_time
5 , minutes
6 , to_date(fixed_start_time,'hh24:mi')
7 + numtodsinterval(minutes,'minute') end_time
8 from sschedule
9 model
10 return updated rows
11 partition by (item,fixed_start_time,minutes)
12 dimension by (0 i)
13 measures (days)
14 ( days [for i from 1 to regexp_count(days[0],',') + 1 increment 1]
15 = regexp_substr(days[0],'[^,]+',1,cv(i))
16 )
17 )
18 select item
19 , day
20 , to_char(st,'hh24:mi') start_time
21 , to_char(et,'hh24:mi') end_time
22 , minutes
23 , fit
24 from schedule_normalized
25 model
26 partition by (day)
27 dimension by
28 ( row_number() over
29 (partition by day order by start_time nulls first, minutes desc) rn
30 )
31 measures
32 ( item
33 , start_time st
34 , minutes
35 , end_time et
36 , lead(start_time) over
37 (partition by day order by start_time) next_start_time
38 , count(nvl2(start_time,null,1)) over (partition by day) cnt
39 , 0 rn_with_largest_gap
40 , row_number() over
41 (partition by day order by start_time nulls first, minutes desc) rnm
42 , cast(null as varchar2(11)) fit
43 )
44 rules iterate(1000) until (iteration_number + 1 = cnt[1])
45 ( rn_with_largest_gap[1]
46 = max(rnm) keep
47 (dense_rank last order by next_start_time - et nulls first)[any]
48 , fit[iteration_number+1]
49 = case
50 when max(next_start_time - et)[any]
51 < minutes[iteration_number+1]/24/60
52 then 'doesn''t fit'
53 end
54 , st[iteration_number+1]
55 = case
56 when fit[iteration_number+1] is null
57 then et[rn_with_largest_gap[1]]
58 end
59 , et[iteration_number+1]
60 = case
61 when fit[iteration_number+1] is null
62 then et[rn_with_largest_gap[1]]
63 + numtodsinterval(minutes[iteration_number+1],'minute')
64 end
65 , next_start_time[iteration_number+1]
66 = case
67 when fit[iteration_number+1] is null
68 then next_start_time[rn_with_largest_gap[1]]
69 end
70 , next_start_time[rn_with_largest_gap[1]]
71 = case
72 when fit[iteration_number+1] is not null
73 then next_start_time[rn_with_largest_gap[1]]
74 end
75 )
76 order by decode(day,'mon',1,'tue',2,'wed',3,'thu',4,'fri',5)
77 , start_time
78 /
ITEM DAY START END_T MINUTES FIT
---------- --- ----- ----- ---------- -----------
opening mon 08:55 09:00 5
keynote mon 09:00 11:00 120
d mon 11:00 11:20 20
lunch mon 12:00 13:00 60
g mon 13:00 15:00 120
b mon 15:00 17:00 120
diner mon 18:00 19:00 60
c mon 19:00 19:20 20
close mon 20:00 20:05 5
opening tue 08:55 09:00 5
a tue 10:00 11:00 60
lunch tue 12:00 13:00 60
f tue 13:00 13:40 40
e tue 13:40 14:20 40
diner tue 18:00 19:00 60
close tue 20:00 20:05 5
opening wed 08:55 09:00 5
g wed 09:00 11:00 120
lunch wed 12:00 13:00 60
b wed 13:00 15:00 120
d wed 15:00 15:20 20
c wed 15:20 15:40 20
diner wed 18:00 19:00 60
close wed 20:00 20:05 5
opening thu 08:55 09:00 5
lunch thu 12:00 13:00 60
e thu 13:00 13:40 40
f thu 13:40 14:20 40
diner thu 18:00 19:00 60
close thu 20:00 20:05 5
opening fri 08:55 09:00 5
d fri 09:00 09:20 20
c fri 09:20 09:40 20
a fri 10:00 11:00 60
f fri 11:00 11:40 40
lunch fri 12:00 13:00 60
e fri 13:00 13:40 40
bye fri 14:00 16:00 120
close fri 16:00 16:05 5
39 rijen zijn geselecteerd.
In the with clause you see the normalized query discussed earlier. The part I'm discussing here, starts at line 18. For each iteration I have to choose which of the rows has the largest open time slot attached at the end. For this the auxiliary measure next_start_time is introduced, calculated with the lead analytic function. The index of the row with the largest open time slot is stored in the auxiliary measure rn_with_largest_gap. Each iteration starts with calculating this value again. All subsequent rules of the models use this rn_with_largest_gap measure. Rules 3 and 4 calculate the start_time and end_time of the rows that started out with an empty start_time. The last two rules adjust the next_start_time measures to the new situation: since the new allocated time slot is adjacent to the existing time slot, the next_start_time of the original one is set to null, and the next_start_time of the new time slot is the original next_start_time.
Last part of the solution is to addition of the fit measure. With this measure and the second rule, I can check whether the largest open time slot is large enough for the item. If it is not large enough, I put in a text "doesn't fit" in this cell. All subsequent rules effectively do nothing when a non null value is encountered in this cell. With the current data, everything fits, but if you click on the link to the original question, you'll see a situation where two rows don't fit.
If you have not given up and have read up through here, you maybe agree with me that this is another nice example of a complex algorithm that can be solved with only SQL. Although in this category, nothing beats this one of course. But remember kids: don't try this at work ;-)
Friday, December 5, 2008
GROUP_ID()
In this AMIS-post, Lucas Jellema was looking for a way to duplicate certain grouping sets for his ADF tree structure. Using ROLLUP this is not completely possible, but with GROUPING SETS it is, as I will show below. It was a small challenge however to distinguish the duplicate sets from each other in the select list and this is where I learned something new.
For example, let's start with this query:
A rollup with N arguments always leads to N+1 grouping sets. So here the rollup has 3 arguments and you can see 4 distinct values in the last gr_text column.
Now let's say we want the grouping sets () and deptno duplicated, leading to 4 extra rows. With rollup you can specify deptno twice:
But there is no way to specify the empty grouping set, as it is implicit with the rollup operator. And so it's also impossible to duplicate this empty grouping set with rollup. It is possible though with the grouping sets notation. First, let's rewrite the original rollup expression to the more tedious but clearer grouping sets notation, like this:
then it becomes very easy to duplicate the deptno and the empty grouping set. Just duplicate them in your grouping sets list of arguments:
Now my question was: how can I distinguish between the two equal grouping sets in my select list? Not with GROUPING_ID, because I'd have to mention the columns on which were grouped, not the grouping set. A grouping_id(deptno) would yield to 1 in both deptno grouping sets.
Using the row_number analytic function and including all columns present in a grouping set in the partition by clause, was what I thought of next. This would accomplish a "1" for all singular grouping sets, and an arbitrary 1 and 2 within the duplicate grouping sets, like this:
The problem is the ordering. There is nothing to order by, so I chose NULL. Any other constant value would have sufficed as well. But this ordering is arbitrary, meaning that two rows can both be 1 and can both be 2. And some implementation detail of Oracle has to decide which one it's going to be. And if I use the row_number function twice, would both expressions give the same results? It depends on the same implementation, so yes, both expressions give the same results:
But still I felt uncomfortable with this solution. I was pressing the Previous and Next buttons in the SQL Reference Manual to find any related functions and saw the GROUP_ID function. "This has got to be the most useless function ever" must have been my thoughts when I read about this function years ago. In fact, I had completely forgotten about it. But it's entire reason for being is a case like this with duplicate grouping sets, to be able to distinguish between the sets:
Here I have duplicated the empty grouping set 4 times and you see the outcome of the GROUP_ID() function for those five sets ranging from 0 to 4. This is exactly what is needed here, so now the initial query is quite easy, like this:
For example, let's start with this query:
rwijk@ORA11GR1> select deptno
2 , job
3 , empno
4 , ename
5 , sum(sal) sumsal
6 , case grouping_id(deptno,job,empno)
7 when 0 then 'grouped by deptno,job,empno,ename'
8 when 1 then 'grouped by deptno,job'
9 when 3 then 'grouped by deptno'
10 when 7 then 'grouped by ()'
11 end gr_text
12 from emp
13 group by rollup(deptno,job,(empno,ename))
14 order by deptno
15 , job
16 , empno
17 /
DEPTNO JOB EMPNO ENAME SUMSAL GR_TEXT
------ --------- ------ ---------- ------ ---------------------------------
10 CLERK 7934 MILLER 1300 grouped by deptno,job,empno,ename
10 CLERK 1300 grouped by deptno,job
10 MANAGER 7782 CLARK 2450 grouped by deptno,job,empno,ename
10 MANAGER 2450 grouped by deptno,job
10 PRESIDENT 7839 KING 5000 grouped by deptno,job,empno,ename
10 PRESIDENT 5000 grouped by deptno,job
10 8750 grouped by deptno
20 ANALYST 7788 SCOTT 3000 grouped by deptno,job,empno,ename
20 ANALYST 7902 FORD 3000 grouped by deptno,job,empno,ename
20 ANALYST 6000 grouped by deptno,job
20 CLERK 7369 SMITH 800 grouped by deptno,job,empno,ename
20 CLERK 7876 ADAMS 1100 grouped by deptno,job,empno,ename
20 CLERK 1900 grouped by deptno,job
20 MANAGER 7566 JONES 2975 grouped by deptno,job,empno,ename
20 MANAGER 2975 grouped by deptno,job
20 10875 grouped by deptno
30 CLERK 7900 JAMES 950 grouped by deptno,job,empno,ename
30 CLERK 950 grouped by deptno,job
30 MANAGER 7698 BLAKE 2850 grouped by deptno,job,empno,ename
30 MANAGER 2850 grouped by deptno,job
30 SALESMAN 7499 ALLEN 1600 grouped by deptno,job,empno,ename
30 SALESMAN 7521 WARD 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7654 MARTIN 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7844 TURNER 1500 grouped by deptno,job,empno,ename
30 SALESMAN 5600 grouped by deptno,job
30 9400 grouped by deptno
29025 grouped by ()
27 rijen zijn geselecteerd.
A rollup with N arguments always leads to N+1 grouping sets. So here the rollup has 3 arguments and you can see 4 distinct values in the last gr_text column.
Now let's say we want the grouping sets () and deptno duplicated, leading to 4 extra rows. With rollup you can specify deptno twice:
rwijk@ORA11GR1> select deptno
2 , job
3 , empno
4 , ename
5 , sum(sal) sumsal
6 , case grouping_id(deptno,job,empno)
7 when 0 then 'grouped by deptno,job,empno,ename'
8 when 1 then 'grouped by deptno,job'
9 when 3 then 'grouped by deptno'
10 when 7 then 'grouped by ()'
11 end gr_text
12 from emp
13 group by rollup(deptno,deptno,job,(empno,ename))
14 order by deptno
15 , job
16 , empno
17 /
DEPTNO JOB EMPNO ENAME SUMSAL GR_TEXT
------ --------- ------ ---------- ------ ---------------------------------
10 CLERK 7934 MILLER 1300 grouped by deptno,job,empno,ename
10 CLERK 1300 grouped by deptno,job
10 MANAGER 7782 CLARK 2450 grouped by deptno,job,empno,ename
10 MANAGER 2450 grouped by deptno,job
10 PRESIDENT 7839 KING 5000 grouped by deptno,job,empno,ename
10 PRESIDENT 5000 grouped by deptno,job
10 8750 grouped by deptno
10 8750 grouped by deptno
20 ANALYST 7788 SCOTT 3000 grouped by deptno,job,empno,ename
20 ANALYST 7902 FORD 3000 grouped by deptno,job,empno,ename
20 ANALYST 6000 grouped by deptno,job
20 CLERK 7369 SMITH 800 grouped by deptno,job,empno,ename
20 CLERK 7876 ADAMS 1100 grouped by deptno,job,empno,ename
20 CLERK 1900 grouped by deptno,job
20 MANAGER 7566 JONES 2975 grouped by deptno,job,empno,ename
20 MANAGER 2975 grouped by deptno,job
20 10875 grouped by deptno
20 10875 grouped by deptno
30 CLERK 7900 JAMES 950 grouped by deptno,job,empno,ename
30 CLERK 950 grouped by deptno,job
30 MANAGER 7698 BLAKE 2850 grouped by deptno,job,empno,ename
30 MANAGER 2850 grouped by deptno,job
30 SALESMAN 7499 ALLEN 1600 grouped by deptno,job,empno,ename
30 SALESMAN 7521 WARD 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7654 MARTIN 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7844 TURNER 1500 grouped by deptno,job,empno,ename
30 SALESMAN 5600 grouped by deptno,job
30 9400 grouped by deptno
30 9400 grouped by deptno
29025 grouped by ()
30 rijen zijn geselecteerd.
But there is no way to specify the empty grouping set, as it is implicit with the rollup operator. And so it's also impossible to duplicate this empty grouping set with rollup. It is possible though with the grouping sets notation. First, let's rewrite the original rollup expression to the more tedious but clearer grouping sets notation, like this:
rwijk@ORA11GR1> select deptno
2 , job
3 , empno
4 , ename
5 , sum(sal) sumsal
6 , case grouping_id(deptno,job,empno)
7 when 0 then 'grouped by deptno,job,empno,ename'
8 when 1 then 'grouped by deptno,job'
9 when 3 then 'grouped by deptno'
10 when 7 then 'grouped by ()'
11 end gr_text
12 from emp
13 group by grouping sets
14 ( (deptno,job,empno,ename)
15 , (deptno,job)
16 , deptno
17 , ()
18 )
19 order by deptno
20 , job
21 , empno
22 /
DEPTNO JOB EMPNO ENAME SUMSAL GR_TEXT
------ --------- ------ ---------- ------ ---------------------------------
10 CLERK 7934 MILLER 1300 grouped by deptno,job,empno,ename
10 CLERK 1300 grouped by deptno,job
10 MANAGER 7782 CLARK 2450 grouped by deptno,job,empno,ename
10 MANAGER 2450 grouped by deptno,job
10 PRESIDENT 7839 KING 5000 grouped by deptno,job,empno,ename
10 PRESIDENT 5000 grouped by deptno,job
10 8750 grouped by deptno
20 ANALYST 7788 SCOTT 3000 grouped by deptno,job,empno,ename
20 ANALYST 7902 FORD 3000 grouped by deptno,job,empno,ename
20 ANALYST 6000 grouped by deptno,job
20 CLERK 7369 SMITH 800 grouped by deptno,job,empno,ename
20 CLERK 7876 ADAMS 1100 grouped by deptno,job,empno,ename
20 CLERK 1900 grouped by deptno,job
20 MANAGER 7566 JONES 2975 grouped by deptno,job,empno,ename
20 MANAGER 2975 grouped by deptno,job
20 10875 grouped by deptno
30 CLERK 7900 JAMES 950 grouped by deptno,job,empno,ename
30 CLERK 950 grouped by deptno,job
30 MANAGER 7698 BLAKE 2850 grouped by deptno,job,empno,ename
30 MANAGER 2850 grouped by deptno,job
30 SALESMAN 7499 ALLEN 1600 grouped by deptno,job,empno,ename
30 SALESMAN 7521 WARD 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7654 MARTIN 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7844 TURNER 1500 grouped by deptno,job,empno,ename
30 SALESMAN 5600 grouped by deptno,job
30 9400 grouped by deptno
29025 grouped by ()
27 rijen zijn geselecteerd.
then it becomes very easy to duplicate the deptno and the empty grouping set. Just duplicate them in your grouping sets list of arguments:
rwijk@ORA11GR1> select deptno
2 , job
3 , empno
4 , ename
5 , sum(sal) sumsal
6 , case grouping_id(deptno,job,empno)
7 when 0 then 'grouped by deptno,job,empno,ename'
8 when 1 then 'grouped by deptno,job'
9 when 3 then 'grouped by deptno'
10 when 7 then 'grouped by ()'
11 end gr_text
12 from emp
13 group by grouping sets
14 ( (deptno,job,empno,ename)
15 , (deptno,job)
16 , deptno
17 , deptno
18 , ()
19 , ()
20 )
21 order by deptno
22 , job
23 , empno
24 /
DEPTNO JOB EMPNO ENAME SUMSAL GR_TEXT
------ --------- ------ ---------- ------ ---------------------------------
10 CLERK 7934 MILLER 1300 grouped by deptno,job,empno,ename
10 CLERK 1300 grouped by deptno,job
10 MANAGER 7782 CLARK 2450 grouped by deptno,job,empno,ename
10 MANAGER 2450 grouped by deptno,job
10 PRESIDENT 7839 KING 5000 grouped by deptno,job,empno,ename
10 PRESIDENT 5000 grouped by deptno,job
10 8750 grouped by deptno
10 8750 grouped by deptno
20 ANALYST 7788 SCOTT 3000 grouped by deptno,job,empno,ename
20 ANALYST 7902 FORD 3000 grouped by deptno,job,empno,ename
20 ANALYST 6000 grouped by deptno,job
20 CLERK 7369 SMITH 800 grouped by deptno,job,empno,ename
20 CLERK 7876 ADAMS 1100 grouped by deptno,job,empno,ename
20 CLERK 1900 grouped by deptno,job
20 MANAGER 7566 JONES 2975 grouped by deptno,job,empno,ename
20 MANAGER 2975 grouped by deptno,job
20 10875 grouped by deptno
20 10875 grouped by deptno
30 CLERK 7900 JAMES 950 grouped by deptno,job,empno,ename
30 CLERK 950 grouped by deptno,job
30 MANAGER 7698 BLAKE 2850 grouped by deptno,job,empno,ename
30 MANAGER 2850 grouped by deptno,job
30 SALESMAN 7499 ALLEN 1600 grouped by deptno,job,empno,ename
30 SALESMAN 7521 WARD 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7654 MARTIN 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7844 TURNER 1500 grouped by deptno,job,empno,ename
30 SALESMAN 5600 grouped by deptno,job
30 9400 grouped by deptno
30 9400 grouped by deptno
29025 grouped by ()
29025 grouped by ()
31 rijen zijn geselecteerd.
Now my question was: how can I distinguish between the two equal grouping sets in my select list? Not with GROUPING_ID, because I'd have to mention the columns on which were grouped, not the grouping set. A grouping_id(deptno) would yield to 1 in both deptno grouping sets.
Using the row_number analytic function and including all columns present in a grouping set in the partition by clause, was what I thought of next. This would accomplish a "1" for all singular grouping sets, and an arbitrary 1 and 2 within the duplicate grouping sets, like this:
rwijk@ORA11GR1> select deptno
2 , job
3 , empno
4 , ename
5 , sum(sal) sumsal
6 , case grouping_id(deptno,job,empno)
7 when 0 then 'grouped by deptno,job,empno,ename'
8 when 1 then 'grouped by deptno,job'
9 when 3 then 'grouped by deptno, grouping set ' ||
10 to_char(2+row_number() over
(partition by deptno,job,empno order by null))
11 when 7 then 'grouped by (), grouping set ' ||
12 to_char(4+row_number() over
(partition by deptno,job,empno order by null))
13 end gr_text
14 from emp
15 group by grouping sets
16 ( (deptno,job,empno,ename)
17 , (deptno,job)
18 , deptno
19 , deptno
20 , ()
21 , ()
22 )
23 order by deptno
24 , job
25 , empno
26 /
DEPTNO JOB EMPNO ENAME SUMSAL GR_TEXT
------ --------- ------ ---------- ------ ---------------------------------
10 CLERK 7934 MILLER 1300 grouped by deptno,job,empno,ename
10 CLERK 1300 grouped by deptno,job
10 MANAGER 7782 CLARK 2450 grouped by deptno,job,empno,ename
10 MANAGER 2450 grouped by deptno,job
10 PRESIDENT 7839 KING 5000 grouped by deptno,job,empno,ename
10 PRESIDENT 5000 grouped by deptno,job
10 8750 grouped by deptno, grouping set 3
10 8750 grouped by deptno, grouping set 4
20 ANALYST 7788 SCOTT 3000 grouped by deptno,job,empno,ename
20 ANALYST 7902 FORD 3000 grouped by deptno,job,empno,ename
20 ANALYST 6000 grouped by deptno,job
20 CLERK 7369 SMITH 800 grouped by deptno,job,empno,ename
20 CLERK 7876 ADAMS 1100 grouped by deptno,job,empno,ename
20 CLERK 1900 grouped by deptno,job
20 MANAGER 7566 JONES 2975 grouped by deptno,job,empno,ename
20 MANAGER 2975 grouped by deptno,job
20 10875 grouped by deptno, grouping set 3
20 10875 grouped by deptno, grouping set 4
30 CLERK 7900 JAMES 950 grouped by deptno,job,empno,ename
30 CLERK 950 grouped by deptno,job
30 MANAGER 7698 BLAKE 2850 grouped by deptno,job,empno,ename
30 MANAGER 2850 grouped by deptno,job
30 SALESMAN 7499 ALLEN 1600 grouped by deptno,job,empno,ename
30 SALESMAN 7521 WARD 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7654 MARTIN 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7844 TURNER 1500 grouped by deptno,job,empno,ename
30 SALESMAN 5600 grouped by deptno,job
30 9400 grouped by deptno, grouping set 3
30 9400 grouped by deptno, grouping set 4
29025 grouped by (), grouping set 5
29025 grouped by (), grouping set 6
31 rijen zijn geselecteerd.
The problem is the ordering. There is nothing to order by, so I chose NULL. Any other constant value would have sufficed as well. But this ordering is arbitrary, meaning that two rows can both be 1 and can both be 2. And some implementation detail of Oracle has to decide which one it's going to be. And if I use the row_number function twice, would both expressions give the same results? It depends on the same implementation, so yes, both expressions give the same results:
rwijk@ORA11GR1> select case grouping_id(deptno,job,empno)
2 when 0 then 'grouped by deptno,job,empno,ename'
3 when 1 then 'grouped by deptno,job'
4 when 3 then 'grouped by deptno, grouping set ' ||
5 to_char(2+row_number() over
(partition by deptno,job,empno order by null))
6 when 7 then 'grouped by (), grouping set ' ||
7 to_char(4+row_number() over
(partition by deptno,job,empno order by null))
8 end gr_text
9 , case grouping_id(deptno,job,empno)
10 when 0 then 'grouped by deptno,job,empno,ename'
11 when 1 then 'grouped by deptno,job'
12 when 3 then 'grouped by deptno, grouping set ' ||
13 to_char(2+row_number() over
(partition by deptno,job,empno order by null))
14 when 7 then 'grouped by (), grouping set ' ||
15 to_char(4+row_number() over
(partition by deptno,job,empno order by null))
16 end gr_text2
17 from emp
18 group by grouping sets
19 ( (deptno,job,empno,ename)
20 , (deptno,job)
21 , deptno
22 , deptno
23 , ()
24 , ()
25 )
26 order by deptno
27 , job
28 , empno
29 /
GR_TEXT GR_TEXT2
--------------------------------- ---------------------------------
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno, grouping set 3 grouped by deptno, grouping set 3
grouped by deptno, grouping set 4 grouped by deptno, grouping set 4
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno, grouping set 3 grouped by deptno, grouping set 3
grouped by deptno, grouping set 4 grouped by deptno, grouping set 4
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job,empno,ename grouped by deptno,job,empno,ename
grouped by deptno,job grouped by deptno,job
grouped by deptno, grouping set 3 grouped by deptno, grouping set 3
grouped by deptno, grouping set 4 grouped by deptno, grouping set 4
grouped by (), grouping set 5 grouped by (), grouping set 5
grouped by (), grouping set 6 grouped by (), grouping set 6
31 rijen zijn geselecteerd.
But still I felt uncomfortable with this solution. I was pressing the Previous and Next buttons in the SQL Reference Manual to find any related functions and saw the GROUP_ID function. "This has got to be the most useless function ever" must have been my thoughts when I read about this function years ago. In fact, I had completely forgotten about it. But it's entire reason for being is a case like this with duplicate grouping sets, to be able to distinguish between the sets:
rwijk@ORA11GR1> select deptno
2 , job
3 , empno
4 , ename
5 , sum(sal) sumsal
6 , case grouping_id(deptno,job,empno)
7 when 0 then 'grouped by deptno,job,empno,ename'
8 when 1 then 'grouped by deptno,job'
9 when 3 then 'grouped by deptno'
10 when 7 then 'grouped by ()'
11 end gr_text
12 , group_id() grid
13 from emp
14 group by grouping sets
15 ( (deptno,job,empno,ename)
16 , (deptno,job)
17 , deptno
18 , deptno
19 , ()
20 , ()
21 , ()
22 , ()
23 , ()
24 )
25 order by deptno
26 , job
27 , empno
28 /
DEPTNO JOB EMPNO ENAME SUMSAL GR_TEXT GRID
------ --------- ------ ---------- ------ --------------------------------- ----
10 CLERK 7934 MILLER 1300 grouped by deptno,job,empno,ename 0
10 CLERK 1300 grouped by deptno,job 0
10 MANAGER 7782 CLARK 2450 grouped by deptno,job,empno,ename 0
10 MANAGER 2450 grouped by deptno,job 0
10 PRESIDENT 7839 KING 5000 grouped by deptno,job,empno,ename 0
10 PRESIDENT 5000 grouped by deptno,job 0
10 8750 grouped by deptno 1
10 8750 grouped by deptno 0
20 ANALYST 7788 SCOTT 3000 grouped by deptno,job,empno,ename 0
20 ANALYST 7902 FORD 3000 grouped by deptno,job,empno,ename 0
20 ANALYST 6000 grouped by deptno,job 0
20 CLERK 7369 SMITH 800 grouped by deptno,job,empno,ename 0
20 CLERK 7876 ADAMS 1100 grouped by deptno,job,empno,ename 0
20 CLERK 1900 grouped by deptno,job 0
20 MANAGER 7566 JONES 2975 grouped by deptno,job,empno,ename 0
20 MANAGER 2975 grouped by deptno,job 0
20 10875 grouped by deptno 1
20 10875 grouped by deptno 0
30 CLERK 7900 JAMES 950 grouped by deptno,job,empno,ename 0
30 CLERK 950 grouped by deptno,job 0
30 MANAGER 7698 BLAKE 2850 grouped by deptno,job,empno,ename 0
30 MANAGER 2850 grouped by deptno,job 0
30 SALESMAN 7499 ALLEN 1600 grouped by deptno,job,empno,ename 0
30 SALESMAN 7521 WARD 1250 grouped by deptno,job,empno,ename 0
30 SALESMAN 7654 MARTIN 1250 grouped by deptno,job,empno,ename 0
30 SALESMAN 7844 TURNER 1500 grouped by deptno,job,empno,ename 0
30 SALESMAN 5600 grouped by deptno,job 0
30 9400 grouped by deptno 0
30 9400 grouped by deptno 1
29025 grouped by () 2
29025 grouped by () 3
29025 grouped by () 4
29025 grouped by () 0
29025 grouped by () 1
34 rijen zijn geselecteerd.
Here I have duplicated the empty grouping set 4 times and you see the outcome of the GROUP_ID() function for those five sets ranging from 0 to 4. This is exactly what is needed here, so now the initial query is quite easy, like this:
rwijk@ORA11GR1> select deptno
2 , job
3 , empno
4 , ename
5 , sum(sal) sumsal
6 , case grouping_id(deptno,job,empno)
7 when 0 then 'grouped by deptno,job,empno,ename'
8 when 1 then 'grouped by deptno,job'
9 when 3 then 'grouped by deptno, grouping set ' || to_char(3+group_id())
10 when 7 then 'grouped by (), grouping set ' || to_char(5+group_id())
11 end gr_text
12 from emp
13 group by grouping sets
14 ( (deptno,job,empno,ename)
15 , (deptno,job)
16 , deptno
17 , deptno
18 , ()
19 , ()
20 )
21 order by deptno
22 , job
23 , empno
24 /
DEPTNO JOB EMPNO ENAME SUMSAL GR_TEXT
------ --------- ------ ---------- ------ ---------------------------------
10 CLERK 7934 MILLER 1300 grouped by deptno,job,empno,ename
10 CLERK 1300 grouped by deptno,job
10 MANAGER 7782 CLARK 2450 grouped by deptno,job,empno,ename
10 MANAGER 2450 grouped by deptno,job
10 PRESIDENT 7839 KING 5000 grouped by deptno,job,empno,ename
10 PRESIDENT 5000 grouped by deptno,job
10 8750 grouped by deptno, grouping set 4
10 8750 grouped by deptno, grouping set 3
20 ANALYST 7788 SCOTT 3000 grouped by deptno,job,empno,ename
20 ANALYST 7902 FORD 3000 grouped by deptno,job,empno,ename
20 ANALYST 6000 grouped by deptno,job
20 CLERK 7369 SMITH 800 grouped by deptno,job,empno,ename
20 CLERK 7876 ADAMS 1100 grouped by deptno,job,empno,ename
20 CLERK 1900 grouped by deptno,job
20 MANAGER 7566 JONES 2975 grouped by deptno,job,empno,ename
20 MANAGER 2975 grouped by deptno,job
20 10875 grouped by deptno, grouping set 3
20 10875 grouped by deptno, grouping set 4
30 CLERK 7900 JAMES 950 grouped by deptno,job,empno,ename
30 CLERK 950 grouped by deptno,job
30 MANAGER 7698 BLAKE 2850 grouped by deptno,job,empno,ename
30 MANAGER 2850 grouped by deptno,job
30 SALESMAN 7499 ALLEN 1600 grouped by deptno,job,empno,ename
30 SALESMAN 7521 WARD 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7654 MARTIN 1250 grouped by deptno,job,empno,ename
30 SALESMAN 7844 TURNER 1500 grouped by deptno,job,empno,ename
30 SALESMAN 5600 grouped by deptno,job
30 9400 grouped by deptno, grouping set 3
30 9400 grouped by deptno, grouping set 4
29025 grouped by (), grouping set 5
29025 grouped by (), grouping set 6
31 rijen zijn geselecteerd.
Sunday, November 23, 2008
executing_packages.sql
In our in-house application we are developing new features and fixing bugs with approximately 40 developers in total. Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package. When there's no time pressure, the developer just postpones the installation to the end of the day, or early next morning. But every now and then, a high priority bugfix or a project nearing its deadline cannot wait this long. The developer calls up the DBA and asks to bounce the developer database. The DBA sends an e-mail to warn everybody and kills all developers' sessions in the process of course. And some five or ten minutes later the DBA sends an e-mail to inform that everything is back to normal. This ritual is causing not only annoyance to 40 developers and a DBA, it also costs money: 41 times 10 minutes times X euros/hour.
Two or three years ago, I was wondering whether it is visible in the data dictionary who is executing the package that needed a new version. I stumbled upon this priceless script by Steve Adams, called executing_packages.sql. It lists the sessions that are currently executing stored code. He had found out that an object that is currently executing, has the sys.x$kglob.kglhdpmd column set to 2. See for example this question and answer. From then on, whenever I received an e-mail announcing the bounce of the developer database, I quickly turned to the developer experiencing the problem asking him the name of the package. I ran executing_packages.sql which revealed who is executing this package. Next, I called the DBA to stop the bounce and just kill one session instead. And I could hand over the sid/serial# of the session to be killed. Or even better, we just asked the responsible developer to end his session manually if possible.
But then we did an upgrade to 10.2.0.4. The script was originally developed for 8.0 and 8.1 according to Steve Adams' site, and I noticed more than once that it worked perfectly in 9.2.0.7. On 10.2.0.4 however, there are two problems:
I was surprised to see very little information about this when googling. I had imagined that such a useful script would be used anywhere and that several people would have bumped into the same problems by now. So this gives me the opportunity to find these things out myself at home.
It would be nice to address the first problem by defining a view under the SYS-schema and giving access to the view to all developers, and not just the DBA-account. The only thing that might worry the DBA's, is that the view might be used in the application. This can be addressed by granting access to the view to a role that every developer has. You cannot base new database objects on objects that have not been granted directly to you, so this will appease the DBA's.
The second problem was easy. Just remove the underscores in the table names and the query works: sys.x$kglob, sys.x$kglpn and sys.x$ksuse are still there in 10 and 11. I just want to extend the session information a little more. The existing script only shows the sid and serial#. In the new query, I added the username, program, module, action and client_info as well, as this additional information will help me in my conversation with the developer who has to end his session. I could have used the v$session view for this, but in the same style as the original script, I used sys.x$ksusex to get the "dbms_application_info" fields module, action and client_info. This results in this view:
Normally I won't include an order by clause in a view, but in this case I only want to do a "select * from sys.v_executing_packages", and not bother about the ordering there.
For now I'll just grant the select privilege directly to my account. As said, granting to a developer role is better in real life.
Below is a small test to see how this view can be used. In session 1 I'm creating a package that executes a long time:
And now session 1 is asleep for an hour.
In session 2 I'm trying to create a second version of the package, that will wait for session 1 to complete. This will eventually lead to a ORA-04021, after some 15 minutes:
Now I will use the view in a third session (or in session 2 after the time-out) to find out who is executing which package:
And this points to the SQL*Plus session 1.
Two or three years ago, I was wondering whether it is visible in the data dictionary who is executing the package that needed a new version. I stumbled upon this priceless script by Steve Adams, called executing_packages.sql. It lists the sessions that are currently executing stored code. He had found out that an object that is currently executing, has the sys.x$kglob.kglhdpmd column set to 2. See for example this question and answer. From then on, whenever I received an e-mail announcing the bounce of the developer database, I quickly turned to the developer experiencing the problem asking him the name of the package. I ran executing_packages.sql which revealed who is executing this package. Next, I called the DBA to stop the bounce and just kill one session instead. And I could hand over the sid/serial# of the session to be killed. Or even better, we just asked the responsible developer to end his session manually if possible.
But then we did an upgrade to 10.2.0.4. The script was originally developed for 8.0 and 8.1 according to Steve Adams' site, and I noticed more than once that it worked perfectly in 9.2.0.7. On 10.2.0.4 however, there are two problems:
- I used a special DBA-account to be able to execute the script. The same upgraded account is now unable to see the sys.x$ tables.
- Even when logged in as SYS, the sys.x_$ tables (note the underscore) are not there anymore.
I was surprised to see very little information about this when googling. I had imagined that such a useful script would be used anywhere and that several people would have bumped into the same problems by now. So this gives me the opportunity to find these things out myself at home.
It would be nice to address the first problem by defining a view under the SYS-schema and giving access to the view to all developers, and not just the DBA-account. The only thing that might worry the DBA's, is that the view might be used in the application. This can be addressed by granting access to the view to a role that every developer has. You cannot base new database objects on objects that have not been granted directly to you, so this will appease the DBA's.
The second problem was easy. Just remove the underscores in the table names and the query works: sys.x$kglob, sys.x$kglpn and sys.x$ksuse are still there in 10 and 11. I just want to extend the session information a little more. The existing script only shows the sid and serial#. In the new query, I added the username, program, module, action and client_info as well, as this additional information will help me in my conversation with the developer who has to end his session. I could have used the v$session view for this, but in the same style as the original script, I used sys.x$ksusex to get the "dbms_application_info" fields module, action and client_info. This results in this view:
sys@ORA11GR1> create view v_executing_packages
2 as
3 select
4 decode(o.kglobtyp,
5 7, 'PROCEDURE',
6 8, 'FUNCTION',
7 9, 'PACKAGE',
8 12, 'TRIGGER',
9 13, 'CLASS'
10 ) "TYPE",
11 o.kglnaown "OWNER",
12 o.kglnaobj "NAME",
13 s.indx "SID",
14 s.ksuseser "SERIAL",
15 s.ksuudnam "USERNAME",
16 s.ksuseapp "PROGRAM",
17 x.app "MODULE",
18 x.act "ACTION",
19 x.clinfo "CLIENT_INFO"
20 from
21 sys.x$kglob o,
22 sys.x$kglpn p,
23 sys.x$ksuse s,
24 sys.x$ksusex x
25 where
26 o.inst_id = userenv('Instance') and
27 p.inst_id = userenv('Instance') and
28 s.inst_id = userenv('Instance') and
29 x.inst_id = userenv('Instance') and
30 p.kglpnhdl = o.kglhdadr and
31 s.addr = p.kglpnses and
32 s.indx = x.sid and
33 s.ksuseser = x.serial and
34 o.kglhdpmd = 2 and
35 o.kglobtyp in (7, 8, 9, 12, 13)
36 order by 1,2,3
37 /
View is aangemaakt.
Normally I won't include an order by clause in a view, but in this case I only want to do a "select * from sys.v_executing_packages", and not bother about the ordering there.
For now I'll just grant the select privilege directly to my account. As said, granting to a developer role is better in real life.
sys@ORA11GR1> grant select on v_executing_packages to rwijk
2 /
Toekennen is geslaagd.
Below is a small test to see how this view can be used. In session 1 I'm creating a package that executes a long time:
rwijk@ORA11GR1> select sid
2 , serial#
3 from v$session
4 where sid in (select sid from v$mystat)
5 /
SID SERIAL#
---------- ----------
136 35
1 rij is geselecteerd.
rwijk@ORA11GR1> create package mypck
2 as
3 procedure test;
4 end mypck;
5 /
Package is aangemaakt.
rwijk@ORA11GR1> create package body mypck
2 as
3 procedure test
4 is
5 begin
6 dbms_lock.sleep(3600); -- an hour
7 dbms_output.put_line('version 1.0');
8 end test;
9 end mypck;
10 /
Package-body is aangemaakt.
rwijk@ORA11GR1> exec mypck.test
And now session 1 is asleep for an hour.
In session 2 I'm trying to create a second version of the package, that will wait for session 1 to complete. This will eventually lead to a ORA-04021, after some 15 minutes:
rwijk@ORA11GR1> select sid
2 , serial#
3 from v$session
4 where sid in (select sid from v$mystat)
5 /
SID SERIAL#
---------- ----------
139 37
1 rij is geselecteerd.
rwijk@ORA11GR1> create or replace package body mypck
2 as
3 procedure test
4 as
5 begin
6 dbms_lock.sleep(3600); -- an hour
7 dbms_output.put_line('version 1.1');
8 end test;
9 end mypck;
10 /
create or replace package body mypck
*
FOUT in regel 1:
.ORA-04021: Time-out tijdens wachten op vergrendeling van object .
Now I will use the view in a third session (or in session 2 after the time-out) to find out who is executing which package:
rwijk@ORA11GR1> select * from sys.v_executing_packages
2 /
TYPE OWNER NAME SID SERIAL
--------- ----- ------------------------------ ---------- ----------
USERNAME PROGRAM
------------------------------ ------------------------------------------------
MODULE
------------------------------------------------
ACTION
--------------------------------
CLIENT_INFO
----------------------------------------------------------------
PACKAGE RWIJK MYPCK 136 35
RWIJK SQL*Plus
SQL*Plus
PACKAGE SYS DBMS_LOCK 136 35
RWIJK SQL*Plus
SQL*Plus
2 rijen zijn geselecteerd.
And this points to the SQL*Plus session 1.
Sunday, November 16, 2008
RETURNING INTO
While doing a quality check on new code, I encountered some row by row processing. This always triggers me to make a remark about rewriting the code to a single SQL, or at least bulk processing. But in this case, inside the loop, several local variables were used to do some counting. And at the end of the loop, a processing report was created to inform the end user about how many records had been processed, split out for several statusses. When rewriting it to a single SQL, you can use the SQL%ROWCOUNT to determine how many rows were processed, but if you want to split this number up per status, how to do that?
Here is an example, which doesn't look like the original code in any way, but it shows the same principle. First, let's create a table with some random statusses and create a procedure that creates a processing report in the dbms_output buffer:
And here is the code doing row by row processing:
When rewriting it to a single SQL statement, it is of course possible to execute another statement to retrieve the numbers per status, but that's an extra query, and a query that's executed at a different time, so you'd have to switch to a serializable isolation level, or use flashback technology to be accurate.
Or you can use aggregate functions in your returning into clause. When reading about the RETURNING INTO clause in the documentation, it talks about a "single_row_expression" being "An expression that returns a single row of a table." So it is not immediately obvious that an aggregate can be used. But it is allowed:
Needless to say, this is the fastest solution.
The funny thing though is that this exact same code doesn't work in Oracle10 (tested on 10.2.0.1 and 10.2.0.4):
And I don't know why the above fails to produce the right numbers, but it does work on Oracle10g when using the sum aggregate function:
So yet another excuse less to write cursor for loops with DML inside.
***
Two other random nice-things-to-know about the returning into clause:
1) You can use an object type to "return into":
2) The returning into clause doesn't work together with the "where current of" clause:
So you'll have to mimic the current of clause by selecting and using the rowid:
Here is an example, which doesn't look like the original code in any way, but it shows the same principle. First, let's create a table with some random statusses and create a procedure that creates a processing report in the dbms_output buffer:
rwijk@ORA11GR1> create table t (id,col,status)
2 as
3 select level
4 , '**********'
5 , cast(dbms_random.string('u',1) as varchar2(1))
6 from dual
7 connect by level <= 1000
8 /
Table created.
rwijk@ORA11GR1> select status
2 , count(*)
3 from t
4 where status in ('A','B','C','D','E')
5 group by status
6 order by status
7 /
S COUNT(*)
- ----------
A 38
B 42
C 37
D 39
E 29
5 rows selected.
rwijk@ORA11GR1> create procedure print_processing_report
2 ( p_count_a in number
3 , p_count_b in number
4 , p_count_c in number
5 , p_count_d in number
6 , p_count_e in number
7 )
8 is
9 procedure print_one_line
10 ( p_status in t.status%type
11 , p_count in number
12 )
13 is
14 begin
15 dbms_output.put_line
16 ( '* Total number of ' || p_status || '''s processed: ' ||
17 rpad(to_char(p_count),4) || '*'
18 );
19 end print_one_line
20 ;
21 begin
22 dbms_output.put_line('**************************************');
23 dbms_output.put_line('* P R O C E S S I N G R E P O R T *');
24 dbms_output.put_line('* ================================== *');
25 print_one_line('A',p_count_a);
26 print_one_line('B',p_count_b);
27 print_one_line('C',p_count_c);
28 print_one_line('D',p_count_d);
29 print_one_line('E',p_count_e);
30 dbms_output.put_line('**************************************');
31 end;
32 /
Procedure created.
And here is the code doing row by row processing:
rwijk@ORA11GR1> declare
2 cursor c_t
3 is
4 select t.status
5 from t
6 where t.status in ('A','B','C','D','E')
7 for update of t.status
8 ;
9 a pls_integer := 0;
10 b pls_integer := 0;
11 c pls_integer := 0;
12 d pls_integer := 0;
13 e pls_integer := 0;
14 begin
15 for r in c_t
16 loop
17 update t
18 set col = 'z'
19 where current of c_t
20 ;
21 case r.status
22 when 'A' then a := a + 1;
23 when 'B' then b := b + 1;
24 when 'C' then c := c + 1;
25 when 'D' then d := d + 1;
26 when 'E' then e := e + 1;
27 end case
28 ;
29 end loop
30 ;
31 print_processing_report(a,b,c,d,e);
32 end;
33 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 38 *
* Total number of B's processed: 42 *
* Total number of C's processed: 37 *
* Total number of D's processed: 39 *
* Total number of E's processed: 29 *
**************************************
PL/SQL procedure successfully completed.
When rewriting it to a single SQL statement, it is of course possible to execute another statement to retrieve the numbers per status, but that's an extra query, and a query that's executed at a different time, so you'd have to switch to a serializable isolation level, or use flashback technology to be accurate.
Or you can use aggregate functions in your returning into clause. When reading about the RETURNING INTO clause in the documentation, it talks about a "single_row_expression" being "An expression that returns a single row of a table." So it is not immediately obvious that an aggregate can be used. But it is allowed:
rwijk@ORA11GR1> rollback
2 /
Rollback complete.
rwijk@ORA11GR1> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 count(decode(status,'A',1))
13 , count(decode(status,'B',1))
14 , count(decode(status,'C',1))
15 , count(decode(status,'D',1))
16 , count(decode(status,'E',1))
17 into
18 a
19 , b
20 , c
21 , d
22 , e
23 ;
24 print_processing_report(a,b,c,d,e);
25 end;
26 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 38 *
* Total number of B's processed: 42 *
* Total number of C's processed: 37 *
* Total number of D's processed: 39 *
* Total number of E's processed: 29 *
**************************************
PL/SQL procedure successfully completed.
Needless to say, this is the fastest solution.
The funny thing though is that this exact same code doesn't work in Oracle10 (tested on 10.2.0.1 and 10.2.0.4):
rwijk@ORA10GR2> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 count(decode(status,'A',1))
13 , count(decode(status,'B',1))
14 , count(decode(status,'C',1))
15 , count(decode(status,'D',1))
16 , count(decode(status,'E',1))
17 into
18 a
19 , b
20 , c
21 , d
22 , e
23 ;
24 print_processing_report(a,b,c,d,e);
25 end;
26 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 190 *
* Total number of B's processed: 190 *
* Total number of C's processed: 190 *
* Total number of D's processed: 190 *
* Total number of E's processed: 190 *
**************************************
PL/SQL-procedure is geslaagd.
And I don't know why the above fails to produce the right numbers, but it does work on Oracle10g when using the sum aggregate function:
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
rwijk@ORA10GR2> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 sum(case status when 'A' then 1 else 0 end)
13 , sum(case status when 'B' then 1 else 0 end)
14 , sum(case status when 'C' then 1 else 0 end)
15 , sum(case status when 'D' then 1 else 0 end)
16 , sum(case status when 'E' then 1 else 0 end)
17 into a, b, c, d, e
18 ;
19 print_processing_report(a,b,c,d,e);
20 end;
21 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 36 *
* Total number of B's processed: 35 *
* Total number of C's processed: 41 *
* Total number of D's processed: 43 *
* Total number of E's processed: 35 *
**************************************
PL/SQL-procedure is geslaagd.
So yet another excuse less to write cursor for loops with DML inside.
***
Two other random nice-things-to-know about the returning into clause:
1) You can use an object type to "return into":
rwijk@ORA11GR1> create type my_object is object
2 ( col1 number
3 , col2 varchar2(1)
4 );
5 /
Type created.
rwijk@ORA11GR1> declare
2 o my_object;
3 begin
4 update t
5 set col = 'z'
6 where id = 123
7 returning my_object(t.id,t.status)
8 into o
9 ;
10 dbms_output.put_line(o.col1);
11 dbms_output.put_line(o.col2);
12 end;
13 /
123
T
PL/SQL procedure successfully completed.
2) The returning into clause doesn't work together with the "where current of" clause:
rwijk@ORA11GR1> declare
2 cursor c is select ename from emp for update of sal;
3 l_ename emp.ename%type;
4 begin
5 for r in c
6 loop
7 update emp
8 set sal = sal * 1.1
9 where current of c
10 returning ename into l_ename
11 ;
12 dbms_output.put_line(l_ename);
13 end loop;
14 end;
15 /
returning ename into l_ename
*
ERROR at line 10:
ORA-06550: line 10, column 12:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored
So you'll have to mimic the current of clause by selecting and using the rowid:
rwijk@ORA11GR1> declare
2 cursor c is select rowid, ename from emp for update of sal;
3 l_ename emp.ename%type;
4 begin
5 for r in c
6 loop
7 update emp
8 set sal = sal * 1.1
9 where rowid = r.rowid
10 returning ename into l_ename
11 ;
12 dbms_output.put_line(l_ename);
13 end loop;
14 end;
15 /
SMITH
ALLEN
WARD
JONES
MARTIN
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed.
Saturday, November 15, 2008
Journaling using flashback data archives - redux
Last year, with version 11.1.0.6, I investigated how useful the new flashback data archive was for implementing journaling on a table in this post. There were two main issues:
1) The documented restriction "DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for the table. If you try to retrieve data from a time before such a DDL executed, you will get error ORA-1466." Most of these actions result in a "ORA-55610: Invalid DDL statement on history-tracked table", so existing undo is not invalidated.
2) There was one unfortunate exception to the above: the addition of constraints. They are allowed in 11.1.0.6 and invalidate the undo without a warning.
In the previous post I wrote:
And that's exactly what has been fixed in 11.1.0.7: adding constraints on a history-tracked table now leads to an ORA-55610. Using the same script, slightly edited for cosmetic reasons only, this is the new section where constraints are being added:
So with this scary bug out of the way, journaling using flashback data archives becomes an even more serious option. The question that remains, is how to handle the first issue. What exactly should be done when you want some maintenance on a history-tracked table? The next part will try to provide an answer.
First let's create a tablespace and two tables and apply journaling using flashback data archive to the main table:
And add auditing to provide usernames of all actions. Auditing information is stored in the sys.aud$ table which resides in the system tablespace by default. If you want to use it for real, you'll probably want to move it to another tablespace. The dbms_audit_mgmt package can do that for you.
And create a view again to be able to view the journaling information easily:
Now do some DML against table T and watch the information in view v:
So far the same ideas as described in the previous post. Next let's try to add a new status column with two possible values, enforced with a check constraint:
As said in the beginning of this post, this generates an ORA-55610. This is the true idea behind the flashback data archives: it is tamper proof. But for journaling you need some more flexibility. So the idea is to introduce a fixed journaling table containing all the information currently present in view v, and setup a renewed table with a flashback data archive. The view v will "union all" both pieces together. Notice that this idea implies downtime for your table and/or application.
Here I simply renamed table t2 to t, but in real life you might want to rename (drop/add) some indexes and constraints as well. Next, create a new view v:
This is not exactly what we want: the new rows from v without journaling information should be filtered, as they are present in the t_jn table as well. So add a "tv.versions_startscn is not null" clause:
And now we can continue doing DML and having journaling:
Note also that the new t_jn table will not automatically lose its information after the originally specified retention period. If that's what you want to happen, you'll have to schedule a job to purge the old data.
1) The documented restriction "DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for the table. If you try to retrieve data from a time before such a DDL executed, you will get error ORA-1466." Most of these actions result in a "ORA-55610: Invalid DDL statement on history-tracked table", so existing undo is not invalidated.
2) There was one unfortunate exception to the above: the addition of constraints. They are allowed in 11.1.0.6 and invalidate the undo without a warning.
In the previous post I wrote:
So if you make the mistake of adding a constraint, you lose all journaling information... This sure is something to be aware of. I think the adding of constraints to a table with a flashback archive should also raise an ORA-55610. Hopefully Oracle will fix this in a future release or patch set.
And that's exactly what has been fixed in 11.1.0.7: adding constraints on a history-tracked table now leads to an ORA-55610. Using the same script, slightly edited for cosmetic reasons only, this is the new section where constraints are being added:
rwijk@ORA11GR1> alter table mijn_emp
2 add constraint my_pk primary key (empno)
3 /
alter table mijn_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
rwijk@ORA11GR1> alter table mijn_emp
2 add constraint my_uk1 unique (ename)
3 /
alter table mijn_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
rwijk@ORA11GR1> alter table mijn_emp
2 add constraint my_fk1 foreign key (deptno) references dept(deptno)
3 /
alter table mijn_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
rwijk@ORA11GR1> alter table mijn_emp
2 add constraint ck1 check (sal>500)
3 /
alter table mijn_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
rwijk@ORA11GR1> select jn_operation
2 , jn_user
3 , to_char(jn_datetime,'dd-mm-yy hh24:mi:ss')
4 , jn_scn
5 , empno
6 , ename
7 , sal
8 , deptno
9 from mijn_emp_jn_v
10 order by empno
11 , jn_scn
12 /
J JN_USER TO_CHAR(JN_DATETI JN_SCN EMPNO ENAME SAL DEPTNO
- ------------- ----------------- ---------- ----- ---------- ----- ----------
I 19-10-08 22:40:16 3175631 7369 SMITH 800 20
U 19-10-08 22:40:19 3175634 7369 SMITH 880 20
U 19-10-08 22:40:19 3175638 7369 Smith 880 20
I 19-10-08 22:40:16 3175631 7499 ALLEN 1600 30
I 19-10-08 22:40:16 3175631 7521 WARD 1250 30
I 19-10-08 22:40:16 3175631 7566 JONES 2975 20
U 19-10-08 22:40:19 3175634 7566 JONES 3273 20
U 19-10-08 22:40:19 3175638 7566 Jones 3273 20
I 19-10-08 22:40:16 3175631 7654 MARTIN 1250 30
I 19-10-08 22:40:16 3175631 7698 BLAKE 2850 30
I 19-10-08 22:40:19 3175644 7777 VAN WIJK 1600 40
D IEMAND_ANDERS 19-10-08 22:40:52 3175705 7777 VAN WIJK 1600 40
I RWIJK 19-10-08 22:40:31 3175667 7778 ROB 1700 40
U RWIJK 19-10-08 22:40:58 3175725 7778 ROB 1800 40
I 19-10-08 22:40:16 3175631 7782 CLARK 2450 10
U 19-10-08 22:40:19 3175638 7782 Clark 2450 10
I 19-10-08 22:40:16 3175631 7788 SCOTT 3000 20
U 19-10-08 22:40:19 3175634 7788 SCOTT 3300 20
U 19-10-08 22:40:19 3175638 7788 Scott 3300 20
D 19-10-08 22:40:19 3175644 7788 Scott 3300 20
I 19-10-08 22:40:16 3175631 7839 KING 5000 10
U 19-10-08 22:40:19 3175638 7839 King 5000 10
I 19-10-08 22:40:16 3175631 7844 TURNER 1500 30
I 19-10-08 22:40:16 3175631 7876 ADAMS 1100 20
U 19-10-08 22:40:19 3175634 7876 ADAMS 1210 20
U 19-10-08 22:40:19 3175638 7876 Adams 1210 20
I 19-10-08 22:40:16 3175631 7900 JAMES 950 30
I 19-10-08 22:40:16 3175631 7902 FORD 3000 20
U 19-10-08 22:40:19 3175634 7902 FORD 3300 20
U 19-10-08 22:40:19 3175638 7902 Ford 3300 20
D 19-10-08 22:40:19 3175644 7902 Ford 3300 20
I 19-10-08 22:40:16 3175631 7934 MILLER 1300 10
U 19-10-08 22:40:19 3175638 7934 Miller 1300 10
33 rows selected.
So with this scary bug out of the way, journaling using flashback data archives becomes an even more serious option. The question that remains, is how to handle the first issue. What exactly should be done when you want some maintenance on a history-tracked table? The next part will try to provide an answer.
First let's create a tablespace and two tables and apply journaling using flashback data archive to the main table:
rwijk@ORA11GR1> create tablespace my_tablespace datafile 'extra_file.dat' size 10M
2 /
Tablespace created.
rwijk@ORA11GR1> create flashback archive flashback_archive_10_years
2 tablespace my_tablespace
3 retention 10 year
4 /
Flashback archive created.
rwijk@ORA11GR1> create table fktable (col number(10) primary key)
2 /
Table created.
rwijk@ORA11GR1> create table t
2 ( pkcol number(10) primary key
3 , fkcol number(10) not null references fktable(col)
4 , description varchar2(11)
5 )
6 flashback archive flashback_archive_10_years
7 /
Table created.
rwijk@ORA11GR1> exec dbms_lock.sleep(15)
rwijk@ORA11GR1> insert into fktable (col) values (1)
2 /
1 row created.
And add auditing to provide usernames of all actions. Auditing information is stored in the sys.aud$ table which resides in the system tablespace by default. If you want to use it for real, you'll probably want to move it to another tablespace. The dbms_audit_mgmt package can do that for you.
rwijk@ORA11GR1> audit insert,update,delete on t by access
2 /
Audit succeeded.
rwijk@ORA11GR1> begin
2 sys.dbms_audit_mgmt.set_audit_trail_location
3 ( audit_trail_type => sys.dbms_audit_mgmt.audit_trail_aud_std
4 , audit_trail_location_value => 'my_tablespace'
5 );
6 end;
7 /
PL/SQL procedure successfully completed.
rwijk@ORA11GR1> select tablespace_name
2 from all_tables
3 where owner = 'SYS'
4 and table_name = 'AUD$'
5 /
TABLESPACE_NAME
------------------------------
MY_TABLESPACE
1 row selected.
And create a view again to be able to view the journaling information easily:
rwijk@ORA11GR1> create view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 from t versions between scn minvalue and maxvalue tv
11 , user_audit_object ao
12 where tv.versions_xid = ao.transactionid (+)
13 /
View created.
Now do some DML against table T and watch the information in view v:
rwijk@ORA11GR1> insert into t
2 select level
3 , 1
4 , lpad('*',11,'*')
5 from dual
6 connect by level <= 10
7 /
10 rows created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> update t
2 set description = 'a'
3 where pkcol = 1
4 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> delete t
2 where pkcol = 7
3 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from v
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION
- ---------- --------------------- ---------- ----- ----- -----------
D RWIJK 15-NOV-08 11.12.36 AM 997358 7 1 ***********
U RWIJK 15-NOV-08 11.12.36 AM 997355 1 1 a
I RWIJK 15-NOV-08 11.12.36 AM 997352 10 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 9 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 8 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 7 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 6 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 5 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 4 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 3 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 2 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 1 1 ***********
12 rows selected.
So far the same ideas as described in the previous post. Next let's try to add a new status column with two possible values, enforced with a check constraint:
rwijk@ORA11GR1> alter table t add (status varchar2(3))
2 /
Table altered.
rwijk@ORA11GR1> alter table t add constraint ck_status check (status in ('NEW','OLD'))
2 /
alter table t add constraint ck_status check (status in ('NEW','OLD'))
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
As said in the beginning of this post, this generates an ORA-55610. This is the true idea behind the flashback data archives: it is tamper proof. But for journaling you need some more flexibility. So the idea is to introduce a fixed journaling table containing all the information currently present in view v, and setup a renewed table with a flashback data archive. The view v will "union all" both pieces together. Notice that this idea implies downtime for your table and/or application.
rwijk@ORA11GR1> create table t_jn
2 as
3 select jn_operation
4 , jn_user
5 , jn_datetime
6 , jn_scn
7 , pkcol
8 , fkcol
9 , description
10 , cast(null as varchar2(3)) status
11 from v
12 /
Table created.
rwijk@ORA11GR1> alter table t_jn read only
2 /
Table altered.
rwijk@ORA11GR1> create table t2
2 ( pkcol number(10) primary key
3 , fkcol number(10) not null references fktable(col)
4 , description varchar2(100)
5 , status varchar2(3) check (status in ('OLD','NEW'))
6 )
7 /
Table created.
rwijk@ORA11GR1> insert /*+ append */ into t2
2 ( pkcol
3 , fkcol
4 , description
5 , status
6 )
7 select pkcol
8 , fkcol
9 , description
10 , null
11 from t
12 /
9 rows created.
rwijk@ORA11GR1> alter table t no flashback archive
2 /
Table altered.
rwijk@ORA11GR1> drop table t purge
2 /
Table dropped.
rwijk@ORA11GR1> rename t2 to t
2 /
Table renamed.
rwijk@ORA11GR1> alter table t flashback archive flashback_archive_10_years
2 /
Table altered.
rwijk@ORA11GR1> audit insert,update,delete on t by access
2 /
Audit succeeded.
Here I simply renamed table t2 to t, but in real life you might want to rename (drop/add) some indexes and constraints as well. Next, create a new view v:
rwijk@ORA11GR1> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 from t versions between scn minvalue and maxvalue tv
12 , user_audit_object ao
13 where tv.versions_xid = ao.transactionid (+)
14 union all
15 select t.jn_operation
16 , t.jn_user
17 , t.jn_datetime
18 , t.jn_scn
19 , t.pkcol
20 , t.fkcol
21 , t.description
22 , t.status
23 from t_jn t
24 /
View created.
rwijk@ORA11GR1> exec dbms_lock.sleep(15)
PL/SQL procedure successfully completed.
rwijk@ORA11GR1> select * from v
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- --------------------- ---------- ----- ----- ----------- ------
1 1 a
2 1 ***********
3 1 ***********
4 1 ***********
5 1 ***********
6 1 ***********
8 1 ***********
9 1 ***********
10 1 ***********
D RWIJK 15-NOV-08 11.12.36 AM 997358 7 1 ***********
U RWIJK 15-NOV-08 11.12.36 AM 997355 1 1 a
I RWIJK 15-NOV-08 11.12.36 AM 997352 1 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 2 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 3 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 4 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 5 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 6 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 7 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 8 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 9 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 10 1 ***********
21 rows selected.
This is not exactly what we want: the new rows from v without journaling information should be filtered, as they are present in the t_jn table as well. So add a "tv.versions_startscn is not null" clause:
rwijk@ORA11GR1> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 from t versions between scn minvalue and maxvalue tv
12 , user_audit_object ao
13 where tv.versions_xid = ao.transactionid (+)
14 and tv.versions_startscn is not null
15 union all
16 select t.jn_operation
17 , t.jn_user
18 , t.jn_datetime
19 , t.jn_scn
20 , t.pkcol
21 , t.fkcol
22 , t.description
23 , t.status
24 from t_jn t
25 /
View created.
rwijk@ORA11GR1> select * from v
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- --------------------- ---------- ----- ----- ----------- ------
D RWIJK 15-NOV-08 11.12.36 AM 997358 7 1 ***********
U RWIJK 15-NOV-08 11.12.36 AM 997355 1 1 a
I RWIJK 15-NOV-08 11.12.36 AM 997352 1 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 2 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 3 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 4 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 5 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 6 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 7 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 8 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 9 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 10 1 ***********
12 rows selected.
And now we can continue doing DML and having journaling:
rwijk@ORA11GR1> insert into t values ( 11, 1, 'bla', 'OLD' )
2 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> delete t where pkcol in (3,5)
2 /
2 rows deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from v
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- --------------------- ---------- ----- ----- ----------- ------
D RWIJK 15-NOV-08 11.12.55 AM 997900 3 1 ***********
D RWIJK 15-NOV-08 11.12.55 AM 997900 5 1 ***********
I RWIJK 15-NOV-08 11.12.55 AM 997896 11 1 bla OLD
D RWIJK 15-NOV-08 11.12.36 AM 997358 7 1 ***********
U RWIJK 15-NOV-08 11.12.36 AM 997355 1 1 a
I RWIJK 15-NOV-08 11.12.36 AM 997352 1 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 2 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 3 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 4 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 5 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 6 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 7 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 8 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 9 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 10 1 ***********
15 rows selected.
Note also that the new t_jn table will not automatically lose its information after the originally specified retention period. If that's what you want to happen, you'll have to schedule a job to purge the old data.
Wednesday, October 29, 2008
Year zero
According to Wikipedia, the year zero does not exist:
But according to Oracle it does:
But wait, it really doesn't:
Or, it does:
And apparently the year zero is a leap year:
Confusing?
Let's inspect the internal representation:
Note that 3*256 + 232 = 1000.
And now the same for the BC years:
A similar formula is used to calculate the year. Only backwards starting by 255,255 (1 BC).
And the year zero apparently has its own representation:
Now let's see how the to_char function deals with these dates:
The day and month and BC indicator are '00'.
My conclusion is that internally the year zero exists in Oracle. However, the to_date and to_char functions mask this "flaw" by raising errors or printing all zeros.
Luckily, as often, the problem isn't new. A quote from this enlightening document:
Thanks Wilco and Klaas for letting me know this interesting problem.
B.C. (or BC) — Before Christ. Used for years prior to AD 1, counting backwards so the year n BC is the year 1-n AD. Using these two calendar eras as historians use them means that there is no year 0 or negative year numbers.
But according to Oracle it does:
rwijk@ORA11GR1> select date '0000-01-01' from dual
2 /
DATE'0000-01-01'
-------------------
01-01-0000 00:00:00
1 row selected.
rwijk@ORA11GR1> select date '0000-12-31' from dual
2 /
DATE'0000-12-31'
-------------------
31-12-0000 00:00:00
1 row selected.
But wait, it really doesn't:
rwijk@ORA11GR1> select to_date('0000-01-01','yyyy-mm-dd') from dual
2 /
select to_date('0000-01-01','yyyy-mm-dd') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
rwijk@ORA11GR1> select to_date('0000-12-31','yyyy-mm-dd') from dual
2 /
select to_date('0000-12-31','yyyy-mm-dd') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0Or, it does:
rwijk@ORA11GR1> select to_date('31-12-0001bc','dd-mm-yyyybc') + 1 from dual
2 /
TO_DATE('31-12-0001
-------------------
01-01-0000 00:00:00
1 row selected.
rwijk@ORA11GR1> select to_date('01-01-0001ad','dd-mm-yyyybc') - 1 from dual
2 /
TO_DATE('01-01-0001
-------------------
31-12-0000 00:00:00
1 row selected.And apparently the year zero is a leap year:
rwijk@ORA11GR1> select to_date('01-01-0001 ad','dd-mm-yyyy bc')
2 - to_date('31-12-0001 bc','dd-mm-yyyy bc')
3 from dual
4 /
TO_DATE('01-01-0001AD','DD-MM-YYYYBC')-TO_DATE('31-12-0001BC','DD-MM-YYYYBC')
-----------------------------------------------------------------------------
367
1 row selected.Confusing?
Let's inspect the internal representation:
rwijk@ORA11GR1> select dump(date '1000-01-01')
2 , dump(date '0100-01-01')
3 , dump(date '0010-01-01')
4 , dump(date '0001-01-01')
5 from dual
6 /
DUMP(DATE'1000-01-01') DUMP(DATE'0100-01-01')
------------------------------- -------------------------------
DUMP(DATE'0010-01-01') DUMP(DATE'0001-01-01')
------------------------------ -----------------------------
Typ=13 Len=8: 232,3,1,1,0,0,0,0 Typ=13 Len=8: 100,0,1,1,0,0,0,0
Typ=13 Len=8: 10,0,1,1,0,0,0,0 Typ=13 Len=8: 1,0,1,1,0,0,0,0
1 row selected.
Note that 3*256 + 232 = 1000.
And now the same for the BC years:
rwijk@ORA11GR1> select dump(date '-1000-01-01')
2 , dump(date '-0100-01-01')
3 , dump(date '-0010-01-01')
4 , dump(date '-0001-01-01')
5 from dual
6 /
DUMP(DATE'-1000-01-01') DUMP(DATE'-0100-01-01')
-------------------------------- ---------------------------------
DUMP(DATE'-0010-01-01') DUMP(DATE'-0001-01-01')
--------------------------------- ---------------------------------
Typ=13 Len=8: 24,252,1,1,0,0,0,0 Typ=13 Len=8: 156,255,1,1,0,0,0,0
Typ=13 Len=8: 246,255,1,1,0,0,0,0 Typ=13 Len=8: 255,255,1,1,0,0,0,0
1 row selected.
A similar formula is used to calculate the year. Only backwards starting by 255,255 (1 BC).
And the year zero apparently has its own representation:
rwijk@ORA11GR1> select dump(date '0000-01-01')
2 from dual
3 /
DUMP(DATE'0000-01-01')
-----------------------------
Typ=13 Len=8: 0,0,1,1,0,0,0,0
1 row selected.
Now let's see how the to_char function deals with these dates:
rwijk@ORA11GR1> select to_char(date '0001-01-01', 'dd-mm-yyyy BC')
2 , to_char(date '0000-01-01', 'dd-mm-yyyy BC')
3 , to_char(date '-0001-01-01', 'dd-mm-yyyy BC')
4 from dual
5 /
TO_CHAR(DATE' TO_CHAR(DATE' TO_CHAR(DATE'
------------- ------------- -------------
01-01-0001 AD 00-00-0000 00 01-01-0001 BC
1 row selected.
The day and month and BC indicator are '00'.
My conclusion is that internally the year zero exists in Oracle. However, the to_date and to_char functions mask this "flaw" by raising errors or printing all zeros.
Luckily, as often, the problem isn't new. A quote from this enlightening document:
After trying many date-arithmetic calculations for dates before 1 AD, we're able to state this with confidence: Oracle is using BC/AD notation but is using the astronomical convention. Presumably some Oracle programmer in the distant past lifted an astronomer's calculation algorithm and plugged it into the Oracle engine, without realizing that the negative year numbers are all exactly one year different from BC numbers. Oracle can't fix this without wrecking existing applications, although it could help by admitting there's a year zero and allowing dates in "0 AD" rather than returning an error.
Thanks Wilco and Klaas for letting me know this interesting problem.
Saturday, October 18, 2008
DBA_DEPENDENCY_COLUMNS
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:
Let's see for ourselves:
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:
And create a view using just two columns, col1 and col3:
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:
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:
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:
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:
Great! And for a procedure:
Good as well. No guarantees are given however that this view will always give the right results, but for these simple examples, it does.
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.