DB reorg clarification

[Former Member]

Hi Gurus,

I am planning to do DB reorg in our production system. The main purpose of our activity is to reduce space & fragmentation used by PSAPBTABD & PSAPBTABI tablespaces.

I am using SAPDBA for this activity and will be doing reorg using list of tables & indexes option from the reorganization menu.

My Questions are,

1. How to zero in on the tables that need to be reorganized?

2. Is there any standard formula to calculate the Initial KB, Next extend size(KB) and No.Of Extends? During DB reorg, we have to set the values for these columns in each table.

regards,

Vinodh.

[Former Member · Accepted solution]

Have a look at this Oracle 8i specific paper from Oracle on this subject:

www.oracle.com/technology/ deploy/availability/pdf/defrag.pdf

[Former Member · Accepted solution]

Hi Maurice,

Thanks for this info. Unfortunately, the link you provided is not found. If you the pdf document with you, could you please mail me at <personal email removed>

regards,

Vinodh.

[former_member204746 · Accepted solution]

Vinodh, try this link (with no space)

http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf

[Former Member · Accepted solution]

Hi Maurice & Eric,

Thanks for the doc..It really gave me an idea about what to do and what not.

Thank you so much.

Best regards,

Vinodh.

Message was edited by:

Vinodh Kumar Narayanan

[former_member204746]

what is your Oracle version?

I using Oracle 10g, I would reorg all tables in PSAPBTAB2D/I tablespaces. Most tables cabn handle this online, then plan offline reorg for rest of tables. when this is completed, make sure that source tablespace PSAPBTABD is empty and drop them... then rename PSAPBTAB2D/I to original names.

read SAP note 646681 and using BRSPACE

if on Oracle 9, you can do a similar process but will need to remove tables again instead of renaming the 2D/I tablespaces

if on Oracle 8, then offline reorg is the only supported solution with SAP.

[Former Member]

Hi Eric,

Thanks for your reply.

My DB version is Oracle 8.1.7

I have left with the option of doing offline reorg using SAPDBA.

Please clarify my doubts in this config.

regards,

Vinodh.

[former_member204746]

sorry, On Oracle 8, only options is offline reorg.

[Former Member]

Hi Eric,

I do know that there is no option in Ora 8 except offline reorg but, I think the concept for reorg on the version should be the same. Based on the experience could you clarify my 2 qeustions?

Thanks for your time.

regards,

Vinodh