Erreur à la création d'une vue matérialisée refresh fast

Version imprimable

Bonjour tout le monde !
J'espere que vous pourez aider un petit nouveau. J'essai de mettre en place des vues matérialisées pour notre systeme d'infocentre.

Personne connait le fonctionnement des materialized view dans ma boite alors j'essai de m'en sortir comme un grand.

J'ai deux utilisateurs "BO" et "CLIENT" sur une même instance. Je souhaite créer une vue materialiée BO_VILLE sur l'utilisateur BO à partire de la table VILLE de l'utilisateur CLIENT.

J'ai fait les manipulations suivantes :

Sur l'utilisateur "CLIENT" :
Code:

1 2 grant select on VILLE to BO; create materialized view log on VILLE;
Ensuite sur l'utilisateur "BO" :
(remarque : la commande "select * from client.ville" retourne bien des données)
Code:

1 2 create materialised view bo_ville refresh fast as select * from client.ville;
J'ai alors le messages d'erreur suivant :
Code:

1 2 3 4 5 6 select * from client.ville * ERREUR à la ligne 2 : ORA-12018: l'erreur suivante est survenue pendant la génération du code pour "BO"."BO_VILLE" ORA-00942: Table ou vue inexistante
Quand je ne met pas l'option "refresh fast" ça fonctionne :
Code:

1 2 3 4 create materialized view bo_ville as select * from client.ville; Vue matérialisée créée.
Quelqu'un peut-il me sauver la vie ?
merki !

Juste une idée, il manque pas le ON COMMIT :
Code:

1 2 create materialised view bo_ville refresh fast on commit as select * from client.ville;
Laly.

Code:

1 2 3 4 5 6 7 create materialized view bo_ville refresh fast on commit as select * from client.ville; select * from client.ville * ERREUR à la ligne 2 : ORA-12054: impossible de définir l'attribut de régénération ON COMMIT pour la vue matérialisée
Y'a un mieux ! Je devrais trouver des infos plus facilement sur cette nouvelle erreur sur tahiti.oracle.com. (pas évident, je suis pas un pro de l'englais) Merki !

26/04/2005, 11h50
zijulo

Ah ben non !
Sur tahiti.oracle.com ils mettent ça comme explication sur l'erreur ORA-12054:

Cause: The materialized view did not satisfy conditions for refresh at commit time.
Action: Specify only valid options.

Je suis bien avancé ! :?
26/04/2005, 11h50
bouyao

Version Oracle et OS ?
26/04/2005, 11h52
zijulo

Ah oui j'ai oublié :

Oracle client 9.2.0.4.0 ici sur une base en 8.1.7.0.0
OS Windows XP pro.
26/04/2005, 12h00
lalystar

Une question bête : que se passe t il si tu essaie de créer le snapshot dans le schéma VILLE ? J'ai l'impression qu'Oracle ne voit pas le snapshot log à partir de l'user BO...

Laly.
26/04/2005, 12h09
bouyao

Essaye de créer le snapshot avec le mode refresh
COMPLETE puis ut fait alter snapshot en mode fast
26/04/2005, 12h18
zijulo

Citation:

Envoyé par bouyao

Essaye de créer le snapshot avec le mode refresh
COMPLETE puis ut fait alter snapshot en mode fast

Ca marche !!! C'est le plus beau jour de ma vie !! :D

C'est marant quand même. Pour info, la création en mode fast fonctionne sur le meme utilisateur. C'est bien de le créer sur un utilisateur different qui provoque l'erreur. Pour le "on commit" par contre ca fait toujours la meme erreur.

Un grand merci à tous ! :wink:
26/04/2005, 12h46
bouyao

pensez à résolu.
26/04/2005, 13h23
zijulo

Exact ! sorry.

Euh... Ca doit pas se rafraichir régulièrement ces machins la ? il faut faire un truc pour parametrer le délai de rafraichissement ? parce que j'ai ajouté un élément dans la table client.ville mais je l'ai toujours pas dans mon snap !
26/04/2005, 13h46
orafrance

il faut rafraichir le snapshot bien sûr :?
26/04/2005, 13h55
lalystar

Citation:

Envoyé par orafrance

il faut rafraichir le snapshot bien sûr :?

Oui tout à fait, j'avais oublié qu'on pouvait avoir un snapshot en refresh fast mais sans on commit :oops:

Laly.
26/04/2005, 14h03
orafrance

