Oracle | DBMS

Oracle DBMS_IOT Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Purpose Creates a table used for identifying chained rows in index organized tables. This package is not created by the DBCA installation because it is not required with database versions greater than 8.0.

I am documenting it here in the library because, for reasons beyond my comprehension, Oracle won't drop it from the distribution. If they keep it ... The Library keeps it.

AUTHID DEFINER

Dependencies

COL$	DBMS_STANDARD	ODCINUMBERLIST
DBMS_ASSERT	ICOL$	ODCIRIDLIST
DBMS_IOT_LIB	IND$	PLITBLM
DBMS_SQL	OBJ$	USER$

Documented Yes but only the first two procedures. The final three are officially undocumented

First Available No known

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsiotc.sql

SQL> @?/rdbms/admin/dbmsiotc.sql

      

      SP2-0808: Package created with compilation warnings

      

      Synonym created.

      

      Grant succeeded.

      

      Library created.

      

      SP2-0810: Package Body created with compilation warnings

      

      SQL> sho err

      Errors for PACKAGE BODY DBMS_IOT:

      

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      92/21 PLW-07203: parameter 'C_NAM' may benefit from use of the NOCOPY
      compiler hint

      518/3 PLW-06002: Unreachable code

Subprograms

BUILD_CHAIN_ROWS_TABLE	CHECK_REDUNDANT_PKEY	REPAIR_REDUNDANT_PKEY
BUILD_EXCEPTIONS_TABLE	NUMBER_TO_UROWID

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 sys@pdbdev as sysdba

      

      @?\rdbms\admin\dbmsiotc.sql

      

      GRANT execute ON dbms_iot TO uwclass;

      

      conn uwclass/uwclass@pdbdev

      

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

conn uwclass/uwclass@pdbdev

      

      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;

conn uwclass/uwclass@pdbdev

      

      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;

DECLARE

       vLen INTEGER;

       vRet VARCHAR2(30);

      BEGIN

        vRet := dbms_iot.number_to_urowid(42, vLen);

        dbms_output.put_line(vRet);

        dbms_output.put_line(TO_CHAR(vLen));

      END;

      /

Overload 2 dbms_iot.number_to_urowid(n IN NUMBER) RETURN VARCHAR2;

SELECT dbms_iot.number_to_urowid(42)

      FROM dual;

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