Oracle DBMS_I_INDEX_UTL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Internal utilities for verifying and rebuilding indexes and index components
AUTHID N/A
Dependencies
DBA_SCHEDULER_JOBS DBMS_SQL JOB_DEFINITION_ARRAY
DBMS_ASSERT DBMS_STANDARD OBJ$
DBMS_EXPORT_EXTENSION DBMS_SYSTEM PLITBLM
DBMS_INDEX_UTL DBMS_SYS_ERROR USER$
DBMS_ISCHED DBMS_SYS_SQL V$INSTANCE
DBMS_JOB DUAL V$PARAMETER
DBMS_PIPE IDX_RB$JOBSEQ V_$DB_PIPES
DBMS_REGISTRY IND$ X$KSPPCV
DBMS_SCHEDULER INDPART_PARAM$ X$KSPPI
DBMS_SNAPSHOT_KKXRCA JOB_DEFINITION  
Documented No
Exceptions
Error Code Reason
ORA-20001 Index <index_name> does not exist in schema <schema_name>.
or
User <schema_name> does not exist.
First Available 10gR1
Security Model Owned by SYS with no privileges granted
Source $ORACLE_HOME/rdbms/admin/prvtidxu.plb
Subprograms
 
COLLECT_PARAMETERS
Returns database parameters relating to index rebuild dbms_i_index_utl.collect_parameters(
parameters OUT VARCHAR2 dbms_i_index_utl.parametername_array,
paramvals  OUT VARCHAR2 dbms_i_index_utl.parameterval_array,
events     OUT NUMBER   dbms_i_index_utl.number_array,
eventvals  OUT NUMBER   dbms_i_index_utl.number_array);
conn sys@pdbdev as sysdba

DECLARE
 pna dbms_i_index_utl.parametername_array;
 pva dbms_i_index_utl.parameterval_array;
 eva dbms_i_index_utl.number_array;
 evl dbms_i_index_utl.number_array;
BEGIN
  dbms_i_index_utl.collect_parameters(pna, pva, eva, evl);

  FOR i IN 1 .. pna.COUNT LOOP
    dbms_output.put_line(pna(i));
    dbms_output.put_line(pva(i));
  END LOOP;
END;
/


-- eva and evl are returned as NULL arrays
 
COMPUTE_ORDER
Undocumented dbms_i_index_utl.compute_order(
data       IN  dbms_i_index_utl.number_array,
sort_order OUT dbms_i_index_utl.number_array);
TBD
 
DROP_IDX_JOB
Undocumented dbms_i_index_utl.drop_idx_job(jobname IN VARCHAR2);
TBD
 
FIX_QUOTES
Undocumented dbms_i_index_utl.fix_quotes(instr IN VARCHAR2) RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

SELECT dbms_i_index_utl.fix_quotes('UWCLASS.SERVERS') FROM dual;

SELECT dbms_i_index_utl.fix_quotes('"UWCLASS.SERVERS"') FROM dual;
 
