error creating mv
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: error creating mv

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    126

    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 ????
    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>

  2. #2
    Join Date
    Feb 2006
    Posts
    3
    Hm... I have had something very similar to this.
    Does anyone know what is the problem?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you got the GLOBAL QUERY REWRITE privilege?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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

  5. #5
    Join Date
    Dec 2000
    Posts
    126
    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>

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you got the GLOBAL QUERY REWRITE privilege?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2000
    Posts
    126

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width