need to create a script to generate 300 materialized view tables

[franktfan]

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

[gandolf989]

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?

[franktfan]

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,

[sambavan]

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.