重建物化视图通用版

数据库 waitig 481℃ 百度已收录 0评论



1)  drop_all_mv_jobs.sql                         [pos]

BEGIN
  FOR ITEM IN (SELECT *
                 FROM (select o.* from USER_JOBS o where o.WHAT like ‘dbms_refresh.refresh%’ or o.WHAT like ‘%dbms_ddl.analyze_object%’ )) LOOP

    DBMS_JOB.REMOVE(ITEM.JOB);
  END LOOP;
  COMMIT;
END;
/
2)  recreate_all_app_mv_logs.sql                 [app]

BEGIN
  FOR ITEM IN (SELECT A.*,
                      CASE
                        WHEN B.CONSTRAINT_NAME IS NULL THEN
                         ‘NO’
                        ELSE
                         ‘YES’
                      END HAS_PRIMARYKEY
                 FROM USER_MVIEW_LOGS A
                 LEFT JOIN (SELECT *
                             FROM USER_CONSTRAINTS
                            WHERE CONSTRAINT_TYPE = ‘P’) B
                   ON A.MASTER = B.TABLE_NAME) LOOP
    EXECUTE IMMEDIATE ‘DROP MATERIALIZED VIEW LOG ON ‘ || ITEM.MASTER ;
    EXECUTE IMMEDIATE ‘CREATE MATERIALIZED VIEW LOG ON ‘ || ITEM.MASTER ||
                         ‘ WITH ‘ || CASE WHEN ITEM.HAS_PRIMARYKEY = ‘YES’ THEN
                         ‘PRIMARY KEY’ ELSE ‘ROWID’ END;
  END LOOP;
END;
/
3)  save_select_all_pos_mv_index_sql             [pos] SAVE OUTPUT QUERY SQL AS select_all_pos_mv_index.sql

DECLARE
  TYPE REC_NORMAL IS RECORD(
    COLUMN_NAME     USER_IND_COLUMNS.COLUMN_NAME%TYPE,
    COLUMN_POSITION USER_IND_COLUMNS.COLUMN_POSITION%TYPE);
  TYPE NNT_NORMAL IS TABLE OF REC_NORMAL;
  L_NNT_NORMAL1 NNT_NORMAL := NNT_NORMAL();
  L_NNT_NORMAL2 NNT_NORMAL := NNT_NORMAL();
  L_NNT_NORMAL3 NNT_NORMAL := NNT_NORMAL();
  L_NNT         NNT_NORMAL := NNT_NORMAL();
  L_SQL         VARCHAR2(3000);

  TYPE REC_EXP IS RECORD(
    COLUMN_EXPRESSION USER_IND_EXPRESSIONS.COLUMN_EXPRESSION%TYPE,
    COLUMN_POSITION   USER_IND_EXPRESSIONS.COLUMN_POSITION%TYPE);
  TYPE NNT_EXP IS TABLE OF REC_EXP;
  L_NNT_EXP    NNT_EXP := NNT_EXP();
  L_REC_NORMAL REC_NORMAL;
