Oracle DBMS_I_INDEX_UTL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_ODCI IDX_RB$JOBSEQ V_$DB_PIPES
DBMS_PIPE IND$ X$KSPPCV
DBMS_SCHEDULER INDPART_PARAM$ X$KSPPI
DBMS_SNAPSHOT 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@pdborcl

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@pdborcl 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
Undocumented dbms_i_index_utl.i_build_indexes(
ctypes         IN     CHAR     dbms_i_index_utl.symbol_array,
iowners        IN     VARCHAR2 dbms_i_index_utl.name_array,
inames         IN     VARCHAR2 dbms_i_index_utl.name_array,
cnames         IN     VARCHAR2 dbms_i_index_utl.name_array,
degrees        IN     NUMBER   dbms_i_index_utl.number_array,
rowcnts        IN     NUMBER   dbms_i_index_utl.number_array,
concurrent     IN     BOOLEAN,
cont_after_err IN     BOOLEAN,
maxdop         IN     BINARY_INTEGER,
num_errors     IN OUT BINARY_INTEGER);
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
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);
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-2013','DD-MON-YYYY')),
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')),
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2015','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-2013','DD-MON-YYYY')),
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')),
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2015','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
DBMS_INDEX_UTL
DBMS_PCLXUTIL
Indexes
Packages

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-2014 Daniel A. Morgan All Rights Reserved