Oracle DBMS_INDEX_UTL
Version 12.1.0.2

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 APIs to build partitioned and non-partitioned indexes with parallel slaves.
AUTHID CURRENT_USER
Dependencies
DBMS_I_INDEX_UTL DBMS_STANDARD DBMS_UTILITY
DBMS_SNAPSHOT DBMS_SYSTEM PLITBLM
Documented No
First Available 10gR1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsidxu.sql
Subprograms
 
BUILD_INDEXES (new 12.1 parameters)
Provides an interface for finding and concurrently, and in parallel, rebuild all the index components (including entire indexes, index partitions, and index subpartitions) for a list of indexes

Overload 1
dbms_index_utl.build_indexes(
list           IN  VARCHAR2 DEFAULT NULL,
just_unusable  IN  BOOLEAN  DEFAULT TRUE,
locality       IN  VARCHAR2 DEFAULT 'ALL',
concurrent     IN  BOOLEAN  DEFAULT TRUE,
cont_after_err IN  BOOLEAN  DEFAULT FALSE,
max_slaves     IN  INT      DEFAULT NULL,
forced_degree  IN  INT      DEFAULT NULL,
retry_online   IN  BOOLEAN  DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);
SELECT index_name
FROM user_indexes;

set serveroutput on

DECLARE
 x PLS_INTEGER;
BEGIN
  dbms_index_utl.build_indexes('UWCLASS.PK_SERV_INST,
  UWCLASS.IX_SERV_INST', FALSE, 'ALL', FALSE, FALSE, 2, x);

  dbms_output.put_line('Failed to rebuild: ' || TO_CHAR(x));
END;
/
Overload 2 dbms_index_utl.build_indexes(
list           IN VARCHAR2 DEFAULT NULL,
just_unusable  IN BOOLEAN  DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN  DEFAULT TRUE,
cont_after_err IN BOOLEAN  DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree  IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN  DEFAULT FALSE,);
SELECT index_name
FROM user_indexes;

exec dbms_index_utl.build_indexes('UWCLASS.PK_SERV_INST, UWCLASS.IX_SERV_INST', FALSE, 'ALL', FALSE, FALSE, 2);
 
BUILD_INDEX_COMPONENTS (new 12.1 parameters)
Provide an interface for finding and concurrently, and in parallel, rebuilding a given list of index components (partitions and subparts). This will not rebuild entire indexes

Overload 1
dbms_index_utl.build_index_components(
list           IN  VARCHAR2 DEFAULT NULL, -- <owner>.<index>.<comp>)
just_unusable  IN  BOOLEAN  DEFAULT TRUE,
locality       IN  VARCHAR2 DEFAULT 'ALL',
concurrent     IN  BOOLEAN  DEFAULT TRUE,
cont_after_err IN  BOOLEAN  DEFAULT FALSE,
max_slaves     IN  INT      DEFAULT NULL,
forced_degree  IN  INT      DEFAULT NULL,
retry_online   IN  BOOLEAN  DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);
See Demo for Overload 2 Below
Overload 2 dbms_index_utl.build_index_components(
list           IN VARCHAR2 DEFAULT NULL,
just_unusable  IN BOOLEAN  DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN  DEFAULT TRUE,
cont_after_err IN BOOLEAN  DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree  IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN  DEFAULT FALSE);
SQL> SELECT index_name
   2 FROM dba_indexes
   3 WHERE owner = 'SH'
   4 AND table_name = 'SALES';

INDEX_NAME
------------------------------
SALES_TIME_BIX
SALES_CUST_BIX
SALES_PROD_BIX
SALES_PROMO_BIX
SALES_CHANNEL_BIX

DECLARE
 complist VARCHAR2(128) := 'SH.SALES_TIME_BIX.SALES_Q1_2002,
                            SH.SALES_TIME_BIX.SALES_Q1_2003';
BEGIN
  dbms_index_utl.build_index_components(complist);
END;
/
 
BUILD_SCHEMA_INDEXES (new 12.1 parameters)
Provides an interface for finding and concurrently and rebuilding, in parallel, all the index components (including entire indexes, index partitions, and index subpartitions) for a list of schemas

Overload 1
dbms_index_utl.build_schema_indexes(
list           IN  VARCHAR2 DEFAULT NULL,  -- comma delimited schemas
just_unusable  IN  BOOLEAN  DEFAULT TRUE,  -- unusable only or all
locality       IN  VARCHAR2 DEFAULT 'ALL', -- GLOBAL | LOCAL | ALL
concurrent     IN  BOOLEAN  DEFAULT TRUE,  -- if TRUE uses Jobs
cont_after_err IN  BOOLEAN  DEFAULT FALSE, -- TRUE | FALSE
max_slaves     IN  INT      DEFAULT NULL,  -- degree of parallelism
forced_degree  IN  INT      DEFAULT NULL,
retry_online   IN  BOOLEAN  DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);    -- number of failures
set serveroutput on

DECLARE
 x PLS_INTEGER;
BEGIN
  dbms_index_utl.build_schema_indexes('UWCLASS', FALSE, 'ALL', FALSE,
  FALSE, 2, x);

  dbms_output.put_line('Failed to rebuild: ' || TO_CHAR(x));
END;
/
Overload 2 dbms_index_utl.build_schema_indexes(
list           IN VARCHAR2 DEFAULT NULL,
just_unusable  IN BOOLEAN  DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN  DEFAULT TRUE,
cont_after_err IN BOOLEAN  DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree  IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN  DEFAULT FALSE);
exec dbms_index_utl.build_schema_indexes('UWCLASS', FALSE, 'ALL', FALSE, FALSE, 2);
 
