error creating mv

Hi,

I have this error when creating mv.
Any help is appreciated.

Thnks

SQL> CREATE MATERIALIZED VIEW S42LD.LDMV_CONTRACT_FIN_DTL
2 TABLESPACE S42LD_DATA
3 NOCACHE
4 LOGGING
5 NOCOMPRESS
6 PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
7 BUILD IMMEDIATE
8 REFRESH FORCE ON DEMAND
9 WITH PRIMARY KEY
10 ENABLE QUERY REWRITE
11 AS
12 (
13 SELECT f.*
14 FROM LDV_CONTRACT_FIN_DTL f <<<< OK here - single table :)
15 );

Materialized view created.



1 CREATE MATERIALIZED VIEW S42LD.LDMV_CONTRACT_FIN_DTL2
2 TABLESPACE S42LD_DATA
3 NOCACHE
4 LOGGING
5 NOCOMPRESS
6 PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
7 BUILD IMMEDIATE
8 REFRESH FORCE ON DEMAND
9 WITH PRIMARY KEY
10 ENABLE QUERY REWRITE
11 AS
12 (
13 SELECT f.*,p.party_id cpty_id ,name cpty
14 FROM LDV_CONTRACT_FIN_DTL f <<<< ERROR ???? :confused:
15 ,axiom.contract c, axiom.party p
16 where c.cparty_id = p.party_id
17 and c.contract_id = f.contract_id
18* )
SQL> /
FROM LDV_CONTRACT_FIN_DTL f
*
ERROR at line 14:
ORA-01031: insufficient privileges


SQL>

Hm... I have had something very similar to this.
Does anyone know what is the problem?

Have you got the GLOBAL QUERY REWRITE privilege?

I would check through the prerequisites for CREATE MATERIALIZED VIEW:

download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_63a.htm#2063839

sorry about delay,
But I can create MV with single table ,

SQL> CREATE MATERIALIZED VIEW S42LD.LDMV_CONTRACT_FIN_DTL
2 TABLESPACE S42LD_DATA
3 NOCACHE
4 LOGGING
5 NOCOMPRESS
6 PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
7 BUILD IMMEDIATE
8 REFRESH FORCE ON DEMAND
9 WITH PRIMARY KEY
10 ENABLE QUERY REWRITE
11 AS
12 (
13 SELECT f.*
14 FROM LDV_CONTRACT_FIN_DTL f <<<< OK here - single table
15 );

Materialized view created.

But have error when using more than 1 tables:

1 CREATE MATERIALIZED VIEW S42LD.LDMV_CONTRACT_FIN_DTL2
2 TABLESPACE S42LD_DATA
3 NOCACHE
4 LOGGING
5 NOCOMPRESS
6 PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
7 BUILD IMMEDIATE
8 REFRESH FORCE ON DEMAND
9 WITH PRIMARY KEY
10 ENABLE QUERY REWRITE
11 AS
12 (
13 SELECT f.*,p.party_id cpty_id ,name cpty
14 FROM LDV_CONTRACT_FIN_DTL f <<<< ERROR ????
15 ,axiom.contract c, axiom.party p
16 where c.cparty_id = p.party_id
17 and c.contract_id = f.contract_id
18* )
SQL> /
FROM LDV_CONTRACT_FIN_DTL f
*
ERROR at line 14:
ORA-01031: insufficient privileges


SQL>

Have you got the GLOBAL QUERY REWRITE privilege?

Hi Slimdave,

Thank you for reply.

How can I check if I have GLOBAL QUERY REWRITE privilege ?

Thks