j'ai un doute d'un coup... ne serait-ce pas une colonne qu'a été ajouté ? :?

Si c'est le cas, il faut recréer le snapshot :)

Citation:

Oui tout à fait, j'avais oublié qu'on pouvait avoir un snapshot en refresh fast mais sans on commit

Le on commit permet de lancer le rafraichissement des snap à chaque commit c'est ca l'idée ? Diantre ! alors il faut que j'arrive à faire fonctionner le refresh fast on commit !

J'ai toujours cette erreur :
Code:

1 2 3 4 5 6 alter materialized view bo_ville refresh fast on commit; alter materialized view bo_ville refresh fast on commit * ERREUR à la ligne 1 : ORA-12054: impossible de définir l'attribut de régénération ON COMMIT pour la vue matérialisée
Je vais y arriver ! j'y croie j'y croie j'y croie !!!

26/04/2005, 14h30
lalystar

Citation:

Envoyé par lalystar

Une question bête : que se passe t il si tu essaie de créer le snapshot dans le schéma VILLE ? J'ai l'impression qu'Oracle ne voit pas le snapshot log à partir de l'user BO...

Est-ce-que tu as fait le test ?

Laly.
26/04/2005, 14h32
bouyao

La table maitre est-elle un IOT ?
26/04/2005, 14h50
bouyao

Une note sur metalink :

Citation:

Problem Description
-------------------

You are trying to create a materialized view which refreshes automatically
when the underlying table is updated.
However, the create command fails with an ora-12051 or an ora-12054.
The select statement that you are using could be any of the following:

==============================================
create materialized view log on emp
with rowid(empno, ename, job, mgr, hiredate, sal, comm, deptno)
including new values
/
=============================================
create materialized view mv_emp_1
build immediate refresh fast on commit
as
select deptno, sum(sal), count(sal)
from emp
group by deptno
/

from emp
*
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

=============================================
or
=============================================
create materialized view mv_emp_1
build immediate refresh fast on commit
as
select deptno, sum(sal)
from emp
group by deptno
/
=============================================
from emp
*
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Solution Description
--------------------

The correct script for creation of this materialized view is as follows:
==============================================
create materialized view log on emp
with rowid(empno, ename, job, mgr, hiredate, sal, comm, deptno)
including new values
/
================================================
create materialized view mv_emp_1
build immediate refresh fast on commit
as
select count(*), deptno, sum(sal), count(sal)
from emp
group by deptno
/
================================================

Explanation
-----------

One of the mandatory requirements for creation of an on-commit materialized
view has not been satisfied.

ON-COMMIT :
Refresh occurs automatically when a transaction that modified one of the
materialized view's fact tables commits.
Can be used with materialized views on single table aggregates
and with materialized views containing joins only.

As can be seen from above, an ON-COMMIT can be used only under specific cases:
These are:

1. The M.V. should have a single table aggregate or
2. the M.V. should have a join only.
3. count(*) must be present for Single-Table Aggregates (see example above).
4. count(<col>) should be present.
Here, <col> stands for the column which is being aggregated.
Note: the only time that count(col)is not required is when
the aggregate itself is a count(col).
5. It should be possible perform a fast refresh on the materialized view.

Fast refresh by itself has a few restrictions.
These are as follows:
a)The FROM list must contain base tables only (that is, no views).
b)It cannot contain references to non-repeating expressions like
SYSDATE and ROWNUM.
c)It cannot contain references to RAW or LONG RAW data types.
d)It cannot contain HAVING or CONNECT BY clauses.
e)The WHERE clause can contain only joins and they must be equi-joins
(inner or outer) and all join predicates must be connected with
ANDs. No selection predicates on individual tables are allowed
f)It cannot have subqueries, inline views, or set functions like
UNION or MINUS.

In addition for M.V.'s with Single-Table Aggregates and Materialized Views
with Joins and Aggregates, there are some more conditions on refresh
to the ones mentioned above:

Single Table Aggregates:
=======================
i) They can only have a single table.
ii) The SELECT list must contain all GROUP BY columns.
iii) Expressions are allowed in the GROUP BY and SELECT
clauses provided they are the same.
iv) They cannot have a WHERE clause.
v) They cannot have a MIN or MAX function.
vi) A materialized view log must exist on the table and must contain all
columns referenced in the materialized view. The log must have been
created with the INCLUDING NEW VALUES clause.
vii) If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr).
viii) If VARIANCE(expr) or STDDEV(expr) is specified,
you must have COUNT(expr) and SUM(expr).

