CNDBA社区
概念
The new SYNC_INTERIM_TABLE procedure for the DBMS_REDEFINITION package enables incremental refresh of dependent materialized views on the interim table. The SYNC_INTERIM_TABLE procedure refreshes the materialized view as part of its execution.
DBMS_REDEFINITION包的新SYNC_INTERIM_TABLE程序允许在临时表上增加依赖物化视图的增量刷新。SYNC_INTERIM_TABLE过程作为其执行的一部分刷新物化视图。
All dependent materialized views on the source table must be fully refreshed after redefinition completes, increasing the time when the table is fully usable. Refreshing the materialized views during the redefinition process eliminates the time to perform a complete refresh at the end.
所有依赖于源表的物化视图必须在重新定义完成后得到完全的刷新，从而增加了该表完全可用的时间。在重新定义过程中刷新物化视图，消除了在结束时执行完全刷新的时间。
实验
1 创建基础数据
1.1 创建表基础表
[leo@www.cndba.cn ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 31 12:01:01 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> conn test/test@pdbcndba
Connected.
SQL> CREATE TABLE orders(
order_id NUMBER(12) PRIMARY KEY,
order_date TIMESTAMP WITH LOCAL TIME ZONE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6));
Table created.
1.2 创建重新定义表，将order_mode列的大小增加到16。临时表定义为:
SQL> CREATE TABLE int_orders(
order_id NUMBER(12),
order_date TIMESTAMP WITH LOCAL TIME ZONE,
order_mode VARCHAR2(16),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6)); 9
Table created.
1.3 还假设这个表有相关的具体化视图。该表有一个物化视图日志，其创建的语句如下:
SQL> CREATE MATERIALIZED VIEW LOG ON orders WITH PRIMARY KEY, ROWID;
Materialized view log created.
1.4 创建以下物化视图
SQL> CREATE MATERIALIZED VIEW orders_pk REFRESH FAST AS
SELECT * FROM orders; 2
Materialized view created.
SQL> CREATE MATERIALIZED VIEW orders_rowid REFRESH FAST WITH ROWID AS
SELECT * FROM orders; 2
Materialized view created.
oe.orders_pk物化视图是一种快速可刷新、主键物化视图。因此，可以在在线表重定义期间刷新。
oe.orders_rowid物化视图是快速可刷新的，但它是一个ROWID物化视图。因此，不能在在线表重定义期间刷新。
2 完成以下步骤，在orders表上执行在线表重定义，同时刷新orders_pk物化视图:
2.1 开始重新定义过程。
SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'TEST',
orig_table => 'orders',
int_table => 'int_orders',
options_flag => DBMS_REDEFINITION.CONS_USE_PK,
refresh_dep_mviews => 'Y');
END;
/
PL/SQL procedure successfully completed.
2.2 复制依赖对象。(在int_orders上自动创建任何触发器、索引、物化视图日志、授予和约束。)
SQL>
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'TEST',
orig_table => 'orders',
int_table => 'int_orders',
copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => TRUE,
num_errors => num_errors);
END;
/
PL/SQL procedure successfully completed.
2.3 检查重新定义状态:
SQL> SELECT REDEFINITION_ID, REFRESH_DEP_MVIEWS
FROM DBA_REDEFINITION_STATUS
WHERE BASE_TABLE_OWNER = 'TEST' AND BASE_TABLE_NAME = 'ORDERS'; 2 3
REDEFINITION_ID R
--------------- -
1 Y
2.4 在另一个会话orders上执行DML。例如:
SQL> INSERT INTO orders VALUES(3000,sysdate,'direct',102,1,42283.2,154,NULL);
1 row created.
SQL> commit;
Commit complete.
2.5 同步临时表int_orders。这一步刷新依赖的物化视图orders_pk。
SQL> BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'TEST',
orig_table => 'ORDERS',
int_table => 'INT_ORDERS');
END;
/
PL/SQL procedure successfully completed.
2.6 检查订单的相关物化视图的刷新状态:
SQL> col OWNER for a10
SQL> col MVIEW_NAME for a10
SQL> col STALENESS for a20
SQL> SELECT m.OWNER, m.MVIEW_NAME, m.STALENESS, m.LAST_REFRESH_DATE
FROM ALL_MVIEWS m, ALL_MVIEW_DETAIL_RELATIONS d
WHERE m.OWNER=d.OWNER AND
m. MVIEW_NAME=d.MVIEW_NAME AND
d.DETAILOBJ_OWNER = 'TEST' AND
d.DETAILOBJ_NAME = 'ORDERS';
OWNER MVIEW_NAME STALENESS LAST_REFRESH
---------- ---------- -------------------- ------------
TEST ORDERS_PK FRESH 31-AUG-17
TEST ORDERS_ROWID NEEDS_COMPILE 31-AUG-17
在前一个步骤中，orders_pk物化视图被刷新，因此它对它的过时状态有了新的认识。在上一个步骤中，orders_rowid物化视图并没有刷新，因此它的跟踪状态是需要的。
2.7 完成重新定义
SQL> BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'TEST',
orig_table => 'ORDERS',
int_table => 'INT_ORDERS');
END;
/
PL/SQL procedure successfully completed.
2.8 查询orders_pk物化视图，以确认插入到orders表中的新行存在于物化视图中，因为它在在线表重定义期间刷新。
SQL> set line 200
SQL> select * from orders_pk ;
ORDER_ID ORDER_DATE ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID
---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ------------
3000 31-AUG-17 12.13.36.000000 PM direct 102 1 42283.2 154
SQL> select * from INT_ORDERS;
ORDER_ID ORDER_DATE ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID
---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ------------
3000 31-AUG-17 12.13.36.000000 PM direct 102 1 42283.2 154
参考官方文档：
版权声明：本文为博主原创文章，未经博主允许不得转载。
