1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

materialized view on prebuilt table - refresh

Discussion in 'General' started by ecivgamer, Nov 25, 2014.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,


    Please help me to learn how to work with materialized view on prebuilt table.


    I created prebuilt table:

    CREATE TABLE "RATER"."TMP003"
    (
    "ITEM_ID" NUMBER(10,0)
    ) ;



    I created materialized view on prebuilt table:

    CREATE MATERIALIZED VIEW "RATER"."TMP003" ("ITEM_ID")
    ON PREBUILT TABLE
    AS SELECT "COST_DETAIL_ITEMS"."ITEM_ID" "ITEM_ID" FROM "RATER"."COST_DETAIL_ITEMS"@EFLP2.EFLATBED.COM "COST_DETAIL_ITEMS";



    I checked quantity of rows at original source object:

    SELECT count(1) as cnt FROM "RATER"."COST_DETAIL_ITEMS"@EFLP2.EFLATBED.COM "COST_DETAIL_ITEMS";

    cnt
    -----
    61047571



    I checked quantity of rows at my result object:

    select count(1) cnt from "RATER"."TMP003"

    CNT
    ----------
    0



    I tried to refresh my result object:

    exec DBMS_MVIEW.REFRESH('RATER.TMP003');

    anonymous block completed



    I checked quantity of rows at my result object after try to refresh:

    select count(1) cnt from "RATER"."TMP003"

    CNT
    ----------
    0



    Please help me to understand how to refresh my result object properly.

    Thanks.
     
  2. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Actually, Pravin Takpire from oracle.com community said "You will have to first populate the base pre-built table using create table as select from" and it works fine.

    Thanks.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation