"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
APIs to build partitioned and non-partitioned indexes with parallel slaves.
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,);
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';
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);
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 = PDBDEV;
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;
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';
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);
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);