Oracle | DBMS_I_INDEX

Oracle DBMS_I_INDEX_UTL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Internal utilities for verifying and rebuilding indexes and index components

AUTHID DEFINER

Data Types NAME_ARRAY NUMBER_ARRAY PARAMETERNAME_ARRAY PARAMETERVAL_ARRAY SYMBOL_ARRAY

Dependencies

DBA_SCHEDULER_JOBS	DBMS_SNAPSHOT_LIB	JOB_DEFINITION_ARRAY
DBMS_ASSERT	DBMS_STANDARD	OBJ$
DBMS_EXPORT_EXTENSION	DBMS_SYSTEM	PLITBLM
DBMS_INDEX_UTL	DBMS_SYS_ERROR	USER$
DBMS_ISCHED	DUAL	V$INSTANCE
DBMS_JOB	IDX_RB$JOBSEQ	V$PARAMETER
DBMS_PIPE	INDEX_STATS	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 10.1

Security Model Owned by SYS with no privileges granted

Source $ORACLE_HOME/rdbms/admin/prvtidxu.plb

Subprograms

COLLECT_PARAMETERS	I_ANALYZE_INDEX	SUBMIT_IDX_REBUILD_JOB
COMPUTE_ORDER	I_BUILD_INDEXES	UNPACK_PARAMETERS
DROP_IDX_JOB	I_COMPUTE_ACTION	VERIFY_IDX_COMP
FIX_QUOTES	PACK_PARAMETERS	VERIFY_INDEX
GET_DOM_IDX_PARAM_STR	REBUILD_INDEX	VERIFY_OWNER
GET_REBUILD_COMMAND	REBUILD_INDEX_LIST	VERIFY_TABLE
IS_DOMAIN_INDEX	REMOVE_PARAMETER_PIPES	VERIFY_TAB_COMP

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_ANALYZE_INDEX (new 21c)

Undocumented dbms_i_index_utl.i_analyze_index( index_owner IN VARCHAR2, index_name IN VARCHAR2, index_comp IN VARCHAR2, index_subcomp IN VARCHAR2, sample_pct IN BINARY_INTEGER);

TBD

I_BUILD_INDEXES

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

I_COMPUTE_ACTION (new 21c)

Undocumented dbms_i_index_utl.i_compute_action( shrink_pct IN BINARY_INTEGER, rebuild_pct IN BINARY_INTEGER, reorg_action OUT BINARY_INTEGER);

DECLARE

       outVal BINARY_INTEGER;

      BEGIN

        dbms_i_index_utl.i_compute_action(10, 95, outVal);

        dbms_output.put_line(outVal);

      END;

      /

      1

      

      PL/SQL procedure successfully completed.

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, 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;