Only Tables (no secondary indexes) Online Reorg with BrSpace

[Former Member]

Hi all,

We are struggling with a huge table reorg (more than 1.5 TB). We manage to reorg the table after several hours but the reorg failed when Brspace tried to rebuild one of the secondary index. Due to this, all the reorg was rolled back and we lost our maintentance window.

My question is if would be possible to run an online reorg for just the table and the primary index. After that we can rebuild the secondary indexes and just rerun the rebuild again in case of failure.

One option is to drop the indexes before and recreate after the reorg, but I'm looking for a more elegant solution.

Many thanks in advanced,

[stefan_koehler · Accepted solution]

Hi Jose,

> My question is if would be possible to run an online reorg for just the table and the primary index.

Yes, this is possible. I assume that you have used DBMS_REDEFINITION (implicitly used by BR*Tools) as you have mentioned an online reorg.

You can create the DDL file (for the interim table and corresponding structures) first, remove all the "non-primary" indexes and then run the reorg. After it has finished you can create all the other indexes as usual. The pre-"DDL" file can be generated with brspace option "-ddl".

Regards

Stefan

[Former Member · Accepted solution]

Yeah, this is an option but in that case it won't be a real online process as user will suffer a lot of performance problems accesing the table without any index.

[stefan_koehler · Accepted solution]

Hi Jose,

sorry i don't get it as this is exactly what you have requested. All the secondary indexes exist, remain useable in any case and you can restart at any point (by index creation failure) within the online reorg.

If you want to have control of each individual step (in case of restarting at that failure point) you have to use DBMS_REDEFINITION manually. That's is basically what i have suggested. Create the DDL file for the interim table (and its dependent objects) with the BR*Tools and do the reorg manually with DBMS_REDEFINITION for full control.

You can restart each index creation several times (in case of failures) before executing the PL/SQL procedure DBMS_REDEFINITION.FINISH_REDEF_TABLE.

This procedure completes the redefinition process. Before this step, you can create new indexes, triggers, grants, and constraints on the interim table. The referential constraints involving the interim table must be disabled. After completing this step, the original table is redefined with the attributes and data of the interim table. The original table is locked briefly during this procedure.

No performance issues - all online - and a lot of happy end users

Regards

Stefan

[Former Member · Accepted solution]

Many thanks Stefan.

[Former Member]

Hi,

What is the exact error that you are getting while rebuilding the secondary Index?

Regards,

Nick Loy

[Former Member]

Hi,

the error was because we hadn't enough space in the temporary tablespace to rebuild the index.,

This error is easy to solve but I want to make sure that if the index rebuild fails again we don't have to start from the scratch.

thanks

[Former Member]

Hi,

This is the minimum requirement to have sufficient space in your temporary tablespace for reorg.

And temporary space can be reclaimed after completion of activity.

I would increase the temporary tablespace size to  equal or bigger than the biggest Index and then do the online reorg, which is best solution and can be done without any interruption/intervention.

If you have enough space in temporary tablespace, then no need to worry about abnormal terminations

Regards,

Nick Loy

[former_member188883]

Hi Jose,

You can drop the indexes first

Then perform reorg

Later re-create the indexes.

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Hi,

Yeah, this is an option but in that case it won't be a real online process as user will suffer a lot of performance problems accesing the table without any index.

any other idea?

Kind Regards