| General Information |
| Note: For moving the OLAP catalog |
| Source |
$ORACLE_HOME/rdbms/admin/prvtidxu.plb |
| First Available |
10.1 |
| Dependencies |
| DBA_SCHEDULER_JOBS |
DBMS_SQL |
JOB_DEFINITION_ARRAY |
| DBMS_ASSERT |
DBMS_SYSTEM |
OBJ$ |
| DBMS_EXPORT_EXTENSION |
DBMS_SYS_ERROR |
PLITBLM |
| DBMS_INDEX_UTL |
DBMS_SYS_SQL |
USER$ |
| DBMS_ISCHED |
DUAL |
V$DB_PIPES |
| DBMS_JOB |
GV$DB_PIPES |
V$INSTANCE |
| DBMS_ODCI |
IDX_RB$JOBSEQ |
V$PARAMETER |
| DBMS_PIPE |
IDX_RB$JOBSEQ |
X$KSPPCV |
| DBMS_SCHEDULER |
INDPART_PARAM$ |
X$KSPPI |
| DBMS_SNAPSHOT |
JOB_DEFINITION |
|
|
| Exceptions |
| Number |
Name |
| ORA-20001 |
Index <index_name> does not exist in schema <schema_name>.
or
User <schema_name> does not exist. |
|
| Security Model |
Owned by SYS with no granted privileges |
| Subprograms |
|
| |
| COLLECT_PARAMETERS |
| Returns database parameters relating to index rebuild |
dbms_i_index_utl.collect_parameters(
parameters OUT VARCHAR2 dbms_i_index_utl.parametername_array,
paramvals OUT VARCHAR2 dbms_i_index_utl.parameterval_array,
events OUT NUMBER dbms_i_index_utl.number_array,
eventvals OUT NUMBER dbms_i_index_utl.number_array); |
conn / as sysdba
DECLARE
pna dbms_i_index_utl.parametername_array;
pva dbms_i_index_utl.parameterval_array;
eva dbms_i_index_utl.number_array;
evl dbms_i_index_utl.number_array;
BEGIN
dbms_i_index_utl.collect_parameters(pna, pva, eva, evl);
FOR i IN 1 .. pna.COUNT LOOP
dbms_output.put_line(pna(i));
dbms_output.put_line(pva(i));
END LOOP;
END;
/
-- eva and evl are returned as NULL arrays |
| |
| DROP_IDX_JOB |
| Undocumented |
dbms_i_index_utl.drop_idx_job(jobname IN VARCHAR2); |
| TBD |
| |
| FIX_QUOTES |
| Undocumented |
dbms_i_index_utl.fix_quotes(instr IN VARCHAR2) RETURN VARCHAR2; |
SELECT dbms_i_index_utl.fix_quotes('UWCLASS.SERVERS') FROM dual;
SELECT dbms_i_index_utl.fix_quotes('"UWCLASS.SERVERS"') FROM dual; |
| |
| GET_DOM_IDX_PARAM_STR |
| Undocumented |
dbms_i_index_utl.get_dom_idx_param_str(
index_name IN VARCHAR2,
idx_owner_name IN VARCHAR2,
cname IN VARCHAR2)
RETURN VARCHAR2; |
| TBD |
| |
| GET_REBUILD_COMMAND |
| Constructs the command to rebuild an index |
dbms_i_index_utl.get_rebuild_command(
ctype IN VARCHAR2, -- G=global, L=local
iowner IN VARCHAR2,
iname IN VARCHAR2,
cname IN VARCHAR2)
RETURN VARCHAR2; |
conn uwclass/uwclass
set serveroutput on
DECLARE
oput VARCHAR2(1000);
BEGIN
oput := dbms_i_index_utl.get_rebuild_command('G', 'UWCLASS', 'PK_SERVER', 'PK_SERVER');
dbms_output.put_line(oput);
END;
/
conn sh/sh
set serveroutput on
DECLARE
oput VARCHAR2(1000);
BEGIN
oput := dbms_i_index_utl.get_rebuild_command('L', 'SH', 'COSTS_TIME_BIX', 'COSTS_Q4_2001');
dbms_output.put_line(oput);
END;
/ |
| |
| IS_DOMAIN_INDEX |
| Returns the object_id if the object is a domain index: Otherwise returns 0. |
dbms_i_index_utl.is_domain_index(
index_name IN VARCHAR2,
idx_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER |
conn / as sysdba
SELECT index_name, index_type
FROM dba_indexes
WHERE owner = 'SH'
SELECT dbms_i_index_utl.is_domain_index('COST_PROD_BIX', 'SH')
FROM dual;
SELECT dbms_i_index_utl.is_domain_index('SUP_TEXT_IDX', 'SH')
FROM dual; |
| |
| I_BUILD_INDEXES |
| Undocumented |
dbms_i_index_utl.i_build_indexes(
ctypes IN CHAR dbms_i_index_utl.symbol_array,
iowners IN VARCHAR2 dbms_i_index_utl.name_array,
inames IN VARCHAR2 dbms_i_index_utl.name_array,
cnames IN VARCHAR2 dbms_i_index_utl.name_array,
degrees IN NUMBER dbms_i_index_utl.number_array,
rowcnts IN NUMBER dbms_i_index_utl.number_array,
concurrent IN BOOLEAN,
cont_after_err IN BOOLEAN,
maxdop IN BINARY_INTEGER,
num_errors IN OUT BINARY_INTEGER); |
| TBD |
| |
| PACK_PARAMETERS |
| Undocumented |
dbms_i_index_utl.pack_parameters(
env_pipe IN VARCHAR2 PL/SQL TABLE,
parameters IN VARCHAR2 dbms_i_index_utl.parametername_array,
paramvals IN VARCHAR2 dbms_i_index_utl.parametername_array,
events IN NUMBER dbms_i_index_utl.number_array,
eventvals IN NUMBER dbms_i_index_utl.number_array); |
| TBD |
| |
| REBUILD_INDEX |
| Undocumented |
dbms_i_index_utl.rebuild_index(
pipe IN VARCHAR2,
jobno IN BINARY_INTEGER,
ctype IN VARCHAR2,
iowner IN VARCHAR2,
iname IN VARCHAR2,
cname IN VARCHAR2,
mvidx IN BINARY_INTEGER,
rscs IN BINARY_INTEGER); |
| TBD |
| |
| REBUILD_INDEX_LIST |
| Undocumented |
dbms_i_index_utl.rebuild_index_list(
inpipe IN VARCHAR2,
jobid IN BINARY_INTEGER,
outpipe IN VARCHAR2,
cont_after_err IN BINARY_INTEGER,
degree IN BINARY_INTEGER); |
| TBD |
| |
| REMOVE_PARAMETER_PIPES |
| Undocumented |
dbms_i_index_utl.remove_parameter_pipes(pipe IN VARCHAR2); |
| TBD |
| |
| SUBMIT_IDX_REBUILD_JOB |
| Undocumented |
dbms_i_index_utl.submit_idx_rebuild_job(
sched_class IN VARCHAR2,
jobname IN VARCHAR2,
jobaction IN VARCHAR2,
jobcomment IN VARCHAR2,
this_inst IN BINARY_INTEGER,
jobno OUT BINARY_INTEGER,
submit_as_job IN BOOLEAN); |
| TBD |
| |
| UNPACK_PARAMETERS |
| Undocumented |
dbms_i_index_utl.unpack_parameters(env_pipe IN VARCHAR2); |
| TBD |
| |
| VERIFY_IDX_COMP |
| Undocumented |
dbms_i_index_utl.verify_idx_comp(
comp_name IN VARCHAR2,
idx_name IN VARCHAR2,
idx_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER; |
CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
PARTITION yr9 VALUES LESS THAN (MAXVALUE));
CREATE INDEX ix_range_part_phid
ON range_part(prof_history_id)
LOCAL;
conn / as sysdba
SELECT dbms_i_index_utl.verify_idx_comp('YR0', 'IX_RANGE_PART_PHID', 'UWCLASS')
FROM dual; |
| |
| VERIFY_INDEX |
| Returns the OBJECT_ID for an index or ORA-20001 if it does not exist |
dbms_i_index_utl.verify_index(
index_name IN VARCHAR2,
idx_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER; |
SELECT object_id
FROM dba_objects_ae
WHERE object_name = 'PK_SERVERS'
AND object_type = 'INDEX';
SELECT dbms_i_index_utl.verify_index('PK_SERVERS', 'UWCLASS')
FROM dual;
SELECT dbms_i_index_utl.verify_index('KP_SERVERS', 'UWCLASS')
FROM dual; |
| |
| VERIFY_OWNER |
| Returns the USER_ID for an schema owner or ORA-20001 if it does not exist |
dbms_i_index_utl.verify_owner(owner_name IN VARCHAR2) RETURN BINARY_INTEGER; |
SELECT user_id
FROM dba_users
WHERE username = 'UWCLASS';
SELECT dbms_i_index_utl.verify_owner('UWCLASS') FROM dual;
SELECT dbms_i_index_utl.verify_owner('UWCLASZ') FROM dual; |
| |
| VERIFY_TABLE |
| Returns the OBJECT_ID for a table or ORA-20001 if it does not exist |
dbms_i_index_utl.verify_table(
table_name IN VARCHAR2,
table_owner IN VARCHAR2)
RETURN BINARY_INTEGER; |
SELECT object_id
FROM dba_objects_ae
WHERE object_name = 'SERVERS'
AND object_type = 'TABLE';
SELECT dbms_i_index_utl.verify_table('SERVERS', 'UWCLASS')
FROM dual;
SELECT dbms_i_index_utl.verify_table('SERVERZ', 'UWCLASS')
FROM dual; |
| |
| VERIFY_TAB_COMP |
| Returns the object identifier for a table component |
dbms_i_index_utl.verify_tab_comp(
comp_name IN VARCHAR2,
table_name IN VARCHAR2,
tab_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER; |
CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
PARTITION yr9 VALUES LESS THAN (MAXVALUE));
conn / as sysdba
SELECT dbms_i_index_utl.verify_tab_comp('YR0', 'RANGE_PART', 'UWCLASS')
FROM dual;
SELECT dbms_i_index_utl.verify_tab_comp('YR2', 'RANGE_PART', 'UWCLASS')
FROM dual;
SELECT dbms_i_index_utl.verify_tab_comp('YR8', 'RANGE_PART', 'UWCLASS')
FROM dual; |
