System views

dba_mviews - materialized views
dba_mview_logs - materilalized view logs
dba_registered_mviews - registered materialized views

dba_refresh - refresh groups
dba_refresh_children - materialized views in refresh groups

Source table modification

Adding new column

Adding new column to the source table will not break existing materialized view, but new column will not be synchronized with the mview

CREATE TABLE test_table(id NUMBER PRIMARY KEY, num NUMBER);
CREATE MATERIALIZED VIEW LOG ON test_table;
CREATE MATERIALIZED VIEW test_mv REFRESH ON DEMAND AS SELECT * FROM test_table;

INSERT INTO test_table VALUES(1,2);
COMMIT;

BEGIN
  dbms_mview.refresh('test_mv', 'C');
END;
/


ALTER TABLE test_table ADD (str VARCHAR2(10));
INSERT INTO test_table VALUES(2,3, 'AAA');
COMMIT;

BEGIN
  dbms_mview.refresh('test_mv', 'F');
END;
/

SELECT * FROM test_table;
SELECT * FROM mlog$_test_table;
SELECT * FROM test_mv;

Removing a column

Removing a column from a source table will lead to an error ORA-12008: error in materialized view refresh path. So after remove need to re-create a materialized view.

Last edited Jul 12, 2012 at 8:23 PM by alekseiv, version 4

Comments

No comments yet.