Show TOC

Syntax documentation-f tbreorg Locate this document in the navigation structure

This BRSPACE function reorganizes tables. For more information, see Reorganizing Tables with BR*Tools.

Function options:

  • -a|-action: specifies the action for the table reorganization

    Syntax: -a|-action reorg|check|cleanup|stop|suspend|resume|long2lob

    • reorg reorganizes tables in the standard way

    • check checks tables for reorganization

      This option identifies tables that cannot be reorganized because, for example, they have LONG columns.

      Caution Caution

      With this option, the processing logic looks exactly the same as for the standard reorganization. However, processing is terminated directly before the actual reorganization starts.

      End of the caution.

    • cleanup cleans up tables after failure

      This option cleans up after a reorganization has terminated unexpectedly. Only use this option if a “hard” termination was executed on the reorganization and if BRSPACE was unable to connect to the database during the final phase (for example, due to a core dump).

      In this case, perform the cleanup repeatedly until no more objects for deletion are found.

      Caution Caution

      To make sure that all objects are checked and tidied up where necessary, simply select all tables for the cleanup function in list menu 352 or set the command option -t "*".

      End of the caution.

    • stop stops reorganization

      This option enables a “clean” premature termination of the reorganization without all tables being processed. However, the reorganization of individual tables is not cancelled in this case. Instead, the process is terminated before the next table is processed. Therefore this action can take a long time.

    • suspend suspends reorganization

      This option lets you pause the reorganization without terminating it. However, the reorganization of individual tables is not cancelled in this case. Instead, the process is terminated before the next table is processed. Therefore this action can take a long time.

    • resume resumes reorganization

      This option lets you resume the reorganization that was paused with the suspend option.

    • long2lob performs an online conversion of the LONG or LONG RAW fields to LOB. You can then perform an online reorganization of tables converted in this way. Prerequisite for this is Oracle 10g or higher and SAP kernel 7.00 or higher.

    Default: reorg

    Caution Caution

    You start the stop, suspend, and resume actions in parallel to a current reorganization, usually from a different shell or command prompt, for example:

    brspace -c force -f tbreorg -a suspend

    ...

    brspace -c force -f tbreorg -a resume

    End of the caution.

  • -d|-ddl: specifies how the Data Definition Language (DDL) statements for tables to be reorganized are handled

    Syntax: -d|-ddl yes|no|first|only|only_tab|only_ind|only_dep

    • yes is the default and specifies that the DDL statements are generated and saved immediately before a table is reorganized

    • no specifies that the DDL statements are not saved – we do not normally recommend this, although there is a small performance gain

    • first specifies that all DDL statements are generated before the reorganization begins. Processing then stops before the reorganization so that you can change the statements if required.

      Caution Caution

      If you change some attributes, make sure that they:

      • Are syntactically correct

      • Do not contain any new fields

      • Are compatible with the SAP dictionary

      End of the caution.

    • only specifies that only the DDL statements are generated and saved. There is no actual reorganization.

    • only_tab specifies that the DDL statements are generated and saved only for tables

    • only_ind specifies that the DDL statements are generated and saved only for indexes

    • only_dep specifies that the DDL statements are generated and saved only for dependent objects such as constraints, triggers, grants

    Default: yes

  • -e|-degree: specifies parallel degree used internally by the DBMS_REDEFINITION package for the reorganization of individual tables BRSPACE resets it to the old value after the reorganization has finished.

    Syntax: -e|-degree <degree>

    Default: the parallel degree of the current table

  • -i|-indts: specifies that a separate index tablespace is to be used to store the indexes

    Syntax: -i|-indts <tablespace>

    <tablespace> specifies the tablespace name

    Default: current index tablespace if option -n|-newts is not specified. Otherwise, the new table tablespace, that is, no separate index tablespace

  • -l|-initial: specifies the initial extent size of the reorganized table

    Syntax: -l|-initial <category>

    <category> specifies the size of the initial extent after reorganization and is restricted to the following possible values:

    • 1: 16 KB

    • 2: 64 KB

    • 3: 1 MB

    • 4: 8 MB

    • 5: 64 MB

    • 6: 1 GB

    Default: none

    If you do not specify initial at all, the reorganized table retains the initial extent size from before the reorganization.

  • -m|-mode: lets you reorganize the tables offline using ALTER TABLE (PARTITION) MOVE (not the Oracle DBMS_REDEFINITION package).

    This method can be faster than an online reorganization but it locks the tables, so make sure that your SAP system is not running at reorganization time.

    Syntax: -m|-mode online|offline

    Default: online

  • -n|-newts: specifies that the tables are reorganized to a new tablespace

    Syntax: -n|-newts <tablespace>

    <tablespace> specifies the tablespace name

    Default: current tablespace where the tables are located

  • -o|-owner: defines the SAP owner of tables- useful for Multiple Components in One Database (MCOD) configurations

    Syntax: -o|-owner <owner>|<owner_list>

    <owner> or <owner_list> specifies the name of the SAP owner or owners

    Default: no default in MCOD, otherwise the single SAP owner

  • -p|-parallel: specifies parallel processing for the reorganization

    Syntax: -p|-parallel <threads>

    <threads> specifies the number of processing threads running in parallel

    Default: 1, that is, no parallel processing

  • -r|-sortind specifies the index on which the table is sorted during reorganization

    Syntax: —r|-sortind first|<ind_id>|<ind_name>

    • first: sorts on the SAP primary index (that is, with ID 0), or on the first index (lexically), or on the only index

    • <ind_id>: sorts on the index with the ID <ind_id>, for example: 0, 1, or 2

    • <ind_name>: sorts on the index with the name <ind_name>

  • -s|-tablespace: specifies the tablespace of the tables to be reorganized

    Syntax: -s|-tablespace <tablespace>|<tablespace list>

    <tablespace> or <tablespace list> specifies the tablespace name(s)

    Default: none

  • -t|-table: specifies the tables to be reorganized

    Syntax: -t|-table all|allsel|[<owner>.]<table>|

    [<owner>.][<prefix>]%[<suffix>]|

    [<owner>.][<prefix>]*[<suffix>]|<table_list>

    where:

    all or “%” means preselect

    allsel or “*” means final select:

    For more information about wildcards for preselect and select, see Selecting Objects in Segment Management with BR*Tools.

    Default: none, since you must always specify tables for reorganization