Home » SQL & PL/SQL » SQL & PL/SQL » How to refresh Materialized view in oracle version 9.2.0.5 64 bit
How to refresh Materialized view in oracle version 9.2.0.5 64 bit [message #243328] Wed, 06 June 2007 23:05 Go to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Dear Sir,

I have create a materialized view, i want to make them refresh weekly how can i do that in oracle version 9.2.0.5 64 bit.
below is my existing mview code pls. help me to make it refresh weekly. If their is any changes need to be done pls. help.
-------------------------------
DROP MATERIALIZED VIEW EPO_PRICE_NEW_V1;

CREATE MATERIALIZED VIEW EPO_PRICE_NEW_V1
TABLESPACE APPS_TS_TX_DATA
NOCACHE
LOGGING
NOPARALLEL
REFRESH FORCE ON DEMAND
AS
SELECT
pvs.org_id org_id
, mp.organization_id organization_id
, msi.inventory_item_id inventory_item_id
, pha.po_header_id po_header_id
, pla.po_line_id po_line_id
, pla.line_type_id line_type_id
, pla.category_id category_id
, pasl.vendor_id vendor_id
, pvs.vendor_site_id vendor_site_id
, pha.agent_id agent_id -- ID
, pha.ship_to_location_id ship_to_location_id
, pha.bill_to_location_id bill_to_location_id
, pasl.using_organization_id using_organization_id
, pasl.owning_organization_id owning_organization_id
, pasl.asl_status_id asl_status_id
, pasl.asl_id asl_id
, msi.segment1 part_no -- Part No
, msi.item_type item_type -- Item Type
-- , msi.attribute1 item_spec --
, msi.global_attribute6 item_spec -- 2005/04/09
, msi.description description -- Desc
, pha.start_date start_date -- ( Effective_date )
, msi.primary_uom_code primary_uom_code -- UOM Code -- 2003/05/02
, msi.primary_unit_of_measure primary_unit_of_measure -- UOM
, pvs.vendor_site_code vendor_site_code -- NO
, mp.organization_code organization_code
, hl.location_code location_code
, hl.description location_desc
, pha.attribute1 l_code -- Market
, pad.sequence_num sequence_num
, pha.currency_code currency_code --
, pha.rate currency_rate -- Add rate, 2005/06/14
, pla.unit_price unit_price --
, pla.market_price landed_cost -- 2005/04/09
, pla.attribute1 base_currency_code1 --
, pla.attribute2 base_price1 --
, pla.attribute3 base_currency_code2 -- Code2
, pla.attribute4 base_price2 --
, pla.attribute5 efactor -- 2005/07/25
, pla.attribute6 amort_cost -- 2005/04/30
, pha.fob_lookup_code fob_lookup_code --
, pltt.line_type line_type -- LINE TYPE
, pltb.outside_operation_flag outside_operation_flag --
, emp.full_name buyer_name --
, pla.last_update_date last_update_date --
, pla.last_updated_by last_updated_by --
, pha.segment1 bpa_no -- BPA NO
, pla.line_num line_num -- LINE NO
, pv.vendor_name vendor_name --
, pv.vendor_type_lookup_code vendor_type_code -- Venodr Type (2003/04/17 )
, pv.minority_group_lookup_code minority_group_lookup_code-- 2005/04/09
, nvl(pasl.disable_flag, 'N') disable_flag --
, pla.attribute10 reason -- att13 -> att10
, pla.attribute11 rnd_value -- CI value
, pla.attribute12 sourcing_value -- CI value
, pla.attribute13 material_value -- CI value
, pla.attribute14 glocal_value -- CI value
, pla.attribute15 others_value -- CI value
, pla.creation_date creation_date --
, pla.created_by created_by --
, pha.approved_date approved_date --
, pla.expiration_date end_date --
, pha.authorization_status authorization_status
, fu.user_name created_by_user
, NVL(pll.price_override, pla.unit_price) price_break
, NVL(pll.price_discount,0) discount
, pv.attribute2 ems_vendor_flag -- 2005/06/01
, NVL(pv.attribute5, pvs.attribute5 ) puocs_vendor_flag -- 2005/07/28
, pv.segment1 vendor_code -- GCMS Code
, pll.line_location_id line_location_id -- 2005/07/14
, pv.attribute13 affiliate_code -- 2005/08/02

FROM
epo_people_v emp
, po_line_types_b pltb
, po_line_types_tl pltt
, po_vendors pv
, po_lines_all pla
, po_headers_all pha
, po_vendor_sites_all pvs
, mtl_system_items_b msi
, po_asl_documents pad
, po_approved_supplier_list pasl
, hr_organization_information hoi
, hr_locations_all hl -- 2005/04/09
, mtl_parameters mp
, po_line_locations_all pll
, fnd_user fu
WHERE
hoi.organization_id = mp.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND pvs.org_id = pha.org_id -- 2005/07/14
AND msi.organization_id = mp.organization_id
AND pasl.item_id = msi.inventory_item_id
AND pasl.vendor_site_id = pvs.vendor_site_id
AND pasl.vendor_id = pvs.vendor_id
AND pasl.vendor_id = pv.vendor_id
AND pasl.asl_id = epo_asl_common_pkg.get_asl_id(pasl.item_id,
pasl.vendor_id,
pasl.vendor_site_id,
mp.organization_id)
AND pad.asl_id = pasl.asl_id
AND pad.using_organization_id = (SELECT max(paa2.using_organization_id)
FROM po_asl_attributes paa2
WHERE paa2.asl_id = pasl.asl_id
AND paa2.using_organization_id in (-1, mp.organization_id))
AND pha.po_header_id = pad.document_header_id
AND pha.type_lookup_code = 'BLANKET'
AND pla.po_line_id = pad.document_line_id
AND pha.ship_to_location_id = hl.location_id -- 2005/04/09
AND hl.inventory_organization_id = hoi.organization_id -- 2005/06/01
AND pla.po_line_id = pll.po_line_id(+)
AND pll.shipment_type(+) ='PRICE BREAK'
AND fu.user_id = pla.created_by
AND emp.person_id(+) = pha.agent_id
AND pltb.line_type_id = pla.line_type_id
AND pltt.line_type_id = pltb.line_type_id
AND pha.approved_flag = 'Y'
AND nvl(pha.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
AND nvl(pla.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
AND nvl(pha.cancel_flag,'N') = 'N'
AND nvl(pha.user_hold_flag, 'N') = 'N'
AND nvl(pha.frozen_flag,'N') = 'N'
AND nvl(pla.cancel_flag,'N') = 'N'
-- and msi.segment1 = '3551FW1120C'
-- and pha.attribute1 = 2
-- and pla.expiration_date is null;;

CREATE INDEX EPO_PRICE_NEW_V1 ON EPO_PRICE_NEW_V1
(ORGANIZATION_ID, PART_NO)
LOGGING
TABLESPACE APPS_TS_TX_DATA
NOPARALLEL;
---------------------

Thanks,
Saurabh
Re: How to refresh Materialized view in oracle version 9.2.0.5 64 bit [message #243332 is a reply to message #243328] Wed, 06 June 2007 23:24 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Example>

SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 7 =====>7days
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

Materialized view created.
http://www.dbasupport.com/oracle/ora9i/mat_views2.shtml

[Updated on: Wed, 06 June 2007 23:37]

Report message to a moderator

Re: How to refresh Materialized view in oracle version 9.2.0.5 64 bit [message #243350 is a reply to message #243332] Thu, 07 June 2007 00:55 Go to previous messageGo to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Thanks for your prompt reply. As per your instruction i had tried to make a mview but it give some error print screen is attached.

Thanks,
Saurabh
./fa/2570/0/
  • Attachment: mv.jpg
    (Size: 96.15KB, Downloaded 680 times)
Re: How to refresh Materialized view in oracle version 9.2.0.5 64 bit [message #243519 is a reply to message #243350] Thu, 07 June 2007 10:48 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 7 =====>7days



Try it with

REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 7


Previous Topic: How to get back deleted procedure from package
Next Topic: Many sql statements are not working in SQL Server 2000.Plz Help?
Goto Forum:
  


Current Time: Sun Dec 11 08:26:30 CST 2016

Total time taken to generate the page: 0.19293 seconds