Home » Server Options » Replication » Materialized View
| Materialized View [message #29584] |
Fri, 12 March 2004 04:57  |
luca martino
Messages: 2
Registered: March 2004
|
Junior Member |
|
|
A materialized view declared as below does make any locks on the table of from clause?
CREATE MATERIALIZED VIEW SLAMUSER04ADM.GUI_DETT_RICH_FMOBILE_ PCTFREE 40 PCTUSED 60 INITRANS 4 MAXTRANS 255
STORAGE(
INITIAL 64 K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
TABLESPACE SLAMTBP
LOGGING
NOCACHE
NOPARALLEL
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT
)
REFRESH COMPLETE
WITH ROWID
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
SELECT CLI.NOME_CLIENTE_SLAM AS NOME_CLIENTE ,
CLI.ID_CLIENTE_SLAM AS CODICE_CLIENTE ,
CLI.PARTITA_IVA_SLAM AS PIVA ,
CLI.COD_FISCALE_SLAM AS CODICE_FISCALE ,
CLI.SEG_CLIENTE AS SEGMENTO ,
CTR.ID_CONTRATTO_SLAM AS CODICE_CONTRATTO ,
GS.GRUPPO_SERVIZIO AS SERVIZIO ,
OFT.DESCRIPTION AS OFFERTA ,
TO_CHAR(CTR.DATA_FIRMA_CONTRATTO_SLAM,'DD/MM/YYYY') AS DATA_FIRMA_CTR ,
TO_CHAR(CTR.DATA_ATT_CONTR_SLAM,'DD/MM/YYYY') AS DATA_ATTIVAZ_CTR ,
RI.ID_INTERAZIONE_SLAM AS ID_INTERAZIONE,
RI.ID_CASE_SLAM AS ID_CASE,
CET.CONTR_EVENT_TYPE_DESC AS EVENTO_CONTRATTUALE ,
PRJ.PROJECT_TYPE_DESC AS TIPO_PROGETTO ,
DECODE(NVL(RI.FLAG_ADESIONE_LEONARDO,0),1,'SI','NO') AS FLAG_LEONARDO,
DECODE(NVL(RI.FLAG_CLIENTE_HOLDING_SLAM,0),1,'SI','NO') AS FLAG_HOLDING,
DECODE(NVL(RI.FLAG_GREENWICH_SLAM,0),1,'SI','NO') AS FLAG_GREENWICH,
TO_CHAR(RI.DATA_FIRMA ,'DD/MM/YYYY') AS DATA_FIRMA ,
TO_CHAR(RI.DATA_INIZIO_INTERAZIONE_SLAM,'DD/MM/YYYY') AS DATA_CREAZIONE_INTERAZ,
WFS.WF_STATUS_DESC AS STATO ,
(RI.CASE_CONCLUSION_SLAM || '-' || RI.CASE_SUBCONCLUSION_SLAM) AS ESITO,
IST.INDICATOR_STATISTIC_VALUE AS KPI,
T.THRESHOLD_MAX AS KPO,
TOT_SIM_STD_SLAM AS TOT_SIM, TOT_SIM_MNP_SLAM AS TOT_SIM_MNP,
TOT_SIM_WPN_SLAM AS TOT_SIM_WPN, TOT_SIM_MNP_WPN_SLAM AS TOT_SIM_WPN_MNP,
TOT_SIM_GREENWICH_MNP_SLAM AS TOT_SIM_GRE_MNP, TOT_SIM_GREENWICH_SLAM AS TOT_SIM_GRE,
TOT_HANDSET_NOLEGGIO AS TOT_HDS_NOL,TOT_HANDSET_VENDITA AS TOT_HDS_VEN,
FLAG_WPN_SLAM AS TOT_WPN
FROM REPORT_CLIENTE CLI ,REPORT_CONTRATTO CTR, REPORT_INTERAZIONE_FMOBILE RI ,
OFFER_TYPE OFT , PROJECT_TYPE PRJ ,CONTR_EVENT_TYPE CET ,GRUPPO_SERVIZIO GS,
WF_STATUS WFS , WF_HEADER WFH ,THRESHOLD T ,INDICATOR_STATISTIC IST
WHERE CTR.ID_CLIENTE_SLAM = CLI.ID_CLIENTE_SLAM
AND RI.ID_CLIENTE_SLAM = CLI.ID_CLIENTE_SLAM
AND RI.ID_CONTRATTO_SLAM= CTR.ID_CONTRATTO_SLAM
AND ( WFS.WF_STATUS_ID <> '13' OR CTR.DATA_ATT_CONTR_SLAM IS NOT NULL )
AND OFT.OFFER_ID = CTR.OFFER_ID_SLAM
AND RI.OBJECT_HEADER_ID = WFH.OBJECT_HEADER_ID
AND WFH.WF_STATUS_ID = WFS.WF_STATUS_ID
AND RI.CONTR_EVENT_TYPE_ID = CET.CONTR_EVENT_TYPE_ID
AND IST.BASED_OBJECT_HEADER_ID = RI.OBJECT_HEADER_ID
AND IST.INDICATOR_ID IN (SELECT FE.INDICATOR_ID FROM FE_INDICATOR_TYPES FE
WHERE FE.FE_E2E_FLAG=1 AND FE.INDICATOR_CLASS_TYPE_ID=8)
AND IST.THRESHOLD_ID = T.THRESHOLD_ID
AND PRJ.PROJECT_TYPE_ID = RI.TIPOLOGIA_PROGETTO_SLAM
AND GS.SPECIFICA = RI.SPECIFICA_SLAM
|
|
|
|
|
|
| Re: Materialized View [message #29617 is a reply to message #29597] |
Mon, 15 March 2004 02:59  |
luca martino
Messages: 2
Registered: March 2004
|
Junior Member |
|
|
Thanks.I'm agree with you, as oracle's manulas says the statment of my first mail should create a lock only if I declare "for update".
I'm using Oracle 8.1.7
But this snapshot creates an inespected lock.
I invoke refresh method on that snapshot during an execution of a java-based bach. The bach makes insert or update on a table whose value are taken by snapshot.
I'm sure that bach doesn't create any write-lock (transactions are managed by Weblogic application server) or read-lock (never I declare for update select).
I see, on Oracle system user, views v$lock and v$locked_object here appear Lock_type as 'JI' and lock_Mode as 'Exclusive'.
Snapshot's refresh and bach's execution never end.
Are there any constants to set invoking of refresh method?
Thank You.
Luca
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 14:29:58 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
