| 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'); |
|
