RE: Refresh option for Materialized view , want to use it during

Here's a weird idea to consider: You might replicate to a dummy table XYZ, then rename CT_PRODUCTIED_VW to CRAP; then rename XYZ to CT_PRODUCTIED_VW. Then rename CRAP to XYZ. If there are any dependent stored procedures, you will probably be required to recompile them. But, if the dependencies aren't too hairy, this could be put into a little script that executes in about two seconds.  

Then there's the idea of fiddling with partitions and swapping them in and out.

-----Original Message-----
Sent: Tuesday, October 21, 2003 2:59 AM
To: Multiple recipients of list ORACLE-L refresh

Hi Gurus,  

I have a materialized view, which is based on Oracle Apps tables and on remote database. The view refresh takes around ½ hour, during this time period I cannot see any records in the materialized view and therefore my application faces errors.

The following is the view definition  

CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) AS

SELECT

    msi.segment1                productid,           

    msi.description             description,

    msi.inventory_item_id       inventory_item_id,

    mc.segment1                 product_family,

    mc.segment2                 product_type

FROM MTL_CATEGORY_SETS_at_CME_ODSPROD      mcs,

     MTL_CATEGORIES_at_CME_ODSPROD         mc,

     MTL_ITEM_CATEGORIES_at_CME_ODSPROD    mic,

     MTL_SYSTEM_ITEMS_at_CME_ODSPROD       msi

where 1=1

and mc.structure_id = 50112

and mc.segment3 != 'SPARE'

and mc.global_name = 'US'

and mc.enabled_flag = 'Y'

and mcs.global_name = mc.global_name

and mcs.category_set_name = 'PROD GROUP'

and mic.category_set_id = mcs.category_set_id

and mic.category_id = mc.category_id

and mic.global_name = mc.global_name

and mic.organization_id = 1

and mic.inventory_item_id = msi.inventory_item_id

and msi.organization_id = mic.organization_id

and msi.global_name = mc.global_name

AND msi.auto_created_config_flag = 'N'

AND msi.item_type IN ('ATO MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST>$0','PTO MODEL','SPARE') and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
  Please note that the tables referenced are remote tables and Oracle Apps tables and not logging on it is possible.

Please suggest an appropriate refresh mechanism to see the records even during refresh period.  

Thanks in advance.  

With Warm Regards

Siddharth Haldankar

Zensar Technologies Ltd.

Cisco Systems Inc.

(Offshore Development Center)

# : 091 020 4128394

shaldank_at_cisco.com

s.haldankar_at_zensar.com  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <Stephen.Lee_at_DTAG.Com
  INET: Stephen.Lee_at_DTAG.Com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).