GET_DOM_IDX_PARAM_STR
Undocumented dbms_i_index_utl.get_dom_idx_param_str(
index_name     IN VARCHAR2,
idx_owner_name IN VARCHAR2,
cname          IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_REBUILD_COMMAND
Constructs the command to rebuild an index dbms_i_index_utl.get_rebuild_command(
ctype  IN VARCHAR2,  -- G=global, L=local
iowner IN VARCHAR2,
iname  IN VARCHAR2,
cname  IN VARCHAR2)
RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
 oput VARCHAR2(1000);
BEGIN
  oput := dbms_i_index_utl.get_rebuild_command('G', 'UWCLASS', 'PK_SERVER', 'PK_SERVER');

  dbms_output.put_line(oput);
END;
/

conn sh/sh@pdbdev

set serveroutput on

DECLARE
 oput VARCHAR2(1000);
BEGIN
  oput := dbms_i_index_utl.get_rebuild_command('L', 'SH', 'COSTS_TIME_BIX', 'COSTS_Q4_2001');

  dbms_output.put_line(oput);
END;
/
 
IS_DOMAIN_INDEX
Returns the object_id if the object is a domain index: Otherwise 0 dbms_i_index_utl.is_domain_index(
index_name     IN VARCHAR2,
idx_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba

SELECT index_name, index_type
FROM dba_indexes
WHERE owner = 'SH'

SELECT dbms_i_index_utl.is_domain_index('COST_PROD_BIX', 'SH')
FROM dual;

SELECT dbms_i_index_utl.is_domain_index('SUP_TEXT_IDX', 'SH')
FROM dual;
 
I_BUILD_INDEXES (new 12.1 parameters)
Undocumented dbms_i_index_utl.i_build_indexes(
ctypes         IN     dbms_i_index_utl.symbol_array,
iowners        IN     dbms_i_index_utl.name_array,
inames         IN     dbms_i_index_utl.name_array,
cnames         IN     dbms_i_index_utl.name_array,
degrees        IN     dbms_i_index_utl.number_array,
rowcnts        IN     dbms_i_index_utl.number_array,
join_idx_flags IN     dbms_i_index_utl.symbol_array,
concurrent     IN     BOOLEAN,
cont_after_err IN     BOOLEAN,
maxdop         IN     BINARY_INTEGER,
num_errors     IN OUT BINARY_INTEGER,
retry_online   IN     BOOLEAN);
TBD
 
PACK_PARAMETERS
Undocumented dbms_i_index_utl.pack_parameters(
env_pipe   IN VARCHAR2 PL/SQL TABLE,
parameters IN VARCHAR2 dbms_i_index_utl.parametername_array,
paramvals  IN VARCHAR2 dbms_i_index_utl.parametername_array,
events     IN NUMBER   dbms_i_index_utl.number_array,
eventvals  IN NUMBER   dbms_i_index_utl.number_array);
TBD
 
REBUILD_INDEX (new 12.1 parameters)
Undocumented dbms_i_index_utl.rebuild_index(
pipe    IN VARCHAR2,
jobno   IN BINARY_INTEGER,
ctype   IN VARCHAR2,
iowner  IN VARCHAR2,
iname   IN VARCHAR2,
cname   IN VARCHAR2,
mvidx   IN BINARY_INTEGER,
rscs    IN BINARY_INTEGER,
ronline IN BOOLEAN);
TBD
 
REBUILD_INDEX_LIST
Undocumented dbms_i_index_utl.rebuild_index_list(
inpipe         IN VARCHAR2,
jobid          IN BINARY_INTEGER,
outpipe        IN VARCHAR2,
cont_after_err IN BINARY_INTEGER,
degree         IN BINARY_INTEGER);
TBD
 
REMOVE_PARAMETER_PIPES
Undocumented dbms_i_index_utl.remove_parameter_pipes(pipe IN VARCHAR2);
TBD
 
SUBMIT_IDX_REBUILD_JOB
Undocumented dbms_i_index_utl.submit_idx_rebuild_job(
sched_class   IN  VARCHAR2,
jobname       IN  VARCHAR2,
jobaction     IN  VARCHAR2,
jobcomment    IN  VARCHAR2,
this_inst     IN  BINARY_INTEGER,
jobno         OUT BINARY_INTEGER,
submit_as_job IN  BOOLEAN);
TBD
 
UNPACK_PARAMETERS
Undocumented dbms_i_index_utl.unpack_parameters(env_pipe IN VARCHAR2);
TBD
 
VERIFY_IDX_COMP
Undocumented dbms_i_index_utl.verify_idx_comp(
comp_name      IN VARCHAR2,
idx_name       IN VARCHAR2,
idx_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER;
conn uwclass/uwclass@pdbdev

CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id       NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date     DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')),
PARTITION yr9 VALUES LESS THAN (MAXVALUE));

CREATE INDEX ix_range_part_phid
ON range_part(prof_history_id)
LOCAL;

conn / as sysdba

SELECT dbms_i_index_utl.verify_idx_comp('YR0', 'IX_RANGE_PART_PHID', 'UWCLASS')
FROM dual;
 
VERIFY_INDEX
Returns the OBJECT_ID for an index or ORA-20001 if it does not exist dbms_i_index_utl.verify_index(
index_name     IN VARCHAR2,
idx_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER;
conn uwclass/uwclass@pdbdev

SELECT object_id
FROM dba_objects_ae
WHERE object_name = 'PK_SERVERS'
AND object_type = 'INDEX';

SELECT dbms_i_index_utl.verify_index('PK_SERVERS', 'UWCLASS')
FROM dual;

SELECT dbms_i_index_utl.verify_index('KP_SERVERS', 'UWCLASS')
FROM dual;
 
VERIFY_OWNER
Returns the USER_ID for an schema owner otherwise an exception dbms_i_index_utl.verify_owner(owner_name IN VARCHAR2) RETURN BINARY_INTEGER;
conn uwclass/uwclass@pdbdev

SELECT user_id
FROM dba_users
WHERE username = 'UWCLASS';

SELECT dbms_i_index_utl.verify_owner('UWCLASS') FROM dual;

SELECT dbms_i_index_utl.verify_owner('UWCLASZ') FROM dual;
 
VERIFY_TABLE
Returns the OBJECT_ID for a table otherwise an exception dbms_i_index_utl.verify_table(
table_name  IN VARCHAR2,
table_owner IN VARCHAR2)
RETURN BINARY_INTEGER;
conn uwclass/uwclass@pdbdev

SELECT object_id
FROM dba_objects_ae
WHERE object_name = 'SERVERS'
AND object_type = 'TABLE';

SELECT dbms_i_index_utl.verify_table('SERVERS', 'UWCLASS')
FROM dual;

SELECT dbms_i_index_utl.verify_table('SERVERZ', 'UWCLASS')
FROM dual;
 
VERIFY_TAB_COMP
Returns the object identifier for a table component dbms_i_index_utl.verify_tab_comp(
comp_name      IN VARCHAR2,
table_name     IN VARCHAR2,
tab_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER;
conn uwclass/uwclass@pdbdev

CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id       NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date     DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')),
PARTITION yr9 VALUES LESS THAN (MAXVALUE));

conn / as sysdba

SELECT dbms_i_index_utl.verify_tab_comp('YR0', 'RANGE_PART', 'UWCLASS')
FROM dual;

SELECT dbms_i_index_utl.verify_tab_comp('YR2', 'RANGE_PART', 'UWCLASS')
FROM dual;

SELECT dbms_i_index_utl.verify_tab_comp('YR8', 'RANGE_PART', 'UWCLASS')
FROM dual;

Related Topics
Built-in Functions
Built-in Packages
DBMS_INDEX_UTL
DBMS_PCLXUTIL
Indexes
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved