DBTABLOG net size bigger than gross size

[Detelin_Nedev]

Hello everybody,

I am checking the table DBTABLOG what size it occupies how many MB are wasted, etc.

It is done with the following SQL script as per the OSS note 821687 - FAQ Space utilization and fragmentation in Oracle

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 TABLE_NAME='DBTABLOG';

However, I receive the result that net size is bigger than gross size and waste size is a negative value.

DBTABLOG    8.4810000000000000E+08    4.2200000000000000E+02    4.2352311000000000E+07    3.5874600000000000E+05    3.0406000000000000E+05    -5.4686000000000000E+04

What could be the reason for this strange result?

Best regards,

Detelin

[former_member206552]

Hi Detelin,

please format the session first

go to sqlplus and run the following before you run the script

col table_name for a20

col NUM_ROWS for 99999999999999999

col NET_MB for 99999999999999999

col GROSS_MB for 99999999999999999

col WASTED_MB for 99999999999999999

execute the script

best regards

marius

[Detelin_Nedev]

Hello Marius,

Thank you for your reply.

Actually I found this is also for tables MARA, MARC and maybe some other - I have not checked all tables. MARA and MARC are compressed and if this could be the possible explanation, but DBTABLOG is not compressed.

I executed this via DBACOCKPIT and converted in Excel.

TABLE_NAME	NUM_ROWS	ROWLEN	BLOCKS	NET_MB	GROSS_MB	WASTED_MB
DBTABLOG	848 100 000	422	42 352 311	358 746	304 060	-54 686
MARA	395 360	669	31 728	265	228	-37
MARC	1338950	584	34699	783	249	-534

Best regards,

Detelin

[former_member206552]

Brilliant, glad you came right

please close the tread

best regards

marius