This appendix contains information about replication support for column length semantics and Unicode.
This appendix contains these topics:
Column Length Semantics for Replication Sites and Table Columns
Replication Support for Unicode
See Also:
The following documents contain more information about length semantics and Unicode:Column length semantics determine whether the length of a column is specified in bytes or in characters. You use BYTE
to specify that the length is in bytes, and you use CHAR
to specify that the length is in characters. CHAR
length semantics is also known as codepoint length semantics.
Because some character sets require more than one byte for each character, a specification of 10
BYTE
for a column might actually store less than 10 characters for certain character sets, but a 10
CHAR
specification ensures that the column can store 10 characters, regardless of the character set. Only Oracle9i Database or later databases can specify CHAR
length semantics.
You set the length semantics for an Oracle database using the NLS_LENGTH_SEMANTICS
initialization parameter, and all VARCHAR2
and CHAR
columns use the setting specified for this initialization parameter as the default. If this initialization parameter is not set, then the default setting is BYTE
.
An individual column can override the length semantics for the database. For example, if the length semantics for a site is CHAR
, then you can still specify BYTE
for the length semantics of an individual column using the CREATE
TABLE
or ALTER
TABLE
statement.
The following statement creates a table and specifies the column length in bytes:
CREATE TABLE byte_col (a VARCHAR2(10 BYTE));
The following statement creates a table and specifies the column length in characters:
CREATE TABLE char_col (a VARCHAR2(10 CHAR));
Note:
A database must have a compatibility level of 9.0.1 or higher to haveCHAR
length semantics. The compatibility level is controlled by the COMPATIBLE
initialization parameter.All master sites in a master group must have the same length semantics, and the individual columns of a master table must have the same length semantics at all master sites. When you have a table in a master group at a master definition site and you want to replicate that table to a new master site, you can create the table at the new site in one of the following ways:
Specify that Advanced Replication generate the table at the new master site when adding the new master site to the master group.
Manually precreate the table at the new master site before adding the master site to the master group.
The following sections describe column length semantics support for each table creation method.
When you specify that Advanced Replication generate the table at the new master site, and you are using CHAR
length semantics, then both the master definition site and the new master site must be running Oracle9i Database or later. If you specify BYTE
length semantics, then these sites can be running a previous Oracle release.
This support is summarized in Table B-1.
Table B-1 Column Length Semantics Support for Generated Tables
Master Definition Site Release | Master Definition Site Column Semantics | New Master Site Release | Resulting Column Semantics at New Master Site |
---|---|---|---|
9.0 or later |
|
9.0 or later |
|
9.0 or later |
|
Prior to 9.0 |
Not supported |
Any release |
|
Any release |
|
When you precreate the table at the new master site, and you are using CHAR
length semantics, then both the master definition site and the new master site must be running Oracle9i Database or later. If you specify BYTE
length semantics, then these sites can be running a previous Oracle release.
Also, because you precreated the table manually, it is possible that you specified a different length semantics for a column in the new master table than was specified for the column in the table at the master definition site. If so, Oracle raises an error because a column in a master table must be using the same length semantics at each master site.
This support is summarized in Table B-2.
Table B-2 Column Length Semantics Support for Precreated Tables
Master Definition Site Release | Master Definition Site Column Semantics | New Master Site Release | New Master Site Column Semantics | Supported? |
---|---|---|---|---|
9.0 or later |
|
9.0 or later |
|
Yes |
9.0 or later |
|
9.0 or later |
|
No |
9.0 or later |
|
9.0 or later |
|
No |
9.0 or later (Multibyte character set) |
|
Prior to 9.0 |
|
No |
9.0 or later (Single-byte character set) |
|
Prior to 9.0 |
|
Yes |
Prior to 9.0 |
|
9.0 or later (Multibyte character set) |
|
No |
Prior to 9.0 |
|
9.0 or later (Single-byte character set) |
|
Yes |
Any release |
|
Any release |
|
Yes |
When you create a materialized view, Oracle determines the length semantics of the columns in the materialized view in the following way:
If the master column is explicitly specified as either BYTE
or CHAR
, then the column in the materialized view retains that specification. In the following example, CHAR
length semantics is explicitly specified for the a
column:
CREATE TABLE char_col (a VARCHAR2(10 CHAR));
If the master column is not explicitly specified, then the column in the materialized view uses the default length semantics of the materialized view site. In the following example, length semantics is not explicitly specified for the a
column:
CREATE TABLE char_col (a VARCHAR2(10));
Materialized view creation fails if an Oracle9i Database or later master has a column with an explicit CHAR
specification and a materialized view site running a release prior to Oracle9i Database attempts to create a materialized view based on this master.
If you prebuild a container table at a materialized view site before you create the materialized view, then the length semantics of the columns in the container table must match the length semantics of the columns in the master. If the length semantics do not match, then an Oracle returns an ORA-12060
error during materialized view creation. You use the ON
PREBUILT
TABLE
clause of the CREATE
MATERIALIZED
VIEW
statement to prebuild a table for a materialized view.
See Also:
The Oracle Database SQL Reference for more information about theON
PREBUILT
TABLE
clause in the CREATE
MATERIALIZED
VIEW
statementThe following operations are always supported if the length semantics of the columns of an updatable materialized view matches the length semantics of the columns of the materialized view's master:
Refreshing the updatable materialized view
Pushing DML changes made at the materialized view to the master
If, however, the length semantics do not match and the master is Oracle9i Database or later, then Oracle raises an error when you try to add the materialized view to a materialized view group. To be updatable, a materialized view must belong to a materialized view group. If you use the replication management API, then you run the CREATE_MVIEW_REPOBJECT
procedure in the DBMS_REPCAT
package to add the materialized view to a materialized view group.
Table B-3 summarizes the length semantics support for updatable materialized views.
Table B-3 Column Length Semantics Support for Updatable Materialized Views
Master Site Release | Master Site Column Semantics | Materialized View Site Release | Materialized View Site Column Semantics | Updatable Materialized View Supported? |
---|---|---|---|---|
9.0 or later |
|
9.0 or later |
|
Yes |
9.0 or later |
|
9.0 or later |
|
No |
9.0 or later |
|
9.0 or later |
|
No |
9.0 or later (Multibyte character set) |
|
Prior to 9.0 |
|
No |
9.0 or later (Single-byte character set) |
|
Prior to 9.0 |
|
Yes |
Prior to 9.0 |
|
9.0 or later |
|
Yes |
Any release |
|
Any release |
|
Yes |
Note:
The master site in Table B-3 can be either a master site in a multimaster replication environment or a master materialized view site.You can use the DBMS_REPCAT
package to propagate a data definition language (DDL) statement that creates a new replicated table or adds columns to an existing replicated table. If you want any of the new columns created by these DDL statements to use CHAR
column length semantics, then make sure you specify CHAR
column length semantics explicitly. Otherwise, the column always has BYTE
length semantics, even if the replication site itself has CHAR
column length semantics set as the default.
The following procedures in the DBMS_REPCAT
package enable you to propagate DDL statements:
ALTER_MASTER_REPOBJECT
CREATE_MASTER_REPOBJECT
EXECUTE_DDL
See Also:
"Column Length Semantics for Replication Sites and Table Columns" for more information about specifyingCHAR
column length semantics explicitlyUnicode is a universal encoded character set that enables you to store information from any language using a single character set. Unicode provides a unique code value for every character, regardless of the platform, program, or language. Unicode is supported in both multimaster and materialized view replication environments. In Oracle9i Database or later, all columns specified as NCHAR
or NVARCHAR2
datatype are stored in Unicode format.
For both master sites and materialized view sites, replication is possible in an environment with different releases of Oracle using an NCHAR
or NVARCHAR2
datatype. However, replication is not recommended when one of the replication sites is a release prior to Oracle9i Database and uses a variable width character set because, in this case, there is a possibility of data loss.
Table B-4 summarizes when replication is recommended.
Table B-4 Replication Support for Globalization Support Character Sets
Release of Local Database with NCHAR or NVARCHAR2 Columns | Release of Remote Database with NCHAR and NVARCHAR2 Columns | Replication Recommended? |
---|---|---|
9.0 or later (Stored in Unicode format) |
9.0 or later (Stored in Unicode format) |
Yes |
Prior to 9.0 (Fixed or variable width national character set format) |
Prior to 9.0 (Fixed or variable width national character set format) |
Yes |
9.0 or later (Stored in Unicode format) |
Prior to 9.0 (Variable width national character set format) |
Not Recommended |
9.0 or later (Stored in Unicode format) |
Prior to 9.0 (Fixed width national character set format) |
Yes |
Prior to 9.0 (Variable width national character set format) |
9.0 or later (Stored in Unicode format) |
Not Recommended |
Prior to 9.0 (Fixed width national character set format) |
9.0 or later (Stored in Unicode format) |
Yes |
In an Advanced Replication environment involving Oracle Database 10g sites and sites running Oracle9i Database that use NCHAR
and NVARCHAR2
datatypes, an Oracle patch must be installed at the sites running Oracle Database releases prior to Oracle9i Database Release 2 (9.2). Contact Oracle Support Services to obtain the appropriate NLS patch as recommended in ALERT 140014.1, "Oracle8/8i to Oracle9i using New AL16UTF16
National Character Set" available on the Oracle MetaLink Web site.
Caution:
Where Table B-4 specifies that replication is not supported, Oracle does not detect an error when you set up replication between the two sites, but data loss can occur later. If data loss occurs, then an error is raised.