Procedure documentationAltering Indexes with BR*Tools

 

You can use BR*Tools for Oracle to alter indexes as follows:

  • Coalesce index

  • Set degree of parallelism

  • Shrink indexes (Oracle 10g or higher)

  • Switch on index usage monitoring

  • Switch off index usage monitoring

Note Note

This section describes how you alter indexes with BR*Tools.

For more information on the approach to altering indexes, see Segment Management.

End of the note.

Procedure

  1. Start the procedure using BRGUI or BRTOOLS, or from the command line:

    • BRGUI or BRTOOLS:

      1. Choose Start of the navigation path Segment Management Next navigation step Alter indexes. End of the navigation path.

        BRGUI or BRTOOLS displays the menu BRSPACE options for alter indexes, where you specify the options with which you call BRSPACE.

      2. Set the required options:

        Menu Entry

        Equivalent BRSPACE

        Command Option

        BRSPACE profile (profile)

        -p|-profile

        Database user/password (user)

        -u|-user

        Alter index action (action)

        -f idalter -a|-action

        Tablespace names (tablespace)

        -f idalter -s|-tablespace

        Index owner (owner)

        -f idalter -o|-owner

        Table names (table)

        -f idalter -t|-table

        Index names (index)

        -f idalter -i|-index

        Confirmation mode (confirm)

        -c|-confirm

        Extended output

        -o|-output

        Scrolling line count (scroll)

        -s|-scroll

        Message language (language)

        -l|-language

        BRSPACE command line (command)

        This shows you the BRSPACE -f idalter command that is to be executed using the current settings.

        Note Note

        If required, in Table names and Index names you can enter the names of multiple objects. You can also use wildcards. For more information, see “Selecting Objects” in Segment Management with BR*Tools.

        In Tablespace names and Table owner, you can specify multiple objects but you cannot use wildcards. BRSPACE processes all the indexes in the specified tablespace(s) or all indexes belonging to the specified index owner(s) or table(s). But these entries and alter action entry are optional.

        End of the note.

      3. Choose Continue.

        BRGUI or BRTOOLS prompts you to start BRSPACE.

      4. Choose Continue to start BRSPACE.

    • Command line:

      Enter at least the following command:

      brspace -f idalter

      You can enter more parameters, including the index names and action, if required. For more information, see BRSPACE -f idalter.

      Note Note

      Whichever way you start the procedure - with BRGUI or BRTOOLS, or from the command line - you can use quick mode if you know the object names, in this case the index names. For more information, see How to Use BR*Tools.

      End of the note.

    BRSPACE starts and you see a message that includes Start of BRSPACE processing. From now on, BRSPACE writes a detail log.

    BRSPACE displays the Alter indexes main menu.

  2. You can use quick mode as follows:

    • If you have already entered the action, continue with step 4.

    • If you have already entered the index names and action, continue with step 6.

    Note Note

    If you have entered multiple indexes, BRSPACE displays as confirmation a List of indexes for alter. If you have not already made a final selection, you can make a selection from this list.

    Continue with step 6 (quick mode).

    End of the note.

  3. Choose or confirm the required action:

    • Coalesce index

    • Set parallel degree

    • Shrink indexes

    • Switch on index usage monitoring

    • Switch off index usage monitoring

  4. If you have already entered the index names, continue with step 6 (quick mode).

    BRSPACE displays the index list:

    List Entry

    Meaning

    Pos.

    List sequence number

    Owner

    Index owner

    Table

    Table name

    Index

    Index name

    Prt.

    Partitioned

    Prts

    Number of partitions

    Mon.

    Usage monitoring

    Par.

    Parallel degree

  5. Select an index or multiple indexes.

    Example Example

    These examples only apply to input in character mode.

    To select the first three indexes in the list, enter 1-3.

    To select the first and third indexes, enter 1,3.

    To select the first three indexes and the fifth, enter 1-3,5.

    To select all indexes, enter 0.

    BRSPACE displays the menu, Options for alter of indexes.

    End of the example.

  6. Set the required options:

    Menu Entry

    Meaning

    Current parallel degree (currdeg)

    – display only

    Current parallel degree of the index

    Alter index action (action)

    – display only

    The action that you selected above

    New parallel degree (degree)

    -f tsalter -d|-degree

    SQL command line (command)

    The SQL command that is to be executed using the current settings. For more information, see your Oracle SQL documentation.

  7. To start processing with the selected options, choose Continue.

Result

Check the results in the BRSPACE logsBRSPACE Logs.

For more information about how to view the logs with BR*Tools, see Showing Profiles and Logs with BR*Tools.