Installing Oracle 12.1.0.2 on Red Hat 7

This is not a complete guide how to do the installation. Just a note how to get a RPM-GPG-KEY-oracle file to Red Hat 7. Go to oracle-base for installation notes on oel7.

Installation documentation is mentioning: "Starting with Oracle Database 12c Release 1 (12.1.0.2), Oracle Linux 7 and Red Hat Enterprise Linux 7 are supported on Linux x86-64 systems." So do not try to install 12.1.0.1 on Red Hat 7. The case when you have SE one licence and not yet buying SE2 license. Just to mention 11.2 is supported to be installed on Red Hat Enterprise Linux 7.

Download the information about Oracle public yum repo https://docs.oracle.com/cd/E52668_01/E54669/html/ol7-downloading-yum-repo.html

cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-ol7.repo

Try to install preinstall package and get an error.

yum install oracle-rdbms-server-12cR1-preinstall

...

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

GPG key retrieval failed: [Errno 14] curl#37 - "Couldn't open file /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle"

You need the file mentioned here https://docs.oracle.com/cd/E37670_01/E39381/html/ol_import_gpg.html

wget http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol7 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

And you will succeed installing:

yum install oracle-rdbms-server-12cR1-preinstall

SUM over time -Elements

A while ago wrote how to calculate sum over time. During overlapping time intervals the resulting sum is produced there. But how about if one needs the elements that the sum is generated from?

2015-07-25 2015-07-26 1
2015-07-26 2015-07-28 2
2015-07-27 2015-07-30 3

The testdata is generated in td view.

with td as (
 select trunc(sysdate) + mod(level, 4) validFrom
      , trunc(sysdate) + mod(level, 8) * 2 validTill
      , level a
   from dual
connect by level < 4
), b as (
  select row_number()over(order by case r when 1 then validFrom else validTill end,r desc) rn
       , a
       , r
   , case r when 1 then validFrom else validTill end validFrom
   , sum(case r when 1 then a else -a end) over (order by case r when 1 then validFrom else validTill end,r desc) sumover
   , sum(case r when 1 then 1 else -1 end) over (order by case r when 1 then validFrom else validTill end,r desc) cntover
   from td, (
  select rownum r from dual connect by level < 3
  )
),c (rn,a,r,validfrom,sumover,cntover,vals) as (
 select rn,a,r,validfrom,sumover,cntover,mdsys.SDO_NUMTAB(a) from b where rn = 1
union all
select b.rn,b.a,b.r,b.validFrom,b.sumover,b.cntover,case b.r when 1 then c.vals multiset union mdsys.SDO_NUMTAB(b.a) else c.vals multiset except mdsys.SDO_NUMTAB(b.a) end
  from b,c where b.rn = c.rn+1 
),d as (select max(cntover)over(partition by validFrom) mx,a,r,validFrom,sumover,cntover,(select count(*) from table(vals)) n,vals, count(*)over(partition by validFrom) cn 
  from c 
  where cntover=(select count(*) from table(vals))
) 
select validFrom
     , nvl(lead(validFrom) over (order by validFrom)
         , to_date('22000101','yyyymmdd')) validTill
     , sumover
     , (select listagg(column_value,'+')within group(order by column_value) from table(vals)) vals
  from d
 where cntover=mx
order by validFrom;

2015-07-25 2015-07-26 1 1
2015-07-26 2015-07-27 2 2
2015-07-27 2015-07-28 5 2+3
2015-07-28 2015-07-30 3 3
2015-07-30 2200-01-01 0