Skip to Content
Technical Articles

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.

1 Comment
You must be Logged on to comment or reply to a post.
  • Very useful straight to the point blog.

    I hope you don’t mind if I add a last step that will be to create new statistics for the reorganized table.

    brconnect -u / -c -f stats -t