| General Information |
| Purpose |
Creates a table used for identifying chained rows in index organized tables. This package is not created by the DBCA installation. |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsiotc.sql |
| Dependencies |
| COL$ |
DBMS_SQL |
OBJ$ |
PLITBLM |
| DBMS_IOT_LIB |
ICOL$ |
ODCINUMBERLIST |
USER$ |
| DBMS_OUTPUT |
IND$ |
ODCIRIDLIST |
|
|
| Security Model |
Execute granted to PUBLIC |
| Subprograms |
|
| |
| 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'); |
