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