need to create a script to generate 300 materialized view tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: need to create a script to generate 300 materialized view tables

  1. #1
    Join Date
    Sep 2000
    Posts
    2

    Smile need to create a script to generate 300 materialized view tables

    Hi, guys,
    I need a script to generate around 300 metrialized views. Something like this for one table GN_ACCESS_CODES:

    create snapshot GN_ACCESS_CODES on prebuilt table refresh fast for update as select
    ID,
    ORDER_EVENT_ID,
    ACCESS_CODE,
    AVAILABLE_COUNT,
    ACTIVE,
    EXPIRATION
    from GN_ACCESS_CODES@datahelios.esri.com;

    In this script, I got the table name GN_ACCESS_CODES from dba_repobject table and column_name (ID, ORDER_EVENT_ID,...) from dba_tab_columns table then concatenated together in order to create above script.
    Can any one show me the example to do it? I have around 300 Mviews were replicated in system.

    Thanks,

    Frank

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Have you thought about what the overhead will be on your database to maintain 300 materialized views? Are you going to refresh the views on commit, or at a set time every day? Wouldn't it be better to create a few materialized views that represent a large number of common queries and then see if you can get Oracle to use them with query rewrite?
    this space intentionally left blank

  3. #3
    Join Date
    Sep 2000
    Posts
    2

    Smile replication materialized views

    Yes, I wish I can just create few Mviews instead of 300 but this is all been created in last three years. I just need some do loop example or how to setup all at one time.

    Thanks again,

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    My approach would be as follows:

    1. Create a stage table containg the information like, table, mview group, schema owner, tablespaces it is associated to, and any other criterias that you would want to use during the creation of mviews.

    2. Insert all the mview information into this criteria table.

    3. Now create a procedure under the mview admin schema, and then let your script loop through the criteria table and create the mviews. Where you can check and skip the mviews that already exist in the environment.

    This way you can maintain a repository of mviews and make use of it to create the mviews in any environments as long as you keep the criteria table upto date.
    Thanx
    Sam



    Life is a journey, not a destination!


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