Question on permissions for base tables of Materialized view and mv logs

From: g3000 <carlton_gregory_at_yahoo.com>
Date: 25 Jul 2006 13:48:36 -0700
Message-ID: <1153860516.282328.164260@75g2000cwc.googlegroups.com>

I created the following materialized view as a user that has select any table, on commit refresh system privleges ( im on 9.2.0.7 EE 64 bit AIX )

SQL> create materialized view dis_eul.dis_mv

  2     PARTITION BY HASH(store_id)
  3     PARTITIONS 8
  4     STORE IN (DISCOVERER_DATA, DISCOVERER_DATA, DISCOVERER_DATA,

DISCOVERER_DATA, DISCOVERER_DATA, DISCOVERER_DATA, DISCOVERER_DATA, DISCOVERER_DA
TA)

  5     NOCACHE
  6     NOPARALLEL
  7        build immediate
  8        refresh on demand
  9        --with rowid
 10        enable query rewrite
 11      as
 12      SELECT    /*+ index(itmloc pk_as_itm_rtl_str) index(soh

pk_rk_store_item_soh) index(ris pk_rk_item_supplier) index(itm pk_as_itm)*/

 13                   psr.rowid pa_s_r_rowid,
 14                   psr.id_str_rt store_id,
 15                   po.rowid pa_o_rowid,
 16                   po.nm_orgn store_name,
 17                   soh.rowid soh_rowid,
 18                   soh.id_itm item_id,

 19
SUBSTR(itm.id_strc_mr,1,INSTR(itm.id_strc_mr,':')-1) Dept,

 20                   soh.id_itm ||'-'|| itm.de_itm id_desc,
 21                   itm.rowid as_i_rowid,
 22                   itm.de_itm item_description,
 23                   itm.lu_uom_dft default_uom,
 24                  itmloc.rowid as_irs_rowid,
 25                  itmloc.id_spr primary_supplier,
 26                  ps.rowid pa_spr_rowid,
 27                  ps.nm_spr vendor_name,
 28                  itmloc.id_spr ||'-'|| ps.nm_spr VDesc,
 29                  ris.rowid rk_is_rowid,
 30                  ris.vpn vendor_style,
 31                  itmloc.repn_type repn_type,
 32                  soh.total_quantity soh, soh.in_transit_quantity
in_transit_quantity,
 33                  soh.adjust_unavail_qty unavailable_quantity,
 34                  CASE WHEN soh.total_quantity = 0 THEN 'ZERO'
 35                       WHEN soh.total_quantity < 0 THEN 'NEGATIVE'
END status
 36             FROM sim.pa_str_rtl psr,
 37                  sim.as_itm_rtl_str itmloc,
 38                  sim.as_itm itm,
 39                  sim.rk_item_supplier ris,
 40                  sim.pa_spr ps,
 41                  sim.rk_store_item_soh soh,
 42                  sim.pa_orgn po
 43            WHERE itmloc.id_str_rt = psr.id_str_rt
 44              AND itmloc.id_itm = itm.id_itm
 45              AND itmloc.id_itm = ris.id_itm
 46              AND itmloc.id_spr = ris.id_spr
 47              AND ris.id_spr    = ps.id_spr
 48              AND soh.id_str_rt = itmloc.id_str_rt
 49              AND soh.id_str_rt IN (


'527','10','227','77','55','468','393')

 50              AND psr.id_prty   = po.id_prty_orgn
 51              AND soh.id_itm    = itmloc.id_itm;

Materialized view created.

----logs where created for the base tables from owner schema ( sim ) SQL> DROP SNAPSHOT LOG ON SIM.AS_ITM; Materialized view log dropped.

Elapsed: 00:00:00.47
SQL> CREATE MATERIALIZED VIEW LOG ON SIM.AS_ITM   2 TABLESPACE RETEK_DATA
  3 PCTUSED 0
  4 PCTFREE 60
  5 INITRANS 2
  6 MAXTRANS 255
  7 STORAGE (

  8              INITIAL          1M
  9              MINEXTENTS       1
 10              MAXEXTENTS       2147483645
 11              PCTINCREASE      0
 12             )

 13 NOCACHE
 14 NOPARALLEL
 15 WITH ROWID, PRIMARY KEY, SEQUENCE
 16 (DE_ITM,LU_UOM_DFT) INCLUDING NEW VALUES; Materialized view log created.