BUILD_TABLE_COMPONENT_INDEXES (new 12.1 parameters)
Provide an interface for finding and concurrently, and in parallel rebuilding all the index components (including index partitions, and index subpartitions) for a given list of table components

Overload 1
dbms_index_utl.build_table_component_indexes(
list           IN  VARCHAR2 DEFAULT NULL,
just_unusable  IN  BOOLEAN  DEFAULT TRUE,
locality       IN  VARCHAR2 DEFAULT 'ALL',
concurrent     IN  BOOLEAN  DEFAULT TRUE,
cont_after_err IN  BOOLEAN  DEFAULT FALSE,
max_slaves     IN  INT      DEFAULT NULL,
forced_degree  IN  INT      DEFAULT NULL,
retry_online   IN  BOOLEAN  DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);
conn / as sysdba

ALTER SESSION SET CONTAINER = PDBORCL;

SQL> SELECT partition_name
2 FROM dba_tab_partitions
3 WHERE table_owner = 'SH'
4 AND table_name = 'SALES'
5 AND partition_name LIKE '%2000%'
6 ORDER BY 1;

PARTITION_NAME
-------------------------------------
SALES_Q1_2000
SALES_Q2_2000
SALES_Q3_2000
SALES_Q4_2000

DECLARE
 complist VARCHAR2(128);
 retval  PLS_INTEGER;
BEGIN
  complist := 'SH.SALES.SALES_Q1_2000, SH.SALES.SALES_Q2_2000';

  dbms_index_utl.build_table_component_indexes(complist, num_errors=>retval);
  -- note that this does not print if no exception occurs
  dbms_output.put_line('NUM_ERRORS: ' || TO_CHAR(retval));
END;
/
Overload 2 dbms_index_utl.build_table_component_indexes(
list           IN VARCHAR2 DEFAULT NULL,
just_unusable  IN BOOLEAN  DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN  DEFAULT TRUE,
cont_after_err IN BOOLEAN  DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree  IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN  DEFAULT FALSE);
DECLARE
 complist VARCHAR2(128);
BEGIN
  complist := 'SH.SALES.SALES_Q1_2000, SH.SALES.SALES_Q2_2000';

  dbms_index_utl.build_table_component_indexes(complist, FALSE, 'ALL');
END;
/
 
BUILD_TABLE_INDEXES (new 12.1 parameters)
Provide an interface for finding and concurrently rebuilding, in parallel, all the index components (including entire indexes, index partitions, and index subpartitions) for a list of tables

Overload 1
dbms_index_utl.build_table_indexes (
list           IN  VARCHAR2 DEFAULT NULL,  -- list of tables
just_unusable  IN  BOOLEAN  DEFAULT TRUE,
locality       IN  VARCHAR2 DEFAULT 'ALL',
concurrent     IN  BOOLEAN  DEFAULT TRUE,
cont_after_err IN  BOOLEAN  DEFAULT FALSE,
max_slaves     IN  INT      DEFAULT NULL,
forced_degree  IN  INT      DEFAULT NULL,
retry_online   IN  BOOLEAN  DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);
set serveroutput on

DECLARE
 x PLS_INTEGER;
BEGIN
  dbms_index_utl.build_table_indexes ('UWCLASS.SERVERS,
  UWCLASS.AIRPLANES', FALSE, 'ALL', FALSE, FALSE, 2, x);

  dbms_output.put_line('Failed to rebuild: ' || TO_CHAR(x));
END;
/
Overload 2 dbms_index_utl.build_table_indexes (
list           IN VARCHAR2 DEFAULT NULL,
just_unusable  IN BOOLEAN  DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN  DEFAULT TRUE,
cont_after_err IN BOOLEAN  DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree  IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN  DEFAULT FALSE,
BEGIN
  dbms_index_utl.build_table_indexes('UWCLASS.SERVERS, UWCLASS.AIRPLANES', FALSE, 'ALL', FALSE, FALSE, 2);
 
MULTI_LEVEL_BUILD (new 12.1 parameters)
Provide an interface for finding and concurrently, and in parallel, rebuilding all the index components (including index partitions, and index subpartitions) for several lists of schema objects. This function has an advantage over others in that it provides the maximum amount of concurrency possible by removing serialization forced by making sequential calls to different rebuild functions

Overload 1
dbms_index_utl.multi_level_build (
schema_list    IN  VARCHAR2 DEFAULT NULL,
table_list     IN  VARCHAR2 DEFAULT NULL,
index_list     IN  VARCHAR2 DEFAULT NULL,
idx_comp_list  IN  VARCHAR2 DEFAULT NULL,
tab_comp_list  IN  VARCHAR2 DEFAULT NULL,
just_unusable  IN  BOOLEAN  DEFAULT TRUE,
locality       IN  VARCHAR2 DEFAULT 'ALL',
concurrent     IN  BOOLEAN  DEFAULT TRUE,
cont_after_err IN  BOOLEAN  DEFAULT FALSE,
max_slaves     IN  INT      DEFAULT NULL,
forced_degree  IN  INT      DEFAULT NULL,
retry_online   IN  BOOLEAN  DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);
TBD
Overload 2 dbms_index_utl.multi_level_build (
schema_list    IN VARCHAR2 DEFAULT NULL,
table_list     IN VARCHAR2 DEFAULT NULL,
index_list     IN VARCHAR2 DEFAULT NULL,
idx_comp_list  IN VARCHAR2 DEFAULT NULL,
tab_comp_list  IN VARCHAR2 DEFAULT NULL,
just_unusable  IN BOOLEAN  DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN  DEFAULT TRUE,
cont_after_err IN BOOLEAN  DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree  IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN  DEFAULT FALSE);
TBD

Related Topics
DBMS_I_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