limitations of "on prebuilt table" for creating snapshots
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: limitations of "on prebuilt table" for creating snapshots

  1. #1
    Join Date
    Apr 2002
    Location
    Chennai
    Posts
    27
    Hello all,

    is there any limitation on using "on prebuilt table" option while creating the materialized view. If I create the materialized view with out this, it is fetching the data, if I use this it is not fetching the data. Can some body explain this.

    Thanks in advance
    --Srinivas

  2. #2
    Join Date
    Apr 2002
    Location
    Chennai
    Posts
    27
    Just posting, to keep it in top

  3. #3
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    dear sir ,

    Limitations I am not knowing them.

    But prebuilt option saved me once when I renamed my source table and then drop that table itself and Materialized view was in hanging stage.

    Thanks to Julian for it !!!

    bye
    viraj

    A Wise Man Knows How much he doesn't know !!!

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by virajvk

    Thanks to Julian for it !!!

    For what? :-) I don't get it...

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Jan 2002
    Location
    India
    Posts
    105

    Arrow

    dear Julian sir,

    Long back i had problem droping a oracle user becaz a single snapshot was not getting dropped.
    This happened becaz I renamed the table on which snapshot was created and then I dropped the table.
    Then snapshot dropping was not taking place saying object does not exist.

    My senior dba asked question related to this and u suggested create snapshot with pre buit option whch we implemented
    and was successful

    so Thanks to Julian


    bye
    viraj


    A Wise Man Knows How much he doesn't know !!!

  6. #6
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    About the original question :

    Do not forget to fill the table with data before you create the MV.

    Example

    create materialized view T_MV
    ...
    as select id,sum(amount) as s_a
    from sales
    group by id

    equals

    create table T_MV
    as select is,sum(amount) as s_a
    from sales
    group by id
    /
    create materialized view T_MV on prebuilt table
    ...
    as select id,sum(amount) as s_a
    from sales
    group by id
    /

    Limitation :
    The table you use must have the same name as the MV.

    Wrong example

    create table T_MV ( id number, s_a number )
    /
    create materialized view T_MV on prebuilt table
    ...
    as select id,sum(amount) as s_a
    from sales
    group by id
    /

    remark

    If the materialized view is dropped, the preexisting table reverts to its identity as a table.



    Hope this helps
    Gert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width