| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsidxu.sql |
| First Available |
10.1 |
| Dependencies |
| DBMS_I_INDEX_UTL |
DBMS_SYSTEM |
PLITBLM |
| DBMS_SNAPSHOT |
DBMS_UTILITY |
V$SES_OPTIMIZER_ENV |
|
| Security Model |
Execute is granted to PUBLIC |
| Subprograms |
|
| |
| BUILD_INDEXES |
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,
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,); |
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 |
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,
num_errors OUT NOCOPY PLS_INTEGER); |
| TBD |
| 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); |
| TBD |
| |
| BUILD_SCHEMA_INDEXES |
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
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); |
| exec dbms_index_utl.build_schema_indexes('UWCLASS', FALSE, 'ALL', FALSE, FALSE, 2); |
| |
| BUILD_TABLE_COMPONENT_INDEXES |
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,
num_errors OUT NOCOPY PLS_INTEGER); |
| TBD |
| 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); |
| TBD |
| |
| BUILD_TABLE_INDEXES |
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,
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); |
BEGIN
dbms_index_utl.build_table_indexes('UWCLASS.SERVERS, UWCLASS.AIRPLANES', FALSE, 'ALL', FALSE, FALSE, 2); |
| |
| MULTI_LEVEL_BUILD |
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,
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); |
| TBD |
|
