1)
ops$tkyte@ORA920PC> select ver_date,
2 when,
3 nvl( max( decode( reg_no, 11, 'Y' ) ), 'N' ) "11",
4 nvl( max( decode( reg_no, 12, 'Y' ) ), 'N' ) "12",
5 nvl( max( decode( reg_no, 03, 'Y' ) ), 'N' ) "03"
6 from (
7 select distinct
8 reg_no, trunc(ver_date) ver_date,
9 case when to_char(ver_date,'hh24') between 0 and 11 then 'Morn'
10 when to_char(ver_date,'hh24') between 12 and 17 then 'After'
11 else 'Evening'
12 end when
13 from t
14 )
15 group by ver_date, when
16 /
VER_DATE WHEN 11 12 03
------------------- ------- -- -- --
08-10-2002 00:00:00 Morn N Y N
08-10-2002 00:00:00 After Y N N
08-10-2002 00:00:00 Evening Y Y Y
ops$tkyte@ORA920PC> select * from t;
REG_NO VER_DATE
---------- -------------------
12 08-10-2002 18:20:31
11 08-10-2002 13:20:34
11 08-10-2002 18:20:36
11 08-10-2002 18:20:39
11 08-10-2002 18:20:41
11 08-10-2002 18:20:44
3 08-10-2002 18:20:50
11 08-10-2002 18:20:52
11 08-10-2002 18:20:55
11 08-10-2002 18:20:57
11 08-10-2002 18:21:00
11 08-10-2002 18:21:07
12 08-10-2002 18:21:13
11 08-10-2002 18:21:16
11 08-10-2002 18:21:18
11 08-10-2002 18:21:21
11 08-10-2002 18:21:23
12 08-10-2002 11:21:26
18 rows selected.
2) doesn't even make SENSE.
we invented the large pool specifically to KEEP triggers/packages/etc away from the data we put in the large pool!!!!
The shared pool (where these things go) is managed in an LRU fashion. It makes sense for them there.
The large pool (where UGA, rman and PQ message buffers go) is managed in a heap using "allocate" and "free". Memory is not "aged" out -- it is released
3) don't know what calendar you see daily, but give
break on month skip 1
set linesize 20
column month 20
select lpad( Month, 20-(20-length(month))/2 ) month,
"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
from (
select to_char(dt,'fmMonthfm YYYY') month,
to_char(dt+1,'iw') week,
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from ( select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') )
group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
)
order by to_date( month, 'Month YYYY' ), to_number(week)
/
a look see
4) </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:533422350794 <code>