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:[EMAIL PROTECTED]
Enviado el: martes, 01 de abril de 2003 20:41
CC: '[EMAIL PROTECTED]'
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
