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_I_INDEX_UTL
Version 11.1
 
General
Note: For moving the OLAP catalog
Source $ORACLE_HOME/rdbms/admin/prvtidxu.plb
First Available 10.1
Dependencies
DBA_SCHEDULER_JOBS DBMS_SCHEDULER IDX_RB$JOBSEQ
DBMS_ASSERT DBMS_SNAPSHOT IND$
DBMS_EXPORT_EXTENSION DBMS_SYSTEM INDPART_PARAM$
DBMS_INDEX_UTL DBMS_SYS_ERROR OBJ$
DBMS_ISCHED DUAL PLITBLM
DBMS_JOB GV$DB_PIPES USER$
DBMS_ODCI GV$INSTANCE X$KSPPCV
DBMS_PIPE GV$PARAMETER X$KSPPI
Exceptions
Number Name
ORA-20001 Index <index_name> does not exist in schema <schema_name>.
or
User <schema_name> does not exist.
 
COLLECT_PARAMETERS
Undocumented 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); 
TBD
 
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
-- do nothing

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 (new 11g)
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 (new 11g)

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; 
TBD
 
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
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
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

Undocumented
dbms_i_index_utl.verify_tab_comp(
comp_name      IN VARCHAR2,
table_name     IN VARCHAR2,
tab_owner_name IN VARCHAR2)
RETURN BINARY_INTEGER;
SELECT comp_name FROM dba_registry ORDER BY 1;

COMP_NAME
----------------------------------
JServer JAVA Virtual Machine
OLAP Analytic Workspace
OLAP Catalog
Oracle Data Mining
Oracle Database Catalog Views
Oracle Database Java Packages
Oracle Database Packages and Types
Oracle Enterprise Manager
Oracle Expression Filter
Oracle OLAP API
Oracle Rules Manager
Oracle Text
Oracle Workspace Manager
Oracle XDK
Oracle XML Database
Oracle interMedia
Spatial

SELECT dbms_i_index_utl.verify_tab_comp('OLAP Catalog', 'SERVERS', 'UWCLASS')
FROM dual;

ERROR at line 1:
ORA-20001: Component "OLAP Catalog" does not exist in table "SERVERS" in schema "UWCLASS".
ORA-06512: at "SYS.DBMS_I_INDEX_UTL", line 779
 

Related Topics

DBMS_INDEX_UTL

DBMS_PCLXUTIL

Indexes

 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [303 users online]    © 2010 psoug.org