An error ORA-12096 exists in materialized view logs

Source: Internet
Author: User

When refresh fast on demand is used to incrementally refresh the materialized view, the conventional creation step is to first create the materialized view log on the source database, and then create the materialized view on the target database. Generally, there is no problem. However, when the database environment or other indexes exist, the materialized view log on the source database sends some variables when generating the table structure.

 
Create materialized view log on source_base.t_a with primary key;

There are many other variables behind simple statements.

Materialized view log name: mlog $ _ base table name.
Materialized View logs are created with multiple options: You can specify rowid, primary key, and Object ID. You can also specify sequence or explicitly specify column names. The structure of Materialized View logs generated in the above situations is different.
Columns included in any Materialized View:
Snaptime $ (date): used to indicate the refresh time (keyword segment ).
Dmltype $(varchar2 (1): used to represent DML operation types, I for insert, d For delete, and U for update.
Old_new $(varchar2 (1): Used to indicate whether the value is a new value or an old value. N (EW) indicates the new value, O (LD) indicates the old value, and u indicates the update operation.
Change_vector $ (raw (255): indicates the modified vector, used to indicate which or which fields are modified.

1. With rowid, the materialized view log contains:
M_row $: used to store the rowid of the changed record.

2. With primary key, the materialized view log contains the base table primary key column.

3. With Object ID, the materialized view log contains:
Sys_nc_oid $: ID of the object used to record each change object.

4. With sequence, the materialized view date will include:
Sequence $: a sequence number is assigned to each operation to ensure that the operation is refreshed in order during refresh.

5. If one or more column names are followed by with, the materialized view log contains these columns.

After creating a materialized view log table, do not rush to the target database to create a materialized view, it is important to "carefully check" fields "and" field types "in the materialized view log table ),
Check whether it is consistent with the expected base table.

The author has encountered that the type of the field (such as the jj_uid field) that has been indexed in the source table is number, and the materialized view log automatically adds this field and sets the type to timestamp.

In the future materialized view synchronization, the following error occurs:
5200 11:03:31, an error occurred while updating the market record: Update t_base set lclose = '8. 5300 ', topen = '8. 5300', thigh = '8. 123456 'Where a = '123'
ORA-12096: An error exists in the materialized view log on "source_base". "T_A"
ORA-00932: Data Type inconsistent: Should be timestamp, but get number

For example:

[Day 2] after today's observation, the jj_uid field does not appear during the creation of the materialized view log table, but is automatically added to the log table during the refresh process. The type is timestamp, this field is used for synchronization between the source table and other tables. The index created with this field is automatically displayed in the source table index. Currently, the source and target tables are synchronized normally after one day of operation by modifying the jj_uid type of the log table to number (20) (same source table type.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.