Top Database Support Questions: How to…reorg a table using brtools and exp/imp

After years of continous improvements, the brtools now cover most of the scenarios you will ever face when administrating an SAP Oracle database.

However, there is one particular feature that sapdba had but the brtools are (still) lacking:
Performing an offline reorg of a single table with exp/imp respectivly expdp/impdp.
The reason for this is quite simple (according to development): You don’t need this.
In general this is true. When it comes to relocating tables, there are usually better ways (see note #646681).

However, as far as analysing/fixing certain kinds of corruptions is concerned, using export/import is still a necessity. I am in particular refering to problems like the one mentioned in note
#1136063 – Oracle Direct Path Load leads to block corruptions in ASSM
but there are others as well.

The procedure itself is quite simple and involves the following steps:

brspace -f tbreorg -t ZZTEST -d only_tab ... BR0370I Directory /oracle/102_32/sapreorg/sechuzfd created brspace -f tbreorg -t ZZTEST -d only_ind ... BR0370I Directory /oracle/102_32/sapreorg/sechuzgn created brspace -f tbreorg -t ZZTEST -d only_dep ... BR0370I Directory /oracle/102_32/sapreorg/sechuzia created

In the directories that can be seen in the log output, a ddl.sql file will be created that
contains all necessary DDL statements for recreating the table, its indices and other depending objects like constraints.
Now, the table itself can be exported (of course only after shutdown of the SAP system):

brspace -f tbexport -t ZZTEST -r yes -i no -c no -g no -e no

Now, we have to rename the table and the indices created on the table:

alter table sapr3.zztest rename to zztest_old; alter index sapr3."ZZTEST~0" rename to "ZZTEST_OLD~0";

In the next step, the ddl.sql script created earlier by the only_table command
has to be executed:

SQL> @ddl.sql

Now, the table can be imported again:

brspace -f tbimport -y full -r yes -i no -c no -g no -n yes

In the last step, the remaining ddl.sql scripts have to be executed and we are done.