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

From: g3000 <carlton_gregory_at_yahoo.com>
Date: 25 Jul 2006 13:49:28 -0700
Message-ID: <1153860568.532109.8290@s13g2000cwa.googlegroups.com>

Ill add that this is the analysis of the materialized view SQL> @readcap.sql

CAPABILITY_NAME                P REL_TEXT
------------------------------ - ------------------------

MSGTXT

---

PCT                            N


REFRESH_COMPLETE               Y


REFRESH_FAST                   Y


REWRITE                        Y


PCT_TABLE                      N SIM.PA_S

relation is not a partitioned table

PCT_TABLE                      N SIM.AS_I

PCT not supported with this type of partitioning

PCT_TABLE                      N SIM.AS_I

CAPABILITY_NAME                P REL_TEXT
------------------------------ - ------------------------

MSGTXT

---

relation is not a partitioned table

PCT_TABLE                      N SIM.RK_I

relation is not a partitioned table

PCT_TABLE                      N SIM.PA_S

relation is not a partitioned table

PCT_TABLE                      N SIM.RK_S

PCT not supported with this type of partitioning

PCT_TABLE                      N SIM.PA_O

relation is not a partitioned table

REFRESH_FAST_AFTER_INSERT Y REFRESH_FAST_AFTER_ONETAB_DML Y

CAPABILITY_NAME                P REL_TEXT
------------------------------ - ------------------------

MSGTXT

---

REFRESH_FAST_AFTER_ANY_DML Y REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the mater

REWRITE_FULL_TEXT_MATCH Y REWRITE_PARTIAL_TEXT_MATCH Y

REWRITE_GENERAL                Y


REWRITE_PCT                    N

general rewrite is not possible and PCT is not possible on a

19 rows selected.

g3000 wrote:
> 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:49:28 CDT