64 DBMS_IOT

The DBMS_IOT package creates a table into which references to the chained rows for an index-organized table can be placed using the ANALYZE command. DBMS_IOT can also create an exception table into which references to the rows of an index-organized table that violate a constraint can be placed during the enable_constraint operation.

DBMS_IOT is not loaded during database installation. To install DBMS_IOT, run dbmsiotc.sql, available in the ADMIN directory.

This chapter contains the following topics:


Summary of DBMS_IOT Subprograms

Table 64-1 DBMS_IOT Package Subprograms

Subprogram Description

BUILD_CHAIN_ROWS_TABLE Procedure

Creates a table into which references to the chained rows for an index-organized table can be placed using the ANALYZE command

BUILD_EXCEPTIONS_TABLE Procedure

Creates an exception table into which rows of an index-organized table that violate a constraint can be placed



BUILD_CHAIN_ROWS_TABLE Procedure

This procedure creates a table into which references to the chained rows for an index-organized table can be placed using the 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');

Parameters

Table 64-2 BUILD_CHAIN_ROWS_TABLE Procedure Parameters

Parameter Description

owner

Owner of the index-organized table.

iot_name

Index-organized table name.

chainrow_table_name

Intended name for the chained-rows table.


Usage Notes

You should create a separate chained-rows table for each index-organized table to accommodate its primary key.

Examples

CREATE TABLE l(a char(16),b char(16), c char(16), d char(240), 
PRIMARY KEY(a,b,c)) ORGANIZATION INDEX pctthreshold 10 overflow;    
EXECUTE DBMS_IOT.BUILD_CHAIN_ROWS_TABLE('SYS','L','LC');  

A chained-row table is created with the following columns:

Column Name                    Null?    Type  
------------------------------ -------- ----  
OWNER_NAME                              VARCHAR2(30)  
TABLE_NAME                              VARCHAR2(30)  
CLUSTER_NAME                            VARCHAR2(30)  
PARTITION_NAME                          VARCHAR2(30)  
SUBPARTITION_NAME                       VARCHAR2(30)  
HEAD_ROWID                              ROWID  
TIMESTAMP                               DATE  
A                                       CHAR(16)  
B                                       CHAR(16)  
C                                       CHAR(16) 

BUILD_EXCEPTIONS_TABLE Procedure

This procedure creates an exception table into which rows of an index-organized table that violate a constraint can be placed during the execution of the following SQL statements:

  • ALTER TABLE ... ENABLE CONSTRAINT ... EXCEPTIONS INTO

  • ALTER TABLE ... ADD CONSTRAINT ... EXCEPTIONS INTO

Syntax

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

Parameters

Table 64-3 BUILD_EXCEPTIONS_TABLE Procedure Parameters

Parameter Description

owner

Owner of the index-organized table.

iot_name

Index-organized table name.

exceptions_table_name

Intended name for exception-table.


Usage Notes

You should create a separate exception table for each index-organized table to accommodate its primary key.

Examples

EXECUTE DBMS_IOT.BUILD_EXCEPTIONS_TABLE('SYS','L','LE');  
  

An exception table for the preceding index-organized table with the following columns:

Column Name                    Null?    Type  
------------------------------ -------- ----  
ROW_ID                                  VARCHAR2(30)  
OWNER                                   VARCHAR2(30)  
TABLE_NAME                              VARCHAR2(30)  
CONSTRAINT                              VARCHAR2(30)  
A                                       CHAR(16)  
B                                       CHAR(16)  
C                                       CHAR(16)