DBMS IOT package used Index-Organized tables for detect row chaining and violate constraints

DBMS IOT Package used for Index-Organized tables. It has two procedures:
BUILD_CHAIN_ROWS_TABLE: Create a table having references to chained rows for Index-Organized table can be use with ANALYZE command
Syntax:

DBMS_IOT.BUILD_CHAIN_ROWS_TABLE (
owner IN VARCHAR2,
iot_name IN VARCHAR2,
chainrow_table_name IN VARCHAR2 default 'IOT_CHAINED_ROWS');


BUILD_EXCEPTIONS_TABLE: Create an exception table into which references to the rows of an index-organized table that violate a constraint can be placed
Syntax:

DBMS_IOT.BUILD_EXCEPTIONS_TABLE (
owner IN VARCHAR2,
iot_name IN VARCHAR2,
exceptions_table_name IN VARCHAR2 default 'IOT_EXCEPTIONS');


Package need to be created from @?/RDBMS/ADMIN/dbmsiotc.sql script.

1. Create package with SYS user.

@?/RDBMS/ADMIN/dbmsiotc.sql


2. Grant the package permission to user.

grant execute on sys.dbms_iot to PUBLIC;


3. If its not work then recompile the created packages with SYS user.

@?/rdbms/admin/utlrp.sql;


Example of using DBMS IOT with BUILD_CHAIN_ROWS_TABLE
It used to detect the chained rows in Index Organized table.

CREATE TABLE emptest(fname char(16),lname char(16),addr char(240), PRIMARY KEY(fname,lname,addr)) ORGANIZATION INDEX pctthreshold 10 overflow;

EXECUTE DBMS_IOT.BUILD_CHAIN_ROWS_TABLE('HR','EMPTEST','IOT_EMPTEST');
PL/SQL procedure successfully completed.

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER_NAME                                         VARCHAR2(128)
 TABLE_NAME                                         VARCHAR2(128)
 CLUSTER_NAME                                       VARCHAR2(128)
 PARTITION_NAME                                     VARCHAR2(128)
 SUBPARTITION_NAME                                  VARCHAR2(128)
 HEAD_ROWID                                         ROWID
 TIMESTAMP                                          DATE
 FNAME                                              CHAR(16)
 LNAME                                              CHAR(16)
 ADDR                                               CHAR(240)

Example of using DBMS IOT with BUILD_EXCEPTIONS_TABLE
It used for detect the violate a constraint in Index Organized table.

EXECUTE DBMS_IOT.BUILD_EXCEPTIONS_TABLE('HR','EMPTEST','IOTEXCEPTION_EMPTEST');

desc IOTEXCEPTION_EMPTEST;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROW_ID                                             VARCHAR2(30)
 OWNER                                              VARCHAR2(128)
 TABLE_NAME                                         VARCHAR2(128)
 CONSTRAINT                                         VARCHAR2(128)
 FNAME                                              CHAR(16)
 LNAME                                              CHAR(16)
 ADDR                                               CHAR(240)