CA ERwin Data Modeler Workgroup Edition Performance › Reindex the Database › Run the Oracle DBMS Reindex Script

Run the Oracle DBMS Reindex Script

As the new index is being built, it coexists with the old index in the database. For this reason, you should plan for enough space to store both the old index and the new index. When the index is rebuilt, the new index becomes available, the old index is dropped, and the space is reclaimed by the database. If you encounter any errors while rebuilding the indexes, re-run the statements. If you require more space to rebuild those specific indexes, add more storage to your index tablespace, and then try rebuilding those specific indexes again.

To run the Oracle DBMS reindex script

1. Create the mmreindex.ora script and copy it locally.
2. Edit the script and replace 'MODELMART' with the name of the mart schema-owner and 'MMINDEX' with the name of the mart index tablespace. Save your changes.
3. Connect to SQL*PLUS as the user SYS.
4. Grant the 'ALTER ANY INDEX' privilege to the mart Schema Owner.
5. Run your Oracle query tool and execute the following at the SQL prompt:

   GRANT ALTER ANY INDEX TO <MART SCHEMA OWNER>;

6. Disconnect user SYS and Connect to your Oracle query tool as the Schema Owner.
7. Execute the following Script at the SQL Prompt:

   @c:\mmreindex.ora

Example: Oracle Reindex Script (MMReIndex.ora)

    ---------------------------------------------------------------------------------- 
    -- Object:   MMReIndex.ora 
    -- Desc:     Use this Procedure to ReIndex the MM ORACLE Repository whenever a 
    --           Merge/Save of big model is done to MM 
    --           Limitation(s) is specific to Oracle Releases >= 817 
    -- For ORACLE DBMS < 8i Modify the script to Use NOPARALLEL 
    -- NOTE:  You will need to change MMOWNER to the Mart schema owner name. 
    --        You will need to change MMINDEX to the Mart index tablespace. 
    -- Oracle indexes are not self-balancing. They become fragmented after a large 
    -- number of INSERTs and DELETEs which may lead to significant performance degradation. 
    -- This script rebuilds the Mart indexes and cures them. 
    ------------------------------------------------------------------------------------- 
    set pagesize 1000 
    set linesize 2000 
    set verify off 
    set feedback off 
    set heading off 
    spool c:\mmreindex.ora 
    SELECT        'ALTER INDEX ' || USER || '.' || INDEX_NAME || 
    ' REBUILD PARALLEL NOLOGGING COMPUTE STATISTICS TABLESPACE MMINDEX;' 
    FROM   DBA_INDEXES 
    WHERE OWNER = UPPER ('MMOWNER') 
    AND    (INDEX_NAME like 'XPK%' or INDEX_NAME like 'XAK%' 
           or INDEX_NAME like 'XIE%') 
    order by index_name; 
    spool off 
    set heading on 
    set pagesize 24 
    set verify on 
    set feedback on 
    @c:\mmreindex.ora 
    /