CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_INDEX_UTL
Version 11.1
 
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  
 
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
 
Related Topics
DBMS_I_INDEX_UTL
DBMS_PCLXUTIL
Indexes
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [187 users online]    © 2010 psoug.org