Bonjour,
J'essaye de créer des vues matérialisées dans ma base de données, elles sont toutes relativement similaires, mais seulement deux sur les 4 ont été créées.
J'ai tout d'abord créé des tables de log sur les tables requises comme ceci :
Ensuite voici les deux vm qui sont passées avec un petit commentaire
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 CREATE MATERIALIZED VIEW LOG ON ApplicationStats WITH rowid, SEQUENCE(Status, DateStatus, Counted, ApplicationId) INCLUDING new values; CREATE MATERIALIZED VIEW LOG ON SlaList WITH rowid, SEQUENCE(Days, StartDate, EndDate, Id) INCLUDING new values; CREATE MATERIALIZED VIEW LOG ON Applications WITH rowid, SEQUENCE(id, slaId) INCLUDING new values;
Et le code des deux qui ne sont pas passées avec l'erreur sous chacune d'elle
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 --vue pour avoir le total par jour des status ok et ko de chaque application CREATE MATERIALIZED VIEW ApplicationStatsDay REFRESH fast START WITH TRUNC(SYSDATE + 1) + 1 / 24 NEXT TRUNC(SYSDATE + 1) + 1 AS SELECT Ap.id AS Appid, trunc(aps.datestatus) AS Day, TO_CHAR(TRUNC(aps.datestatus), 'YYYYIW') AS Week, TO_CHAR(TRUNC(aps.datestatus), 'YYYYMM') AS Month, COUNT(aps.status) AS nb_applicationstats, aps.status AS status FROM applications Ap, applicationstats aps, SlaList WHERE Ap.id = aps.applicationid AND Slalist.id = Ap.slaid AND aps.counted = 1 AND TO_NUMBER(TO_CHAR(aps.datestatus,'HH24'))*60 + TO_NUMBER(TO_CHAR(aps.datestatus,'MI')) BETWEEN TO_NUMBER(TO_CHAR(Slalist.startdate,'HH24'))*60 + TO_NUMBER(TO_CHAR(Slalist.startdate,'MI')) AND TO_NUMBER(TO_CHAR(Slalist.endDate,'HH24'))*60 + TO_NUMBER(TO_CHAR(Slalist.endDate,'MI')) AND INSTR(Slalist.Days, TO_CHAR(aps.datestatus,'DY')) <> 0 GROUP BY ap.id, TRUNC(aps.datestatus), aps.status ORDER BY appid, month, week, day / --vue pour avoir le total par mois des status ok et ko de chaque application CREATE MATERIALIZED VIEW ApplicationStatsMonth REFRESH fast START WITH TRUNC(SYSDATE + 1) + 1 / 24 NEXT TRUNC(SYSDATE + 1) + 1 AS SELECT Ap.id AS Appid, TO_CHAR(TRUNC(aps.datestatus), 'MONTH') AS Month, TO_CHAR(TRUNC(aps.datestatus), 'MM') AS MonthNumber, TO_CHAR(TRUNC(aps.datestatus), 'YYYY') AS Year, COUNT(aps.status) as nb_applicationstats, aps.status AS status FROM applications Ap, applicationstats aps, SlaList WHERE Ap.id = aps.applicationid AND Slalist.id = Ap.slaid AND aps.counted = 1 AND TO_NUMBER(TO_CHAR(aps.datestatus,'HH24'))*60 + TO_NUMBER(TO_CHAR(aps.datestatus,'MI')) BETWEEN TO_NUMBER(TO_CHAR(Slalist.startdate,'HH24'))*60 + TO_NUMBER(TO_CHAR(Slalist.startdate,'MI')) AND TO_NUMBER(TO_CHAR(Slalist.endDate,'HH24'))*60 + TO_NUMBER(TO_CHAR(Slalist.endDate,'MI')) AND INSTR(Slalist.Days, TO_CHAR(aps.datestatus,'DY')) <> 0 GROUP BY ap.id, TO_CHAR(TRUNC(datestatus), 'MONTH'), TO_CHAR(TRUNC(datestatus), 'MM'), TO_CHAR(TRUNC(datestatus), 'YYYY'), aps.status ORDER BY appid, year, monthNumber /
Je ne comprends aucune des deux erreurs car la première est quasiment identique que la vm ApplicationStatsMonth (deuxième qui est passée) et la deuxième échouée est une copie conforme de la table d'origine mais avec des lignes en moins en gros...
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42 --vue pour avoir le total par semaine des status ok et ko de chaque application CREATE MATERIALIZED VIEW ApplicationStatsWeek REFRESH fast START WITH TRUNC(SYSDATE + 1) + 1 / 24 NEXT TRUNC(SYSDATE + 1) + 1 AS SELECT Ap.id AS Appid, 'Week '||TO_CHAR(TRUNC(aps.datestatus), 'IW') AS Week, TO_CHAR(TRUNC(aps.datestatus), 'YYYYQ') AS Quarter, COUNT(aps.status) AS nb_applicationstats,aps.status AS status FROM applications Ap, applicationstats aps, SlaList WHERE Ap.id = aps.applicationid AND Slalist.id = Ap.slaid AND aps.counted = 1 AND TO_NUMBER(TO_CHAR(aps.datestatus,'HH24'))*60 + TO_NUMBER(TO_CHAR(aps.datestatus,'MI')) BETWEEN TO_NUMBER(TO_CHAR(Slalist.startdate,'HH24'))*60 + TO_NUMBER(TO_CHAR(Slalist.startdate,'MI')) AND TO_NUMBER(TO_CHAR(Slalist.endDate,'HH24'))*60 + TO_NUMBER(TO_CHAR(Slalist.endDate,'MI')) AND INSTR(Slalist.Days, TO_CHAR(aps.datestatus,'DY')) <> 0 GROUP BY ap.id, TO_CHAR(TRUNC(aps.datestatus), 'IW'), TO_CHAR(TRUNC(aps.datestatus), 'YYYYQ'), aps.status ORDER BY appid, quarter, week / AND INSTR(Slalist.Days, TO_CHAR(aps.datestatus,'DY')) <> 0 * ERROR at line 12: ORA-12015: cannot create a fast refresh materialized veiw from a complex query --vue de la table ApplicationStats pour ne voir que les tests étant dans la période SLA CREATE MATERIALIZED VIEW ApplicationStatsSla REFRESH fast START WITH TRUNC(SYSDATE + 1) + 1 / 24 NEXT TRUNC(SYSDATE + 1) + 1 AS SELECT aps.id, aps.status, aps.datestatus, aps.counted, aps.maintComment, aps.applicationId FROM ApplicationStats aps, SlaList, applications a WHERE a.id = aps.applicationId AND a.slaId = SlaList.id AND TO_NUMBER(TO_CHAR(aps.datestatus,'HH24'))*60 + TO_NUMBER(TO_CHAR(aps.datestatus,'MI')) BETWEEN TO_NUMBER(TO_CHAR(SlaList.StartDate,'HH24'))*60 + TO_NUMBER(TO_CHAR(SlaList.StartDate,'MI')) AND TO_NUMBER(TO_CHAR(SlaList.EndDate,'HH24'))*60 + TO_NUMBER(TO_CHAR(SlaList.EndDate,'MI')) AND INSTR(SlaList.Days, TO_CHAR(aps.datestatus,'DY')) <> 0 / AND INSTR(SlaList.Days, TO_CHAR(aps.datestatus,'DY')) <> 0 * ERROR at line 11: ORA-12052: cannot fast refresh materialized view PORTAL.APPLICATIONSTATSSLA
Voyez vous quelque chose qui cloche ?
Pour info je tourne sous 11g en dev, et le serveur de prod sur lequel les vues seront retranscrites tourne sous 10g.
Merci d'avance pour votre aide car je ne vois pas du tout ce qui cloche vu la similitude qu'il y a entre les requêtes...
Partager