Joins and Aggregates :
=====================

i)The WHERE clause can contain inner equi-joins only
(that is, no outer joins)
ii)Materialized views from this category are FAST refreshable after
Direct Load to the base tables; they are not FAST refreshable after
conventional DML to the base tables.
iii)Materialized views from this category can have only the
ON DEMAND option (so, the on-commit cannot be used for this category).
26/04/2005, 14h58
zijulo

Citation:

Une question bête : que se passe t il si tu essaie de créer le snapshot dans le schéma VILLE ? J'ai l'impression qu'Oracle ne voit pas le snapshot log à partir de l'user BO...

Est-ce-que tu as fait le test ?

Oui, sur l'utilisateur CLIENT, le snap se crée sans problème directement en refresh fast. Par contre, la création avec refresh fast on commit donne le même message d'erreur ora-12054 partout.

Citation:

La table maitre est-elle un IOT ?

qu'est c'est qu'un IOT ?

Merki pour la note metalink, je vais essayé de tripoter ça dans tous les sens et je vous dis ce qu'il en est.
26/04/2005, 15h04
bouyao

IOT : LES TABLES ORGANISÉES EN INDEX .
26/04/2005, 15h36
orafrance

http://oracle.developpez.com/guide/a...age=Chap1#L1.4 :twisted:
26/04/2005, 15h51
zijulo

Cool, ça ! mais nous non. c'est des tables indexées classiques. 8)

Est-ce-que tu peux recréer la MV log en adaptant l'exemple de la note Metalink fournie par Bouayo :
Code:

1 2 3 4 create materialized view log on emp with rowid(empno, ename, job, mgr, hiredate, sal, comm, deptno) including new values
Laly.

J'ai réussi à reproduire le pb. La phrase clé toujours dans la note ML de Bouayo est :

Citation:

Can be used with materialized views on single table aggregates
and with materialized views containing joins only.

Code:

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
43
44
45
46
47
48
49
50
 
SQL> create table TEST as select rownum rnk from all_objects;
 
Table created
 
SQL> alter table TEST add constraint pk_test primary key (rnk);
 
Table altered
 
SQL> create snapshot log on TEST with rowid(rnk) including new values;
 
Materialized view log created
 
SQL> create snapshot S_TEST refresh fast on commit as
  2  select *
  3  from   TEST;
 
create snapshot S_TEST refresh fast on commit as
select *
from   TEST
 
ORA-12054: impossible de définir l'attribut de régénération ON COMMIT pour la vue matérialisée
 
SQL> create snapshot S_TEST refresh fast on commit as
  2  select rnk, count(*)
  3  from   TEST
  4  group by rnk;
 
Materialized view created
 
SQL> insert into TEST values(-1);
 
1 row inserted
 
SQL> select * from S_TEST where rnk = -1;
 
       RNK   COUNT(*)
---------- ----------
 
SQL> commit;
 
Commit complete
 
SQL> select * from S_TEST where rnk = -1;
 
       RNK   COUNT(*)
---------- ----------
        -1          1
 
SQL>

Laly.

La commande de création du snapshot log avec primary key fonctionne :

Code:

create snapshot log on VILLE with rowid(VIL_ID) including new values;

Par contre, cette même commande ne fonctionne pas avec une materialized view.

Sinon, j'ai fait un snapshot log avec une primary key. J'ai pu créer un snapshot avec un group by sur la primary key. Mais maintenant, quand j'insert dans la table maitre j'ai une erreur bizard :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SQL> insert into o2_ville 2 values 3 ( 4 o2_seq_ville.nextval, 5 'DUVALVILLE', 6 'DUVALVILLE', 7 'TSJD2', 8 '32454'); 1 ligne créée. SQL> commit; commit * ERREUR à la ligne 1 : ORA-00942: Table ou vue inexistante
Mais je vais faire un peu de ménage dans mes bases et je referai des essais.

27/04/2005, 11h24
bouyao

il faut ajouter le privilege SELECT ANY TABLE à l'utilisaur qui a la vue materlialisé.
27/04/2005, 11h29
zijulo

Oui exact ! suis-je distrait ! L'utilisateur maitre n'avait pas les droits sur les objets de l'utilisateur fils.