Oracle-L: RE: how to calculate table size

From: Fermin Bernaus Berraondo <fbernaus_at_sammic.com>
Date: Tue, 01 Apr 2003 23:28:47 -0800
Message-ID: <F001.00577AE8.20030401232847@fatcity.com>

I've been using this query for both normal and empty tables and works so far.

For those tables with no data in them (or that have suffered no deletion) you can ommit the second query since the table should not have any emptied block. Emptied blocks are those who have been occupied by data from the table but that have been deleted; for these, Oracle marks them as deleted but are still asigned to a table. I do not know if you want to consider this free space as part of the table or not.

There is a way to deallocate unused space to a table that has been previously used. You must use 'alter table ... deallocate' for that. You have explanations on this in the manual, check:

http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/statements_32.htm#2080417

I am sending this mail to the list as well, I am a learner and do not consider myself an expert, maybe someone else can join and comment something.

-----Mensaje original-----
De: Basavaraja, Ravindra [mailto:Ravindra.Basavaraja_at_T-Mobile.com] Enviado el: martes, 01 de abril de 2003 20:41 CC: 'fbernaus_at_sammic.com'
Asunto: RE: how to calculate table size

Hi Fermin,

Thanks for your reply.

I am estimating the growth of database tables for a new database and many tables don't have any data. Can I still use the same queries to estimate the size of the tables or do you have anything different?

Thanks

-----Original Message-----
Sent: Tuesday, April 01, 2003 4:24 AM
To: Multiple recipients of list ORACLE-L

Hi Ravindra,

Use the following, supposing your db_block_size is 2048 (change as appropiate).

	  SELECT segment_type, segment_name,BLOCKS*2048/1024 "Kb"
        FROM   DBA_SEGMENTS
        WHERE  OWNER=UPPER('<owner>') AND SEGMENT_NAME = UPPER('<table_name>');

	You should substract emptied blocks from this table, using:

	  ANALYZE TABLE <owner>.<table_name> ESTIMATE STATISTICS;

        SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 "Kb"
        FROM   DBA_TABLES
        WHERE  OWNER=UPPER('<owner>') AND TABLE_NAME = UPPER('<table_name>');

	This will give you how many kb are occupied by empty blocks, so substract this amount from the prior result.

	Hope this helps.

-----Mensaje original-----
De: root_at_fatcity.com [mailto:root_at_fatcity.com]En nombre de Basavaraja, Ravindra
Enviado el: martes, 01 de abril de 2003 1:24 Para: Multiple recipients of list ORACLE-L Asunto: how to calculate table size

Hi,

Anyone having any formula to calculate table size?Basically to estimate the growth of table over a peroid of time. I have the row_size,db_block_size.How do i get the table size.

Thanks
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Basavaraja, Ravindra
INET: Ravindra.Basavaraja_at_T-Mobile.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Fermin Bernaus Berraondo
INET: fbernaus_at_sammic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Fermin Bernaus Berraondo
INET: fbernaus_at_sammic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------