Elapsed: 00:00:00.47
SQL> DROP SNAPSHOT LOG ON SIM.AS_ITM_RTL_STR; Materialized view log dropped.

Elapsed: 00:00:00.46
SQL> CREATE MATERIALIZED VIEW LOG ON SIM.AS_ITM_RTL_STR   2 TABLESPACE RETEK_DATA
  3 PCTUSED 0
  4 PCTFREE 60
  5 INITRANS 2
  6 MAXTRANS 255
  7 STORAGE (

  8              INITIAL          1M
  9              MINEXTENTS       1
 10              MAXEXTENTS       2147483645
 11              PCTINCREASE      0
 12             )

 13 NOCACHE
 14 NOPARALLEL
 15 WITH ROWID, PRIMARY KEY, SEQUENCE
 16 (ID_SPR,REPN_TYPE) INCLUDING NEW VALUES; Materialized view log created.

Elapsed: 00:00:00.47
SQL> DROP SNAPSHOT LOG ON SIM.PA_ORGN; Materialized view log dropped.

Elapsed: 00:00:00.47
SQL> CREATE MATERIALIZED VIEW LOG ON SIM.PA_ORGN   2 TABLESPACE RETEK_DATA
  3 PCTUSED 0
  4 PCTFREE 60
  5 INITRANS 2
  6 MAXTRANS 255
  7 STORAGE (

  8              INITIAL          1M
  9              MINEXTENTS       1
 10              MAXEXTENTS       2147483645
 11              PCTINCREASE      0
 12             )

 13 NOCACHE
 14 NOPARALLEL
 15 WITH ROWID, PRIMARY KEY, SEQUENCE
 16 (NM_ORGN) INCLUDING NEW VALUES; Materialized view log created.

Elapsed: 00:00:00.47
SQL> DROP SNAPSHOT LOG ON SIM.PA_SPR; Materialized view log dropped.

Elapsed: 00:00:00.31
SQL> CREATE MATERIALIZED VIEW LOG ON SIM.PA_SPR   2 TABLESPACE RETEK_DATA
  3 PCTUSED 0
  4 PCTFREE 60
  5 INITRANS 2
  6 MAXTRANS 255
  7 STORAGE (

  8              INITIAL          1M
  9              MINEXTENTS       1
 10              MAXEXTENTS       2147483645
 11              PCTINCREASE      0
 12             )

 13 NOCACHE
 14 NOPARALLEL
 15 WITH ROWID, PRIMARY KEY, SEQUENCE
 16 (NM_SPR) INCLUDING NEW VALUES; Materialized view log created.

Elapsed: 00:00:00.47
SQL> DROP SNAPSHOT LOG ON SIM.PA_STR_RTL; Materialized view log dropped.

Elapsed: 00:00:00.63
SQL> CREATE MATERIALIZED VIEW LOG ON SIM.PA_STR_RTL   2 TABLESPACE RETEK_DATA
  3 PCTUSED 0
  4 PCTFREE 60
  5 INITRANS 2
  6 MAXTRANS 255
  7 STORAGE (

  8              INITIAL          1M
  9              MINEXTENTS       1
 10              MAXEXTENTS       2147483645
 11              PCTINCREASE      0
 12             )

 13 NOCACHE
 14 NOPARALLEL
 15 WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES; Materialized view log created.

Elapsed: 00:00:00.47
SQL> DROP SNAPSHOT LOG ON SIM.RK_ITEM_SUPPLIER; Materialized view log dropped.

Elapsed: 00:00:00.31
SQL> CREATE MATERIALIZED VIEW LOG ON SIM.RK_ITEM_SUPPLIER   2 TABLESPACE RETEK_DATA
  3 PCTUSED 0
  4 PCTFREE 60
  5 INITRANS 2
  6 MAXTRANS 255
  7 STORAGE (

  8              INITIAL          1M
  9              MINEXTENTS       1
 10              MAXEXTENTS       2147483645
 11              PCTINCREASE      0
 12             )

 13 NOCACHE
 14 NOPARALLEL
 15 WITH ROWID, PRIMARY KEY, SEQUENCE
 16 (VPN) INCLUDING NEW VALUES; Materialized view log created.

