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

[Stephen.Lee]

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----- From: Siddharth Haldankar [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 2:59 AM To: Multiple recipients of list ORACLE-L Subject: Refresh option for Materialized view , want to use it during 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 [EMAIL PROTECTED]      mcs,      [EMAIL PROTECTED]         mc,      [EMAIL PROTECTED]    mic,      [EMAIL PROTECTED]       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 [EMAIL PROTECTED] [EMAIL PROTECTED]