How to calculate the exact space occupied by table

[Former Member]

Hi,

I am deleting the data in tables such as DBTABLOG and SRRELROLES.

Until and unless I do a re-org I will not get the free space back but how do I estimate the freed up space before doing a re-org.

As of now I calculate the space occupied by the multiplying the number of records in the actual table with the average row length of each record (stats from DB02)... but the calculation does not match the actual size occupied by the table...

Please help me here

[Former Member · Accepted solution]

Your way to estimate the exact size is correct, maybe you give us the real numbers. Be careful, the table DBTABLOG has a special column type LOGDATA (either LONG or BLOB). But for SRRELROLES you should be fine.

If the numbers don't make sense, then the table statistics can be wrong.

Best regards, Michael

[Former Member · Accepted solution]

This is before deletion of the records

*Total................. 79,171,720

*Chained............... 0

*Avg. length.......byte 72

*Avg. length+header...byte 75

*Avg. initial length..byte 83

Total Space occupied as per my calculation would be 5.53GB and as per DB Stats in DB02 the total space occupied is 6.65GB

and this is after the deletion of the records

Rows

*Total................. 13,310,935

*Chained............... 0

*Avg. length.......byte 72

*Avg. length+header...byte 93

*Avg. initial length..byte 83

[stefan_koehler · Accepted solution]

Hello Grame,

that is the same reason as i have also answered in another thread of yours.

The calculation of the 5.53 GB is correct. This is the "used" space of the segment and not the allocated space. The transaction DB02 or DB02n is counting the space that is allocated by the segment, not the used space within the segment.

If you are using locally managed tablespaces, check sapnote #214995 for the extent allocation algorithm.

Regards

Stefan

[Former Member]

If you are on Oracle 10g, you could also use DBMS_SPACE to calculate the used space and allocated space:

SELECT

TO_CHAR(SPACE_ALLOCATED / 1024 / 1024, 999990.99) GROSS_MB,

TO_CHAR(SPACE_USED / 1024 / 1024, 999990.99) NET_MB,

TO_CHAR((SPACE_ALLOCATED - SPACE_USED) / 1024 / 1024, 999990.99) UNUSED_MB,

CHAIN_PCENT CHAIN_PERCENT

FROM

TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF('<owner>', '<table_name>', 'TABLE', NULL ));

See SAP note 1295200 for more information (English translation should be available within a few days).

Regards

Martin

[Former Member]

Martin,

I am running on 9i Oracle

[Former Member]

Hi Grame,

In order to calculate the space utilized by a table you can do the following :

1) Logon as orasid or sidadm user.

2) Connect to the DB as sysdba user.

3) Run the follwoing query :

SQL> select OWNER,TABLESPACE_NAME, segment_name, bytes/1024/1024 MB from dba_segments Where segment_type = 'TABLE' and Segment_Name='DBTABLOG';

Expected output :

OWNER TABLESPACE_NAME SEGMENT_NAME MB

SAPR3 PSAPPROTD DBTABLOG 10.015625

Hope this helps.

Regards,

Deoraj Alok.

[Former Member]

Deoraj,

I tried your formula, it gives the total space allocated whereas I wanted the total space actually used by the table records