Elapsed: 00:00:00.47
SQL> DROP SNAPSHOT LOG ON SIM.RK_STORE_ITEM_SOH; Materialized view log dropped.

Elapsed: 00:00:00.47
SQL> CREATE MATERIALIZED VIEW LOG ON SIM.RK_STORE_ITEM_SOH   2 TABLESPACE RETEK_DATA
  3 PCTUSED 0
  4 PCTFREE 60
  5 INITRANS 2
  6 MAXTRANS 255
  7 STORAGE (

  8              INITIAL          1M
  9              MINEXTENTS       1
 10              MAXEXTENTS       2147483645
 11              PCTINCREASE      0
 12             )

 13 NOCACHE
 14 NOPARALLEL
 15 WITH ROWID, PRIMARY KEY, SEQUENCE
 16 (TOTAL_QUANTITY) INCLUDING NEW VALUES; Materialized view log created.

Elapsed: 00:00:00.47

• then I tried to issue thisSQL> alter materialized view refresh on commit;

alter materialized view refresh on commit

                                *

ERROR at line 1:
ORA-00933: SQL command not properly ended

• so I issued from the base table owner ( sim ) this: SQL> grant alter any materialized view to dis_eul;

Grant succeeded.

Elapsed: 00:00:00.15

• but that did not change anything, I know it has to do with on commit because of this SQL> create materialized view dis_eul.dis_mv 2 PARTITION BY HASH(store_id) 3 PARTITIONS 8 4 STORE IN (DISCOVERER_DATA, DISCOVERER_DATA, DISCOVERER_DATA, DISCOVERER_DATA, DISCOVERER_DATA, DISCOVERER_DATA, DIS TA) 5 NOCACHE 6 NOPARALLEL 7 build immediate 8 refresh on commit 9 --with rowid 10 enable query rewrite 11 as 12 SELECT /*+ index(itmloc pk_as_itm_rtl_str) index(soh pk_rk_store_item_soh) index(ris pk_rk_item_supplier) index 13 psr.id_str_rt store_id, 14 po.nm_orgn store_name, 15 soh.id_itm item_id, 16 SUBSTR(itm.id_strc_mr,1,INSTR(itm.id_strc_mr,':')-1) Dept, 17 soh.id_itm ||'-'|| itm.de_itm id_desc, 18 itm.de_itm item_description, 19 itm.lu_uom_dft default_uom, 20 itmloc.id_spr primary_supplier, 21 ps.nm_spr vendor_name, 22 itmloc.id_spr ||'-'|| ps.nm_spr VDesc, 23 ris.vpn vendor_style, 24 itmloc.repn_type repn_type, 25 soh.total_quantity soh, soh.in_transit_quantity in_transit_quantity, 26 soh.adjust_unavail_qty unavailable_quantity, 27 CASE WHEN soh.total_quantity = 0 THEN 'ZERO' 28 WHEN soh.total_quantity < 0 THEN 'NEGATIVE' END status 29 FROM sim.pa_str_rtl psr, 30 sim.as_itm_rtl_str itmloc, 31 sim.as_itm itm, 32 sim.rk_item_supplier ris, 33 sim.pa_spr ps, 34 sim.rk_store_item_soh soh, 35 sim.pa_orgn po 36 WHERE itmloc.id_str_rt = psr.id_str_rt 37 AND itmloc.id_itm = itm.id_itm 38 AND itmloc.id_itm = ris.id_itm 39 AND itmloc.id_spr = ris.id_spr 40 AND ris.id_spr = ps.id_spr 41 AND soh.id_str_rt = itmloc.id_str_rt 42 AND soh.id_str_rt IN (
  '527','10','227','77','55','468','393')
  43 AND psr.id_prty = po.id_prty_orgn 44 AND soh.id_itm = itmloc.id_itm; AND soh.id_itm = itmloc.id_itm * ERROR at line 44: ORA-01031: insufficient privileges

Is there a grant to give to a user to view/read a materialized view log?

I saw a thread on asktom where he told a user that he may not have permissions on the base tables and or logs.

thanks for your help Received on Tue Jul 25 2006 - 15:48:36 CDT