Show TOC

Reorganizing Tables with BR*ToolsLocate this document in the navigation structure

Prerequisites

You cannot perform online reorganization for tables with LONG or LONG RAW fields but you can convert them to CLOB or BLOB online. After this conversion, you can reorganize all tables online. For more information, see SAP Note 646681 Information published on SAP site.

Context

You can use BR*Tools for Oracle to reorganize tables online.

Note

This section describes how you reorganize a table with BR*Tools.

For more information on the approach to table reorganization, see:

You can perform the following types of reorganization:

  • Reorganize tables online or offline

  • Reorganize table partitions online or offline

  • Check tables for reorganization

  • Clean up tables after aborted reorganization

  • Convert LONG and LONG RAW fields to CLOB or BLOB online (Oracle 10g or higher)

  • Convert BasicFile LOBs to SecureFile LOBs online (Oracle 11g or higher)

  • Convert heap tables to IOTs online

  • Convert IOTs to heap tables online

  • Stop reorganization (command-line mode only)

  • Suspend reorganization (command-line mode only)

  • Resume reorganization (command-line mode only)

Note

  • Online reorganization is performed with the Oracle package DBMS_REDEFINITION. The SAP system can remain online during this process.

  • Offline reorganization is performed with the SQL command ALTER TABLE MOVE, which locks the affected tables. Therefore, make sure you stop the SAP system for this process.

For more information, see -f tbreorg.

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 Reorganize tables End of the navigation path.

        BRGUI or BRTOOLS displays the menu BRSPACE options for reorganization of tables, 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

        Reorganization action (action)

        -f tbreorg -a|-action

        Tablespace names (tablespace)

        -f tbreorg -s|-tablespace

        Table owner (owner)

        -f tbreorg -o|-owner

        Table names (table)

        -f tbreorg -t|-table

        Table partitions (tabpart)

        -f tbreorg -tp|-tabpart

        Confirmation mode (confirm)

        -c|-confirm

        Extended output (output)

        -o|-output

        Scrolling line count (scroll)

        -s|-scroll

        Message language (language)

        -l|-language

        BRSPACE command line (command)

        BRSPACE

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

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

        You can also specify a list of tables to be processed with reorg_table in the initialization profile init<DBSID>.sap.

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

      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 tbreorg

      You can enter more parameters, including the table names, if required. For more information, see BRSPACE -f tbreorg.

    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 final object names, in this case the table names. For more information, see How to Use BR*Tools.

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

  2. If you have already entered the table names, continue with step 5 (quick mode).
    Note

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

    Continue with step 5 (quick mode).

    BRSPACE displays the Table reorganization main menu.

  3. Choose Reorganize tables.

    BRSPACE displays the table list:

    List Entry

    Meaning

    Pos.

    List sequence number

    Owner

    Table owner

    Table

    Table name

    Pt.

    Partitioned

    DgPk.

    Parallel degree / primary key:

    “ ” means no

    “+” means yes / enabled

    “-” means yes / disabled

    Rows

    Number of rows

    Used [KB]

    Space used by the table (HWM - high-water mark)

    Data [KB:%]

    Amount of data in the table: percentage of occupied space
  4. Select a table or multiple tables.
    Example

    These examples only apply to input in character mode.

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

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

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

    To select all tables, enter 0.

    BRSPACE displays the menu, Options for reorganization of tables.

  5. Set the required options:

    Menu Entry

    Meaning

    Reorganization action (action)

    -f tbreorg -a|-action

    Reorganization mode (mode)

    -f tbreorg -m|-mode

    Create DDL statements (ddl)

    -f tbreorg -d|-ddl

    New destination tablespace (newts)

    -f tbreorg -n|-newts

    Separate index tablespace (indts)

    -f tbreorg -i|-indts

    Parallel threads (parallel)

    -f tbreorg -p|-parallel

    Table/index parallel degree (degree)

    -f tbreorg -e|-degree

    Category of initial extent size (initial)

    -f tbreorg -l|-initial

    Sort by fields of index (sortind)

    -f tbreorg -r|-sortind

    Index for IOT conversion (iotind)

    -f tbreorg -r|-iotind

    Compression action (compress)

    -f tbreorg -c|-compress

    LOB compression degree (lobcompr)

    -f tbreorg -lc|-lobcompr

    Index compression method (indcompr)

    -f tbreorg -ic|-indcompr

    Note

    There is no SQL command line here because the online reorganization is performed in several steps by an Oracle package, which contains multiple procedures.

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

Results

Check the results in the BRSPACE logs:

  • The summary log space<DBSID>.log displays the return code.

  • The detail log s<encoded timestamp>.idr displays the details.

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

For online reorganization, the file ddl.sql is created in the subdirectory <encoded timestamp> of the directory if the option -d|-ddl is set to a value other than no. The file contains the Data Definition Language (DDL) statements used for the creation of interim tables during the reorganization. For more information, see Reorganization. If the -d|-ddl option is set to only, then only DDL statements are created and the actual reorganization is not performed.

Next Steps

For more information about LOB converison and table compression, see SAP Note 1431296 Information published on SAP site. For more information about conversion between heap-organized tables and IOTs, see SAP Note 1325304 Information published on SAP site.