$page_title = $titlebar_text; $header_bar_text = $titlebar_text; ///////////////////////////////////// // get header include('rlib_header.php'); // print << Oracle DBMS_IOT Version 11.1  
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsiotc.sql
Dependencies dbms_iot_lib
Object Privileges execute on dbms_iot
 
BUILD_CHAIN_ROWS_TABLE

Creates a table into which references to the chained rows for an index-organized table can be placed using the ANALYZE command
dbms_iot.build_chain_rows_table(
owner               IN VARCHAR2,
iot_name            IN VARCHAR2,
chainrow_table_name IN VARCHAR2 DEFAULT 'IOT_CHAINED_ROWS');
conn / as sysdba

@?\rdbms\admin\dbmsiotc.sql

GRANT execute ON dbms_iot TO uwclass;

conn uwclass/uwclass

CREATE TABLE iottab (
person_id   NUMBER(5),
dept_name   VARCHAR2(20), 
project_no  VARCHAR2(20),
time_worked NUMBER(4,2),
PRIMARY KEY(person_id, dept_name, project_no)) 
ORGANIZATION INDEX pctthreshold 10 OVERFLOW;

exec dbms_iot.build_chain_rows_table('UWCLASS','IOTTAB');

desc iottab

desc iot_chained_rows
 
BUILD_EXCEPTIONS_TABLE

Creates an exception table into which rows of an index-organized table that violate a constraint can be placed
dbms_iot.build_exceptions_table(
owner                 IN VARCHAR2,
iot_name              IN VARCHAR2,
exceptions_table_name IN VARCHAR2 default 'IOT_EXCEPTIONS');
exec dbms_iot.build_exceptions_table('UWCLASS', 'IOTTAB');

desc iottab

desc iot_exceptions

INSERT INTO iottab
(person_id, dept_name, project_no, time_worked)
VALUES
(1, 'IT', '110A', 6.5);

INSERT INTO iottab
(person_id, dept_name, project_no, time_worked)
VALUES
(1, 'IT', '201C', 1.5);

INSERT INTO iottab
(person_id, dept_name, project_no, time_worked)
VALUES
(2, 'IT', '110A', 8.5);

COMMIT;

SELECT * FROM iottab;

ALTER TABLE iottab
ADD CONSTRAINT cc_iottab_max_time_worked
CHECK (time_worked <= 8.0);

SELECT * FROM iot_exceptions;

ALTER TABLE iottab
ADD CONSTRAINT cc_iottab_max_time_worked
CHECK (time_worked <= 8.0)
EXCEPTIONS INTO iot_exceptions;

SELECT * FROM iot_exceptions;
 
CHECK_REDUNDANT_PKEY

Check for redundant primary key entry
dbms_iot.check_redundant_pkey(
table_owner IN VARCHAR2,
table_name  IN VARCHAR2,
index_owner IN VARCHAR2,
index_name  IN VARCHAR2,
uniqueness  IN VARCHAR2,
nblk_uniq   IN BINARY_INTEGER DEFAULT NULL)
RETURN VARCHAR2;
CREATE TABLE labor_hour (
WORK_DATE   DATE,
EMPLOYEE_NO VARCHAR2(8),
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no))
ORGANIZATION INDEX;

INSERT INTO labor_hour VALUES (TRUNC(SYSDATE), 1);
INSERT INTO labor_hour VALUES (TRUNC(SYSDATE), 2);
INSERT INTO labor_hour VALUES (TRUNC(SYSDATE), 3);
INSERT INTO labor_hour VALUES (TRUNC(SYSDATE), 1);

set serveroutput on

DECLARE
 retval VARCHAR2(100);
BEGIN
  retval := dbms_iot.check_redundant_pkey('UWCLASS', 'LABOR_HOUR', 'UWCLASS', 'PK_LABOR_HOUR', 'UNIQUE');
  dbms_output.put_line(retval);
END;
/
 
NUMBER_TO_UROWID
Undocumented
Overload 1
dbms_iot.number_to_urowid(n IN NUMBER, len OUT INTEGER) RETURN VARCHAR2;
TBD
Overload 2 dbms_iot.number_to_urowid(n IN NUMBER) RETURN VARCHAR2;
TBD
Overload 3 dbms_iot.number_to_urowid(n sys.ODCINumberList)
RETURN sys.ODCIRidList;
TBD
 
REPAIR_REDUNDANT_PKEY
Repair redundant primary key entry dbms_iot.repair_redundant_pkey(schema IN VARCHAR2);
exec dbms_iot.repair_redundant_pkey('UWCLASS');
 
Related Topics
Constraints
Index Organized Tables
EOM; ///////////////////////////////////// // get footer include('rlib_footer.php'); ?>