BEGIN
  FOR ITEM IN (SELECT UI.INDEX_NAME, UI.TABLE_NAME
                 FROM USER_INDEXES UI
                WHERE UI.TABLE_NAME IN
                      (SELECT UW.MVIEW_NAME
                         FROM USER_MVIEWS UW
                        WHERE UW.MASTER_LINK IS NOT NULL)
                  AND NOT EXISTS
                (SELECT 1
                         FROM USER_CONSTRAINTS UC
                        WHERE UI.TABLE_NAME = UC.TABLE_NAME
                          AND UI.INDEX_NAME = UC.
                        CONSTRAINT_NAME
                          AND UC.CONSTRAINT_TYPE = ‘P’)
                  AND UI.INDEX_NAME NOT LIKE ‘I_SNAP$%’) LOOP
    L_NNT_NORMAL1 := NNT_NORMAL();
    L_NNT_NORMAL2 := NNT_NORMAL();
    L_NNT_NORMAL3 := NNT_NORMAL();
    L_NNT_EXP     := NNT_EXP();
    L_SQL         := ‘CREATE INDEX ‘ || ITEM.INDEX_NAME || ‘ ON ‘ ||
                     ITEM.TABLE_NAME || ‘(‘;
    SELECT UIC.COLUMN_NAME, UIC.COLUMN_POSITION
      BULK COLLECT
      INTO L_NNT_NORMAL1
      FROM USER_IND_COLUMNS UIC
     WHERE UIC.INDEX_NAME = ITEM.INDEX_NAME 
       AND NOT EXISTS
     (SELECT 1
              FROM USER_IND_EXPRESSIONS UIE
             WHERE UIC.INDEX_NAME = UIE.INDEX_NAME
               AND UIC.COLUMN_POSITION = UIE.COLUMN_POSITION);
 
    SELECT UIE.COLUMN_EXPRESSION, UIE.COLUMN_POSITION
      BULK COLLECT
      INTO L_NNT_EXP
      FROM USER_IND_EXPRESSIONS UIE
     WHERE UIE.INDEX_NAME = ITEM.INDEX_NAME;
 
    IF L_NNT_EXP.COUNT > 0 THEN
      FOR I IN L_NNT_EXP.FIRST .. L_NNT_EXP.LAST LOOP
        L_REC_NORMAL.COLUMN_NAME     := SUBSTR(L_NNT_EXP(I)
                                               .COLUMN_EXPRESSION,
                                               1,
                                               4000);
        L_REC_NORMAL.COLUMN_POSITION := L_NNT_EXP(I).COLUMN_POSITION;
        L_NNT_NORMAL2.EXTEND();
        L_NNT_NORMAL2(L_NNT_NORMAL2.LAST) := L_REC_NORMAL;
      END LOOP;
    END IF;
    —-L_NNT_NORMAL3=L_NNT_NORMAL1+L_NNT_NORMAL2
    L_NNT_NORMAL3 := L_NNT_NORMAL1 MULTISET UNION L_NNT_NORMAL2;
 
    —-GENERATE INDEX COLUMN LIST FROM L_NNT_NORMAL3
    FOR J IN 1 .. L_NNT_NORMAL3.COUNT LOOP
   
      FOR I IN L_NNT_NORMAL3.FIRST .. L_NNT_NORMAL3.LAST LOOP
        IF J = L_NNT_NORMAL3(I).COLUMN_POSITION THEN
          L_SQL := L_SQL || CASE
                     WHEN J = 1 THEN
                      ”
                     ELSE
                      ‘,’
                   END || L_NNT_NORMAL3(I).COLUMN_NAME;
        END IF;
      END LOOP;
    END LOOP;
    L_SQL := L_SQL || ‘);’;
    DBMS_OUTPUT.PUT_LINE(L_SQL);
  END LOOP;
END;
/

4)  recreate_all_pos_mv.sql                      [pos] NOTE:DON’T FORGET TO EXECUTE select_all_pos_mv_index.sql

BEGIN
  FOR ITEM IN (SELECT A.*,
                      B.MASTER,
                      CASE
                        WHEN B.PRIMARY_KEY = ‘YES’ THEN
                         1
                        ELSE
                         0
                      END R_PRIMARY_KEY
                 FROM USER_MVIEWS A
                 LEFT JOIN (SELECT * FROM
USER_MVIEW_LOGS@OPTI7APP_LINK) B
                   ON REPLACE(A.MVIEW_NAME, ‘MV_’, ”) = B.MASTER
                WHERE MASTER_LINK IS NOT NULL) LOOP
   EXECUTE IMMEDIATE ‘DROP MATERIALIZED VIEW ‘ || ITEM.MVIEW_NAME ;
   EXECUTE IMMEDIATE ‘CREATE MATERIALIZED VIEW ‘ || ITEM.MVIEW_NAME ||
                         ‘ REFRESH FORCE WITH ‘ || CASE WHEN
                         ITEM.R_PRIMARY_KEY = 1 THEN ‘PRIMARY KEY’ ELSE
                         ‘ROWID’ END ||
                         ‘ ON DEMAND START WITH SYSDATE NEXT SYSDATE + 4/24 AS SELECT * FROM ‘ ||
                         ITEM.MASTER || ITEM.MASTER_LINK ;
  END LOOP;
END;
/

5)  recompile_objects.sql                        [pos] IF PROJECT TYPE IS TD,PLEASE CHANGE SHEME TO POSBETA

BEGIN
DBMS_UTILITY.compile_schema(schema => ‘POSUSER’);
END;
/


本文由【waitig】发表在等英博客
本文固定链接:重建物化视图通用版
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)