Accenture Enkitec Group E4 Webinar

Wednesday, November 01, 2006

Truncating a Table does not affect the Materialized View Log

I am working on a site that is replicating tables between databases using Materialized Views. I have realised that if a replicated table is truncated on the source database, that the rows remain in the materialized view on the target until a complete refresh is performed. Worse, if ROWID based Materialized View is used, then the fast refresh process will error with 'ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh'. In PeopleSoft, there are no primary keys, and it is not possible to build them if any of the key columns are nullable, which is the case with non-required date fields in PeopleSoft.

Here is an example. I will create two tables, and replicate them with Materialized Views. One by primary key, and the other by ROWID.

CREATE TABLE t_pk
(a NUMBER
,b VARCHAR2(20)
,CONSTRAINT t PRIMARY KEY(a));

CREATE TABLE t_r
(a NUMBER
,b VARCHAR2(20));

CREATE MATERIALIZED VIEW LOG ON t_pk WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW t_pk_mv REFRESH FAST WITH PRIMARY KEY
AS SELECT * FROM t_pk ;

CREATE MATERIALIZED VIEW LOG ON t_r WITH ROWID;
CREATE MATERIALIZED VIEW t_r_mv REFRESH FAST WITH ROWID
AS SELECT * FROM t_r ;

INSERT INTO t_pk VALUES(1,'Old');
INSERT INTO t_pk VALUES(2,'Old');
INSERT INTO t_r VALUES(1,'Old');
INSERT INTO t_r VALUES(2,'Old');

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'f'); END;
BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'f'); END;

SELECT * FROM t_pk_mv;
A B
- ---
1 Old
2 Old

SELECT * FROM t_r_mv;
A B
- ---
1 Old
2 Old

So the data has replicated and everything seems to working fine. Now lets truncate the source tables, and put some new data in. One row has the same key value, one does not.

TRUNCATE TABLE t_pk;
TRUNCATE TABLE t_r;

INSERT INTO t_pk VALUES(2,'New');
INSERT INTO t_pk VALUES(3,'New');
INSERT INTO t_r VALUES(2,'New');
INSERT INTO t_r VALUES(3,'New');

The fast refresh of the Materialized View with the primary key appears to work. The new rows are inserted into the Materialized View, replacing existing rows with the same key values, but the old rows remain.

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'f'); END;
PL/SQL procedure successfully completed.

SELECT * FROM t_pk;
A B
- ---
2 New
3 New

SELECT * FROM t_pk_mv;
A B
- ---
1 Old
2 New
3 New


The fast refresh of the Materialized View by ROWID fails, and the old data remains in place.

BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'f'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1


But rows 1 & 2 are in MV but not source table

SELECT * FROM t_r;
A B
- ---
2 New
3 New

SELECT * FROM t_r_mv;
A B
- ---
1 Old
2 Old

However, a full refresh corrects the discrepancy

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'c'); END;
BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'c'); END;

SELECT * FROM t_pk_mv;
A B
- ---
2 New
3 New

SELECT * FROM t_r_mv;
A B
- ---
2 New
3 New

It seems to me, that it would be appropriate to prevent a Truncate command executing if there is a Materialized View Log on the table. I have written such a trigger

CREATE OR REPLACE TRIGGER mvtrunc_lock
BEFORE TRUNCATE
ON SYSADM.SCHEMA
DECLARE
e_generate_message EXCEPTION;
l_recname VARCHAR2(15 CHAR);
l_msg VARCHAR2(100 CHAR) := 'No Message.';
l_msg2 VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent
||' '||lower(ora_dict_obj_type)
||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name||'. ';

sql_text ora_name_list_t;
l_sql_stmt VARCHAR2(1000 CHAR) := '';
n INTEGER;
i INTEGER;

BEGIN
/*extract the originating SQL statement into a string variable*/
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
l_sql_stmt := SUBSTR(l_sql_stmt || sql_text(i),1,1000);
END LOOP;

IF ora_dict_obj_type = 'TABLE' AND
ora_sysevent = 'TRUNCATE' THEN

BEGIN /*if a materialized view log exists*/
SELECT 'There is a materialized view log.'
INTO l_msg
FROM all_mview_logs l
WHERE ROWNUM = 1
AND l.master = ora_dict_obj_name
AND l.log_owner = ora_dict_obj_owner
;
RAISE e_generate_message;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
WHEN e_generate_message THEN
RAISE_APPLICATION_ERROR(-20042,
'MVTRUNC_LOCK:'||l_msg2||l_msg||CHR(10)||'SQL:'||l_sql_stmt);
END;
/

show errors

The trigger MVTRUNC_LOCK can be downloaded from the Go-Faster website.