Difference between utlchain.sql and utlchn1.sql

A very small thought

The difference between utlchain.sql and utlchn1.sql is the column head_rowid data type:

:::::::::::::: utlchain.sql ::::::::::::::
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date );

and

:::::::::::::: utlchn1.sql ::::::::::::::
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid urowid,
analyze_timestamp date );

ROWID and UROWID Datatypes

Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.

Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions and index partitions and subpartitions.

Logical rowids store the addresses of rows in index-organized tables.
A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway.

ROWID:
Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.

UROWID
U as Universal, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway. UROWIDs can save both “physical” as well as “logical” ROW IDs.

So if you have IOT in your database use utlchn1.sql to create the CHAINED_ROWS or else you will get ORA-01496: specified chain row table form incorrect

Hope this helps…

 

Leave a Comment