Streams DML Types Supported and Supported Datatypes

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.1.0.6
Information in this document applies to any platform.
Oracle Server Enterprise Edition – Version: 9.2.0.1 to 11.1.0.6

Purpose

Since the introduction of Streams in release 9.2, the list of supported datatypes and DML changes managed have been increasing across releases. This note will list the supported and unsupported data types across different releases and will list types of DML supported.

For DDL types and further details please refer to documentation.

Oracle Streams Concepts and Administration

Scope and Application

To be used by DBA’s as a reference when configuring and troubleshooting
Streams.

Streams DML Types Supported and Supported Datatypes

The first thing it is necessary to clarify is that the DML types and data types supported by Capture process do not always match with the DML types and data types supported by an Apply process.

Therefore I am distinguish the behavior of the Capture Process and the Apply process.
Also note that the features are being increased across released, therefore it should be assume that something supported on release 9i still is on release 11G.

Also please be aware of views ALL/DBA_STREAMS_NEWLY_SUPPORTED and ALL/DBA_STREAMS_UNSUPPORTED available since release 10.1. They allow you to check what are the new supported tables and the not supported tables.

Capture Process

Capture process never captures changes in the SYS, SYSTEM, or CTXSYS schemas.
Capture process captures the following types of DML changes made to tables INSERT, UPDATE, DELETE, MERGE and Piecewise updates to LOBs.
A capture process cannot capture DML changes made to :

* Temporary tables or object tables,
* Tables stored with segment compression enabled,
* Tables in the flashback data archive.
* External tables

Regarding Index Organized Tabled (IOTs):

- In release 9i, capture process cannot capture DML changes made to index-organized tables or object tables
- In release 10G, capture process can capture changes made to an index-organized table only if
the index-organized table does not contain any columns of the following data types:

* ROWID
* UROWID
* User-defined types (including object types, REFs, varrays, and nested tables)

- In release 11.1 capture process can capture changes made to an index-organized table only if
the index-organized table does not contain any columns of the following data types:

* ROWID
* User-defined types (including object types, REFs, varrays, and nested tables)
* XMLType stored object relationally or as binary XML (XMLType stored as CLOB is supported.)
* The following Oracle-supplied types: Any types, URI types, spatial types, and media types

NOTE: It is the originating database source of the redo logs which determines
what is supported and what is not supported.

ie Using Oracle11g CDC, an attempt to mine 10g logs containing CLOB XMLType
operations fails with ORA-26783 “Column data type not supported”.
This is because the logs are 10g, in which the CLOB XMLType was not supported yet.
So, even though you are using 11g CDC, you receive ORA-26783 because the
logs are from 10g database.

When capturing the row changes resulting from DML changes made to tables, a
capture process can capture changes made to columns of the following data types:

A capture process does not capture the results of DML changes to columns of the
following data types:

* SecureFile CLOB, NCLOB, and BLOB
* BFILE
* ROWID
* User-defined types (including object types, REFs, varrays, and nested tables)
* XMLType stored object relationally or as binary XML
* The following Oracle-supplied types: Any types, URI types, spatial types, and media types

In addition, a capture process does not capture the results of DML changes to virtual
columns.

NOTE: In prior releases of Oracle Database, Oracle Streams did not support the
replication of changes to tables with virtual columns.
In Oracle Database 11g Release 1 (11.1) and later, Oracle Streams supports tables
with virtual columns.

Streams 9i capture process will also fail with ORA-902 if the tables are using function
or descending indexes.

Apply Process

As the Apply process do not handle DML calls, it handles LCRs or user-messages, the restrictions are applicable only at data type layer.
When applying row LCRs for data manipulation language (DML) changes to tables, an apply process applies changes made to columns of the following data types:

NOTE: SecureFile CLOB, NCLOB, BLOB and Virtual Columns are the
new features of Oracle 11g database.
(Reference: 11g Database New Features Guide manual)

An apply process does not apply row LCRs containing the results of DML changes in columns of the following data types:

* SecureFile CLOB, SecureFile NCLOB, and SecureFile BLOB
* BFILE
* ROWID
* User-defined types (including object types, REFs, varrays, and nested tables)
* The following Oracle-supplied types: Any types, URI types, spatial types, and media type

© 2009 – 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. 11g R1 Streams New Features
2. Streams Combined Capture and Apply in 11g
3. Know more about DML
4. Streams Conflict Resolution
5. Split and Merge of a Streams Destination
6. Troubleshooting Streams Apply Error ORA-1403, ORA-26787 or ORA-26786
7. Script to Prevent Excessive Spill of Message From the Streams Buffer Queue To Disk
8. 9i Best Practices For Streams RAC Setup
9. Compare and Converge in an Oracle Streams Replication Environment
10. How to setup Streams replication using DBMS_STREAMS_ADM.MAINTAIN_* set of procedures