Show TOC

-f tbreorgLocate 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|check|cleanup|heap2iot|iot2heap|lob2lob|

    long2lob|reopart|reorg|resume|stop|suspend

    • check checks tables for reorganization

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

      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.

    • 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

      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 "*".

    • heap2iot converts heap tables to IOTs

    • iot2heap converts IOTs to heap tables

    • lob2lob converts old LOBs to new LOBs

    • 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.

    • reopart reorganizes table partitions

    • reorg reorganizes tables in the standard way

    • resume resumes reorganization

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

    • 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.

    Default: reorg

    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

  • -c|-compress: specifies the table / LOB / index compression

    Syntax: -c|-compress none|ctab|clob|cind|ctablob|ctabind|

    clobind|ctablobind|dtab|dlob|dind|dtablob|dtabind|dlobind|

    dtablobind|ctab_only|clob_only|cind_only|ctablob_only|

    ctabind_only|clobind_only|ctablobind_only|dtab_only|dlob_only|

    dind_only|dtablob_only|dtabind_only|dlobind_only|dtablobind_only

    • none: no action

    • ctab: compress tables

    • clob: compress LOBs

    • cind: compress indexes

    • ctablob: compress tables and LOBs

    • ctabind: compress tables and indexes

    • clobind: compress LOBs and indexes

    • ctablobind: compress tables, LOBs, and indexes

    • dtab: decompress tables

    • dlob: decompress LOBs

    • dind: decompress indexes

    • dtablob: decompress tables and LOBs

    • dtabind: decompress tables and indexes

    • dlobind: decompress LOBs and indexes

    • dtablobind: decompress tables, LOBs and indexes

    • ctab_only: compress uncompressed tables only

    • clob_only: compress uncompressed LOBs only

    • cind_only: compress qualified indexes only

    • ctablob_only: compress uncompressed tables and LOBs only

    • ctabind_only: compress uncompressed tables and indexes only

    • clobind_only: compress uncompressed LOBS and indexes only

    • ctablobind_only: compress uncompressed tables, LOBs and indexes only

    • dtab_only: decompress compressed tables only

    • dlob_only: decompress compressed LOBs only

    • dind_only: decompress qualified indexes only

    • dtablob_only: decompress compressed tables and LOBs only

    • dtabind_only: decompress compressed tables and indexes only

    • dlobind_only: decompress compressed LOBs and indexes only

    • dtablobind_only: decompress compressed tables, LOBs and indexes only

    Default: none

    For more information, see SAP Notes 1431296 Information published on SAP site and 1464156 Information published on SAP site.

  • -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

      If you change some attributes, make sure that they:

      • Are syntactically correct

      • Do not contain any new fields

      • Are compatible with the SAP dictionary

    • 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. There is no actual reorganization.

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

    • only_dep specifies that the DDL statements are generated and saved only for dependent objects such as constraints, triggers, grants. There is no actual reorganization.

    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>

    Note

    Be careful when setting <degree> to a value greater than 1. This alters the sequence of the table rows, which can lead to poor performance.

    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

  • -ic|-indcompr: specifies the index compression method

    Syntax: -ic|-indcompr all_col|ora_proc|sap_proc|val_cmd|<number>

    • all_col: all eligible index columns

    • ora_proc: procedure provided by Oracle

    • sap_proc: procedure provided by SAP

    • val_cmd: result of validate command

    • <number>: fixed number of index columns

    Default: ora_proc

    For more information, see SAP Note 1454156 Information published on SAP site.

  • -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

    • 7: 8MB

    • 8: 64 GB

    Default: none

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

  • -lc|-lobcompr low|medium|high: specifies the degree of LOB compression

    Default: medium

  • -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: all SAP owners 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: all (preselection)

  • -tp|tabpart: specifies the table partitions to be reorganized

    Syntax: -tp|-tabpart all|allsel|[[<owner>.]<table>.]<partition>|

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

    [[<owner>.]<table>.][<prefix>]*[<suffix>]|<tabpart_list>

    all or allsel means selection of all table partitions

    '%' or '*' means selection of wildcards

    Default: all table partitions

  • -x|-iotind: specifies the index for heap to IOT conversion

    Syntax: first|<ind_id>|<ind_name>

    Default: first index