刷新物化视图脚本



CREATE OR REPLACE PROCEDURE OSS03.p_syncbasictable IS
  CURSOR my_cursor IS
    SELECT t.mview_name FROM user_mviews t ORDER BY mview_name;
  lv_mview_name VARCHAR(30);
  lv_errinfo    VARCHAR(4000);
  lv_count      NUMBER(5) := 0;
  vsqlerrm      VARCHAR2(2000);
BEGIN
  OPEN my_cursor;
  LOOP
    FETCH my_cursor
      INTO lv_mview_name;
    EXIT WHEN my_cursor%NOTFOUND;
    lv_count := lv_count + 1;
    BEGIN
      dbms_mview.refresh(lv_mview_name, 'FAST');
    EXCEPTION
      WHEN OTHERS THEN
        vsqlerrm := substrb(SQLERRM, 1, 200);
        oss_user.pro_sys_os_sms_message_insert('06139',
                                               '06139',
                                               '86139xxxxx',
                                               'OSS_SITES_CLASS_INFO刷新错误' ||
                                               vsqlerrm,
                                               100);
        COMMIT;
    END;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    vsqlerrm := substrb(SQLERRM, 1, 200);
    oss_user.pro_sys_os_sms_message_insert('06139',
                                           '06139',
                                           '8613922811554',
                                           'OSS_SITES_CLASS_INFO刷新错误' ||
                                           vsqlerrm,
                                           100);
END p_syncbasictable;
/