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 support for the DBMS_PCLXUTIL package supporting local partition index creation.
AUTHID
DEFINER
Data Types
TYPE JobList IS TABLE OF NUMBER;
Dependencies
DBA_IND_PARTITIONS
DBMS_ASSERT
DBMS_STANDARD
DBA_IND_SUBPARTITIONS
DBMS_LOCK
DUAL
DBA_SCHEDULER_JOBS
DBMS_OUTPUT
PCLX_JOBSEQ
DBA_TABLES
DBMS_PCLXUTIL
PLITBLM
DBA_TAB_PARTITIONS
DBMS_SCHEDULER
V$PARAMETER
Documented
No
First Available
12.2
Security Model
Owned by SYS with no privilege granted
Source
{ORACLE_HOME}/rdbms/admin/prvthpclxi.plb
BUILD_PART_INDEX
Build local partition index
dbms_pclxutil.build_part_index (
jobs_per_batch IN NUMBER DEFAULT 1,
procs_per_job IN NUMBER DEFAULT 1,
tab_name IN VARCHAR2 DEFAULT NULL,
idx_name IN VARCHAR2 DEFAULT NULL,
force_opt IN BOOLEAN DEFAULT FALSE,
curr_user IN VARCHAR2);
jobs_per_batch -- number of partitions
procs_per_batch -- degree <= max_slaves
force_opt -- If true forces rebuild of all indexes.
-- If false rebuild only of index marked UNUSABLE.
-- create tablespace DDL on Partitions page
CREATE TABLE prof_hist (
prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL,
prof_hist_comments VARCHAR2(2000))
PARTITION BY HASH (prof_history_id)
PARTITIONS 3
STORE IN (part1, part2, part3);
SELECT table_name, partition_name
FROM user_tab_partitions;
CREATE INDEX ix_prof_hist
ON prof_hist(prof_history_id)
LOCAL
UNUSABLE;
-- causes the dictionary entries to be created without "building" the index itself, the time consuming part of creating an index
desc user_ind_partitions
SELECT index_name, partition_name, status
FROM user_ind_partitions;
-- causes a concurrent build of local indexes with the specified degree of parallelism exec dbms_pclxutil_internal.build_part_index(3, 1, 'PROF_HIST', 'IX_PROF_HIST', TRUE, USER);
SELECT index_name, partition_name, status
FROM user_ind_partitions;
