如何在 Oracle 物化视图中记录导致错误的数据？

Question

我们创建了一些物化视图，这些视图在实际应用程序数据的副本上运行良好。该应用程序不会监管自己的数据。从那时起，一些用户在数据输入时可能要么粗心，要么有创意。 Mview 现在窒息而死。错误消息表明我们正在从一个或多个函数返回多行。

我们一直在尝试使用异常——在 DBMS_Output 中针对第一行 object_id 取得了一些成功，导致（其中一个）函数失败。最好能够完成 MView 的运行，并记录导致每个函数出现问题的 object_ids。我们尚未成功将异常数据插入表中。

平台是Oracle 10g2。我一直试图将 DML 错误登录塞进我的脑子里。我知道这应该适用于批量数据，并且我假设创建物化视图符合条件。这对 MView 有用吗？这是最好的方法吗？

Answer 1

如果您只是尝试刷新物化视图，我不知道如何使用 DML 错误日志记录来捕获所有问题行。另一方面，您可以创建一个表，并在填充该表时使用 DML 错误日志记录来捕获刷新物化视图时遇到的所有错误。

您可以手动填充此表，然后创建一个物化在此预建表上查看。这可能会产生问题，具体取决于具体化视图的使用方式以及启用的查询重写类型，因为您构建的表将丢失基础表中的一些数据（写入错误日志的行）。

创建表和错误日志

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