Identifying tables that need to be reorganized

[Former Member]

Hello folks,

Would you please let me know what is the recommendation to identify the tables that need to be reorganized in an ABAP BW system in order to release space at the DB?

Is it possible to execute the reorganization in SAP itself on these individual tables? Or do we need to perform a full reorganization of the DB? Is it possible to reorganize them online?

Thanks for your help,

Best regards

[former_member215759 · Accepted solution]

You need reorg whole DB, that is the recommended way.

[Former Member]

Basically you only want to reorganize tables that are larger, than the data they contain. I assume you are on oracle. Here is how you can identify the tables:

- look out for tables where rows have been deleted, somebody should know where data is being deleted

- use the CBO stats to compare the amount of data to the table size, remember these are only stats, so they could be inacurate:

SQL> select table_name, (num_rows * avg_row_len)/1024/1024 "DATA_MB", (blocks*8)/1024 "TABLE_MB"
     from dba_tables where (blocks*8)/1024 > 1000

This gives you a list of all tables above 1gb, of course this does not work for funky lob tables. But the BW tables should not contain them.

If you reorg only the table, then the space will only be freed within the tablespace. Thus your database will not get smaller, but other tables in that tablespace can reuse the space. BW systems can use partitioned tables as well, in large systems they are a must. With partitioning reorganising tables will not be necessary anymore, because you can simply drop a partition.

Cheers Michael

[Former Member]

Lot of good suggestions have already been made. Also keep an eye on long to lob conversions if interested in online re-orgs.

Regards.

Ruchit.

[Former Member]

follow Note 541538 - FAQ: Reorganization

[Former Member]

Well you can pick out a list of top tables and indexes if you are not comfortable with full Db Reorg.

In this case , Use transaction DB02old or DB02 and pick out the list of Tables and Indexes whose extent say is more than 100 and which are part of the SAPSR3 tablespace .

You can use report "RSANAORA" via SE38 to reorganize indexes online .

Make sure you scedule it , when there is not much activity happening in the system .

Regards,

Ashish .A. Poojary