Determine tables for online reorg

[Former Member]

Hello,

Our Customer has asked us to do reorganisation of Database to claim free space.

I search a lot still confuse about few aspects abt it, as new to oracle.

How can we determine which table needs the reorganisation, which help us to reclaim maximum space.

I have gone through below notes

646681 and 541538

But these gives the generic concept about reorg, bt still didnt got the clear idea.

Any help is appreciated.

Regards

Ashish

[former_member206552 · Accepted solution]

Hi Ashish

you can use the query below to find the tables with the most wasted space and where you are most likely to gain the most space from

SELECT * FROM

(SELECT

    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,

    NUM_ROWS,

    AVG_ROW_LEN ROWLEN,

    BLOCKS,

    ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,

    ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *

      (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,

    ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -

      (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"

  FROM DBA_TABLES

  WHERE

    NUM_ROWS IS NOT NULL AND

    OWNER LIKE 'SAP%' AND

    PARTITIONED = 'NO' AND

    (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)

  ORDER BY 7 DESC)

WHERE ROWNUM <=20;

remember your statistics needs to up to date.

Best Regards

Marius

[Former Member]

Hello,

Thanks for your valuable reply.

We have finialize our list of table for reorganisation.

There are some tables with type "LOB".

Is there any additional steps/ precaution needs to be taken while doing reorg of these tables.

or we have to follow same steps from BR tools?

Regards

Ashish

[former_member188883]

Hi Ashish,

Refer SAP Note

500340

FAQ: LOBS

Follow the note, check your scenario and follow the guideline accordingly.

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Also have a look at SAP note 821687.
There is a query that will help in finding those tables that are wasting the most space.

regards

[former_member188883]

Hi Ashish,

How can we determine which table needs the reorganisation, which help us to reclaim maximum space.

Do you know whether any archiving activity has been performed in the system ?

If yes, look out for the tables which are affected by archiving. This should be your first candidate for reorg.

Secondly generate a list of top table in size from DB02.

Identify any Basis tables in the list and perform housekeeping activities for those tables.

These table should be your next candidate for Reorg.

Refer SAP note for knowing which are the basis tables which can be controlled from becoming large.

SAp Note 706478 - Preventing Basis Tables From Increasing

Hope this helps.

Regards,

Deepak Kori