Oracle DBMS_IOT Index Organized Tables


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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 @? dbms\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

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us