需求背景: app 数据库类型OLTP 主要是数据仓库纬度数据表;pos数据库类型是OLAT 做数据分析用,大量门店销售数据,进行物流仓库货物调拨,通过系统进行分获配货调拨;
step 1. 删除所有pos 数据库正在进行的job任务
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;
/
step2 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;
step 3. 在重建物化视图之前,查询出pos 数据物化视图的索引,有两种方法,一是通过集合提取,另一种方法通过get_ddl 包提取索引的定义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;
/
step 4,重建物化视图,在此之前不要忘记保留索引。
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@APPLINK) 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;
/
step 5. 重新编译用户对象。
BEGIN
DBMS_UTILITY.compile_schema(schema => 'POSUSER');
END;
/