如果您只是尝试刷新物化视图,我不知道如何使用DML错误日志记录来捕获所有问题行 . 另一方面,您可以创建一个表并在填充表时使用DML错误日志记录来捕获刷新实例化视图时遇到的所有错误 .
可能您可以手动填充此表,然后create a materialized view on this prebuilt table . 这可能会产生问题,具体取决于物化视图的确切使用方式以及启用了哪种查询重写,因为您构建的表将丢失基础表中的一些数据(写入错误日志的行) .
创建表和错误日志
SQL> create table t (
2 col1 number,
3 col2 number
4 );
Table created.
Elapsed: 00:00:00.00
SQL> ed
Wrote file afiedt.buf
1 begin
2 dbms_errlog.create_error_log( 'T', 'T_ERR' );
3* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> create function f1
2 return varchar2
3 is
4 begin
5 return 'A';
6 end;
7 /
Function created.
尝试插入10行 . 3将失败,因为 LEVEL 将是3的倍数,并且函数返回的字符串不能转换为数字
Elapsed: 00:00:00.01
SQL> insert into t( col1, col2 )
2 select level,
3 (case when mod(level,3) = 0
4 then to_number( f1 )
5 else mod(level,3)
6 end)
7 from dual
8 connect by level <= 10
9 log errors into t_err
10 reject limit unlimited;
7 rows created.
Elapsed: 00:00:00.01
SQL> ed
Wrote file afiedt.buf
1 select ora_err_mesg$, col1, col2
2* from t_err
SQL> /
ORA_ERR_MESG$ COL1 COL2
------------------------------ ---------- ----------
ORA-01722: invalid number 3 0
ORA-01722: invalid number 6 0
ORA-01722: invalid number 9 0
Elapsed: 00:00:00.00
现在,使用此预构建表来创建物化视图
SQL> ed
Wrote file afiedt.buf
1 create materialized view t
2 on prebuilt table
3 as
4 select 1 col1, 1 col2
5* from dual
SQL> /
Materialized view created.
Elapsed: 00:00:00.11