Real volume of data in database table? (Archiving, reorganization)

[jan_kotrc]

Dear SAP bloggers,

I have a question about table sizing in Oracle DB. In DBACOCKPIT/DB02 we can see the size of the database table, but not the real size of the data within the table.

If the table was growing exponentially over months / years without a good archiving strategy, what is the best process to identify the optimal size for the table after archiving & reorganization?

For example, the table SOFFCONT1 stopped growing 50/2021, because the basis team run archiving activities. It stopped on 1.026.439GB (1TB), got some data archived (and deleted) and the size is still the same. Is there a way to identify how much data is really stored in the table after archiving? If we knew how much data is in the table, we could identify optimal table size (based on historical data grow patterns) and "resize" the table by offline reorganization to save DB space. Maybe the SOFFCONT1 table is not a good example, because its a table for SAPoffice (user attachments etc.), but the question is for all tables in general.

How to identify how much data is really stored in a table?

Another table example (archiving activities run 12/2022 and the size of the table is 535.2GB, but how much data is in there? How much DB space could we free up by running offline reorganization and resizing the table?

Any advice?

Best Regards

[JamesZ]

We can use sap note ##1295200 query the used size which is the table real size.

Please note in case the table has lob column, we need to call note ##1295200 script by putting log segment name, and type is LOB.

Table and lobs(outline lob) stored separated, thus caculed size differently.

[jan_kotrc]

Dear James,

Thank you for this note!

Altho, we are unable to calculate real space usage of lob segments.

We have SOFFCONT1 table, which has 1TB lob segment, where we deleted 99% of data. The lob segment should have only hundreds of MBs inside. But the script is still calculating the whole lob segment as it is full:

Any idea how to calculate real volume of data in a lob segment?

[JamesZ]

Then Please check lob retention:

select nvl(to_char(pctversion),'NULL') pctversion,

nvl(to_char(retention),'NULL') retention
from dba_lobs
where segment_name = '';

And check undo_retention.

If we have high undo_retention, then some expired lob could there.

If indeed retention is high, you can try to do reorg.