script to rebuild all materialized views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: script to rebuild all materialized views

Hybrid View

  1. #1
    Join Date
    Jan 2003
    Location
    I am from Chile and I live in Santiago
    Posts
    2

    script to rebuild all materialized views

    Hi!

    Mi name is Juan.Please, I need a script for rebuild all materialized views.

    Sorry for my English.

    Thanks.

    Juan.

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Try this

    select 'create snapshot "&1"."'||a.name||'" tablespace '||b.tablespace_name||
    ' storage(initial '||b.INITIAL_EXTENT||
    ' maxextents '||b.MAX_EXTENTS||
    ' )using index tablespace '||c.tablespace_name||
    ' storage(initial '||c.INITIAL_EXTENT||
    ' maxextents '||c.MAX_EXTENTS||
    ' )refresh fast with primary key as '
    from user_snapshots a, user_tables b, user_indexes c
    where a.name = b.table_name
    and c.index_name = ( select d.constraint_name from user_constraints d where
    d.table_name = a.table_name and d.constraint_type = 'P')
    order by a.name;


    you can edit this according to ur needs

    HTH
    Amar
    Last edited by adewri; 01-08-2003 at 11:12 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Jan 2003
    Location
    I am from Chile and I live in Santiago
    Posts
    2

    Thumbs up

    Thanks, for your Help.

    Juan

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I doubt very much if that works for all MVs

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    what version of oracle? if 9i look at dbms_metadata.get_ddl

    steve
    I'm stmontgo and I approve of this message

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by pando
    I doubt very much if that works for all MVs
    Hey Pando that's why i said edit it according to ur needs
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yea well he has to get all the where conditions, the query etc etc

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by jvelizca
    Thanks, for your Help.

    Juan
    Atleast it helped him, i hope so
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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