create pluggable database NIHILUS admin user NIHILUS$OWNER identified by secret;
alter pluggable database NIHILUS open;
alter pluggable database NIHILUS save state;

orapwd file=orapwSITHPRD password="ad420e57a205c9a7d80d!"

alias NIHILUS='rlwrap sqlplus adm/"secret"@bakura:1521/NIHILUS as sysdba'

alter session set container=NIHILUS;

create user DEMO identified by secret;
grant connect, resource to DEMO;
grant create materialized view to DEMO;
grant create view to DEMO;
grant unlimited tablespace to DEMO;

alias DEMO='rlwrap sqlplus DEMO/"secret"@bakura:1521/NIHILUS'

create table DEMO as 
  select 0 seq,current_timestamp now 
     from 
     xmltable('1 to 1000');


-- infinite_update.sql
whenever sqlerror exit failure
begin
   loop 
     update demo set seq=seq+1,now=current_timestamp where rownum=1;
     commit; 
     dbms_session.sleep(1);
   end loop;
end;
/


select max(seq),max(now) from DEMO.DEMO;
create materialized view DEMOMV1 as select * from DEMO;
create materialized view DEMOMV2 as select * from DEMO;

create view V as
              select 'DEMOMV1' source,seq,now from DEMOMV1
              union all
              select 'DEMOMV2' source,seq,now from DEMOMV2
              union all              
              select 'DEMO' source,seq,now from DEMO;

set lines 256
col maxseq for 999999999
col maxnow for a50

select source,max(seq) maxseq,max(now) maxnow from V group by source;


exec dbms_refresh.make('DEMO.DEMORGROUP', list=>'DEMOMV1,DEMOMV2', next_date=>null, interval=>'null');

exec dbms_refresh.refresh('DEMO.DEMORGROUP');

-- we can index and gather stats on materialized views
create index IMV1 on DEMOMV1(seq);
create index IMV2 on DEMOMV2(now);

exec dbms_stats.gather_table_stats(user,'DEMOMV1', method_opt=>'for all columns size SKEWONLY');
exec dbms_stats.gather_table_stats(user,'DEMOMV2', method_opt=>'for all columns size AUTO');

alter table DEMO add constraint PK_DEMO primary key (NOW);

create materialized view log on DEMO.DEMO
	including new values;