Home » SQL & PL/SQL » SQL & PL/SQL » how to refresh a materialzed view which refreshes on demand
how to refresh a materialzed view which refreshes on demand [message #214367] Tue, 16 January 2007 01:16 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Here is the materialzed view

CREATE MATERIALIZED VIEW "MDS"."TGT_ITEM_ORCL"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "MST_ITEM"."SCENARIO_ID" "SCENARIO_ID","MST_ITEM"."EMAIL_DIST_ONLY" "EMAIL_DIST_ONLY","MST_ITEM"."ITEM_ID" "ITEM_ID","MST_ITEM"."SKU_TYPE" "SKU_TYPE","MST_ITEM"."DESCRIPTION" "DESCRIPTION","MST_ITEM"."ITEM_STATUS" "ITEM_STATUS","MST_ITEM"."PRODUCT_GROUP" "PRODUCT_GROUP","MST_ITEM"."PRODUCT_CATEGORY" "PRODUCT_CATEGORY","MST_ITEM"."SALABLE" "SALABLE","MST_ITEM"."UOI" "UOI","MST_ITEM"."UOM" "UOM","MST_ITEM"."DSI_FREIGHT_CLASS" "DSI_FREIGHT_CLASS","MST_ITEM"."COPS_CLASS_CODE" "COPS_CLASS_CODE","MST_ITEM"."PRODUCT_TYPE" "PRODUCT_TYPE","MST_ITEM"."SERIALIZATION_IND" "SERIALIZATION_IND","MST_ITEM"."RETURNABLE_TO_WHSE" "RETURNABLE_TO_WHSE","MST_ITEM"."EXACT_WEIGHT" "EXACT_WEIGHT","MST_ITEM"."LENGTH" "LENGTH","MST_ITEM"."WIDTH" "WIDTH","MST_ITEM"."HEIGHT" "HEIGHT","MST_ITEM"."VOLUME" "VOLUME","MST_ITEM"."CURR_COST_PER_UNIT" "CURR_COST_PER_UNIT","MST_ITEM"."RETAIL_PRICE" "RETAIL_PRICE","MST_ITEM"."CUSTOMS_REP_PRICE" "CUSTOMS_REP_PRICE","MST_ITEM"."WHOLESALE_PRICE" "WHOLESALE_PRICE","MST_ITEM"."GL_INV_EXP" "GL_INV_EXP","MST_ITEM"."GL_SALES_INC" "GL_SALES_INC","MST_ITEM"."UPC" "UPC","MST_ITEM"."UPC_REQD" "UPC_REQD","MST_ITEM"."I2_APPROVAL_REQD" "I2_APPROVAL_REQD","MST_ITEM"."IDEN_PROD_TYPE_IND" "IDEN_PROD_TYPE_IND","MST_ITEM"."PSFT_VEND_NUM" "PSFT_VEND_NUM","MST_ITEM"."VENDOR_TARGET_PRICE" "VENDOR_TARGET_PRICE","MST_ITEM"."CYCLE_COUNT_CODE" "CYCLE_COUNT_CODE","MST_ITEM"."MAKE_BUY_CODE" "MAKE_BUY_CODE","MST_ITEM"."SCHEDULE_TYPE" "SCHEDULE_TYPE","MST_ITEM"."SKU_CASE_QTY" "SKU_CASE_QTY","MST_ITEM"."SKU_PACK_FACOR" "SKU_PACK_FACOR","MST_ITEM"."E911_GPS_AFLT" "E911_GPS_AFLT","MST_ITEM"."DEPARTMENT" "DEPARTMENT","MST_ITEM"."CATEGORY" "CATEGORY","MST_ITEM"."CLASS" "CLASS","MST_ITEM"."REVENUE_ACC_NUM" "REVENUE_ACC_NUM","MST_ITEM"."INV_ASSET_ACC_NUM" "INV_ASSET_ACC_NUM","MST_ITEM"."COGS_ACC_NUMBER" "COGS_ACC_NUMBER","MST_ITEM"."PUR_LIAB_ACC_NUM" "PUR_LIAB_ACC_NUM","MST_ITEM"."CYCLE_COUNT_POLICY" "CYCLE_COUNT_POLICY","MST_ITEM"."WNLP_COMPLIANT" "WNLP_COMPLIANT","MST_ITEM"."VENDOR_CONTACT_NAME" "VENDOR_CONTACT_NAME","MST_ITEM"."SKU_EFFECTIVE_DATE" "SKU_EFFECTIVE_DATE","MST_ITEM"."INT_SPRINT_CONTACT" "INT_SPRINT_CONTACT","MST_ITEM"."DATE_APPROVED" "DATE_APPROVED","MST_ITEM"."COMMENTS" "COMMENTS","MST_ITEM"."FIN_AUTHORIZATION" "FIN_AUTHORIZATION","MST_ITEM"."LOGISTICS_AUTH" "LOGISTICS_AUTH","MST_ITEM"."PSFT_ITEM_ID_REQD" "PSFT_ITEM_ID_REQD","MST_ITEM"."PSFT_ITEM_ID" "PSFT_ITEM_ID","MST_ITEM"."BASE_SKU" "BASE_SKU","MST_ITEM"."RETURN_SKU" "RETURN_SKU","MST_ITEM"."WHSE_USE_ONLY" "WHSE_USE_ONLY","MST_ITEM"."TRANSACTION_TYPE" "TRANSACTION_TYPE","MST_ITEM"."END_OF_LIFE_FLAG" "END_OF_LIFE_FLAG","MST_ITEM"."PLANNED_BY_I2" "PLANNED_BY_I2","MST_ITEM"."INTL_SHIPMENT" "INTL_SHIPMENT","MST_ITEM"."COMMODITY_CODE" "COMMODITY_CODE","MST_ITEM"."EXCOMM_DESCR1" "EXCOMM_DESCR1","MST_ITEM"."EXCOMM_DESCR2" "EXCOMM_DESCR2","MST_ITEM"."EXCOMM_DESCR3" "EXCOMM_DESCR3","MST_ITEM"."EXCOMM_UOM" "EXCOMM_UOM","MST_ITEM"."EXPORT_LICENSE" "EXPORT_LICENSE","MST_ITEM"."LIC_EXPIRATION_DATE" "LIC_EXPIRATION_DATE","MST_ITEM"."LIC_EXCEPTION_SYM" "LIC_EXCEPTION_SYM","MST_ITEM"."ECCN" "ECCN","MST_ITEM"."HARMONIZED_CODE" "HARMONIZED_CODE","MST_ITEM"."SCHEDULE_B_CODE" "SCHEDULE_B_CODE","MST_ITEM"."SCHEDULE_B_UOM1" "SCHEDULE_B_UOM1","MST_ITEM"."CONVERSION_FACTOR1" "CONVERSION_FACTOR1","MST_ITEM"."SCHEDULE_B_UOM2" "SCHEDULE_B_UOM2","MST_ITEM"."CONVERSION_FACTOR2" "CONVERSION_FACTOR2","MST_ITEM"."SCHEDULE_B_UOM3" "SCHEDULE_B_UOM3","MST_ITEM"."CONVERSION_FACTOR3" "CONVERSION_FACTOR3","MST_ITEM"."COUNTRY_OF_ORIGIN" "COUNTRY_OF_ORIGIN","MST_ITEM"."COUNTRY_OF_MANUFACTURE" "COUNTRY_OF_MANUFACTURE","MST_ITEM"."FOREIGN_DOMESTIC_INDICATOR" "FOREIGN_DOMESTIC_INDICATOR","MST_ITEM"."NAFTA_PRODUCER" "NAFTA_PRODUCER","MST_ITEM"."NAFTA_PREF_CRITERIA" "NAFTA_PREF_CRITERIA","MST_ITEM"."NAFTA_NET_COST_CD" "NAFTA_NET_COST_CD","MST_ITEM"."NAFTA_NET_COST_FROM_DATE" "NAFTA_NET_COST_FROM_DATE","MST_ITEM"."NAFTA_NET_COST_TO_DATE" "NAFTA_NET_COST_TO_DATE","MST_ITEM"."EXCOMM_WEIGHT" "EXCOMM_WEIGHT","MST_ITEM"."EXCOMM_WEIGHT_UOM" "EXCOMM_WEIGHT_UOM","MST_ITEM"."UNIT_PRICE" "UNIT_PRICE","MST_ITEM"."DANGEROUS_GOODS_CODE" "DANGEROUS_GOODS_CODE","MST_ITEM"."IS_HAZARDOUS" "IS_HAZARDOUS","MST_ITEM"."HAZARDOUS_QTY_CODE" "HAZARDOUS_QTY_CODE","MST_ITEM"."HAZARDOUS_DESCRIPTION" "HAZARDOUS_DESCRIPTION","MST_ITEM"."START_DATE_ACTIVE" "START_DATE_ACTIVE","MST_ITEM"."ITEM_TYPE" "ITEM_TYPE","MST_ITEM"."SHIPPABLE" "SHIPPABLE","MST_ITEM"."INT_ORDER_ENABLED_FLAG" "INT_ORDER_ENABLED_FLAG","MST_ITEM"."BOM_ENABLED" "BOM_ENABLED","MST_ITEM"."WIP_ENABLED" "WIP_ENABLED","MST_ITEM"."INSPECTION_REQUIRED" "INSPECTION_REQUIRED","MST_ITEM"."SERIAL_CONTROLLED" "SERIAL_CONTROLLED","MST_ITEM"."LIST_PRICE" "LIST_PRICE","MST_ITEM"."PLANNING_METHOD" "PLANNING_METHOD","MST_ITEM"."PACKAGE_BARCODE" "PACKAGE_BARCODE","MST_ITEM"."INNER_PACK_QTY" "INNER_PACK_QTY","MST_ITEM"."TIER_QUANTITY" "TIER_QUANTITY","MST_ITEM"."PALLET_QUANTITY" "PALLET_QUANTITY","MST_ITEM"."TARIFF_CLASS" "TARIFF_CLASS","MST_ITEM"."SKU_ACTIVE_IN_SDF" "SKU_ACTIVE_IN_SDF","MST_ITEM"."CIM_INV_METHOD" "CIM_INV_METHOD","MST_ITEM"."CIM_ALLOCATE_MULTIPLE_LOTS" "CIM_ALLOCATE_MULTIPLE_LOTS","MST_ITEM"."CIM_ALLOCATE_PARTIAL_LOTS" "CIM_ALLOCATE_PARTIAL_LOTS","MST_ITEM"."CIM_LOT_ASSIGN_METHOD" "CIM_LOT_ASSIGN_METHOD","MST_ITEM"."DIM_INV_METHOD" "DIM_INV_METHOD","MST_ITEM"."DIM_LOTSIZE_METHOD" "DIM_LOTSIZE_METHOD","MST_ITEM"."ENSEMBLE_VEND_CODE" "ENSEMBLE_VEND_CODE","MST_ITEM"."SHORT_DESCRIPTION" "SHORT_DESCRIPTION","MST_ITEM"."WARRANTY_PERIOD" "WARRANTY_PERIOD","MST_ITEM"."VAD_FLAG" "VAD_FLAG","MST_ITEM"."SHORT_NAME" "SHORT_NAME","MST_ITEM"."ORG_ID" "ORG_ID","MST_ITEM"."MANUAL_REPL" "MANUAL_REPL","MST_ITEM"."ROW_ID" "ROW_ID","MST_ITEM"."SET_ID" "SET_ID","MST_ITEM"."PURCH_LEAD_TIME" "PURCH_LEAD_TIME","MST_ITEM"."BACK_ORDER" "BACK_ORDER","MST_ITEM"."ENSEMBLE_WHSE_CODES" "ENSEMBLE_WHSE_CODES","MST_ITEM"."ENSEMBLE_SALES_CHANNELS" "ENSEMBLE_SALES_CHANNELS","MST_ITEM"."PRICING_EFF_DATE" "PRICING_EFF_DATE","MST_ITEM"."DIRECT_SHIP" "DIRECT_SHIP","MST_ITEM"."SYS_TARGET_ID" "SYS_TARGET_ID","MST_ITEM"."SYS_AUTH_ID" "SYS_AUTH_ID","MST_ITEM"."SYS_SOURCE" "SYS_SOURCE","MST_ITEM"."SYS_CREATED_BY" "SYS_CREATED_BY","MST_ITEM"."SYS_CREATION_DATE" "SYS_CREATION_DATE","MST_ITEM"."SYS_ENT_STATE" "SYS_ENT_STATE","MST_ITEM"."SYS_LAST_MODIFIED_BY" "SYS_LAST_MODIFIED_BY","MST_ITEM"."SYS_LAST_MODIFIED_DATE" "SYS_LAST_MODIFIED_DATE","MST_ITEM"."SYS_NC_TYPE" "SYS_NC_TYPE","MST_ITEM"."SYS_ERR_CODE" "SYS_ERR_CODE","MST_ITEM"."SYS_ERR_SVRTY" "SYS_ERR_SVRTY","MST_ITEM"."SYS_FILTER" "SYS_FILTER","MST_ITEM"."SYS_EXCEPTION_TYPE" "SYS_EXCEPTION_TYPE" FROM "MST_ITEM" "MST_ITEM";


How can it be refreshed

Its not getting refreshed by
exec dbms_snapshot.refresh_all;

Any help really appreciated

Thanks In advance
Re: how to refresh a materialzed view which refreshes on demand [message #214372 is a reply to message #214367] Tue, 16 January 2007 01:39 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
How about using DBMS_MVIEW.REFRESH_ALL_MVIEWS or DBMS_MVIEW.REFRESH package?
Re: how to refresh a materialzed view which refreshes on demand [message #214378 is a reply to message #214372] Tue, 16 January 2007 02:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@vikram1780: please use code tags. Your code wasn't readable.

CREATE MATERIALIZED VIEW mds.tgt_item_orcl
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE  (INITIAL 65536
          NEXT 1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645
          PCTINCREASE 0
          FREELISTS 1
          FREELIST GROUPS 1
          BUFFER_POOL DEFAULT
         )
  TABLESPACE users
  BUILD IMMEDIATE
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
    STORAGE(INITIAL 65536
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            PCTINCREASE 0
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT
           )
  TABLESPACE users
  REFRESH FORCE ON DEMAND
  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
  DISABLE QUERY REWRITE
  AS
  SELECT mst_item.scenario_id         scenario_id,
         mst_item.schedule_b_code     schedule_b_code,
         mst_item.schedule_b_uom1     schedule_b_uom1,
         mst_item.conversion_factor1  conversion_factor1,
         mst_item.schedule_b_uom2     schedule_b_uom2,
         mst_item.conversion_factor2  conversion_factor2,
         ....
         ....
         mst_item.item_type item_type,
         mst_item.sys_nc_type sys_nc_type,
         mst_item.sys_err_code sys_err_code,
         mst_item.sys_err_svrty sys_err_svrty,
         mst_item.sys_filter sys_filter,
         mst_item.sys_exception_type sys_exception_type
FROM     mst_item mst_item;


MHE

[Updated on: Tue, 16 January 2007 02:58]

Report message to a moderator

Re: how to refresh a materialzed view which refreshes on demand [message #214379 is a reply to message #214378] Tue, 16 January 2007 02:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maarten,

I am afraid code-tags won't help...
Remember to keep it all in CAPS, retain the double quotes, the enourmous (for the problem superfluous) list of selected columns etc.

[Updated on: Tue, 16 January 2007 03:00]

Report message to a moderator

Re: how to refresh a materialzed view which refreshes on demand [message #214381 is a reply to message #214379] Tue, 16 January 2007 03:08 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Do you have the privileges to refresh (ALTER ANY SNAPSHOT)? Do you receive an error message when you refresh? An Oracle version would be convenient too.

MHE
Previous Topic: two table creation
Next Topic: Compilation Error in PLSQL
Goto Forum:
  


Current Time: Fri Dec 09 08:00:44 CST 2016

Total time taken to generate the page: 0.08353 seconds