Oracle-L: RE: How TO KNOW TABLE SIZE

From: Tomita Koga, Alex - (Per) <Alex.Tomita_at_attla.com>
Date: Mon, 04 Mar 2002 12:23:31 -0800
Message-ID: <F001.0041EA52.20020304122331@fatcity.com>

Check this note from metalink.....

Title: Script to compute table size

Disclaimer:
This script is provided for educational purposes only. It is NOT supported by Oracle Support Services. The script has been tested and appears to work as intended. However, you should always test any script before relying on it.

PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text editors, email packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Abstract:
It is sometimes necessary to calculate the exact size (exact number of bytes) of a table.

The function 'VSIZE' gives the exact number of bytes allocated by Oracle for data.

For example, if you have a table called 'table1' with n columns say, 'col1', 'col2', .... , 'coln', execute the following SQL statement to find the exact size of the table:

SELECT SUM(VSIZE(col1)) + SUM(VSIZE(col2)) + ... +

SUM(VSIZE(coln)) FROM table1;

In the scott/tiger schema, the following SQL statement can be executed to find the exact table size of the 'DEPT' table:

SQL> SELECT SUM(VSIZE(deptno)) + SUM(VSIZE(dname)) +

SUM(VSIZE(loc)) FROM dept;

SUM(VSIZE(DEPTNO))+SUM(VSIZE(DNAME))+SUM(VSIZE(LOC))

Requirements:
None

Version Testing:
This script was tested on Oracle 7.3.3 to 8.1.6 and SQL*Plus

AT&T Latin America
Alex Tomita Koga - ITS
e-mail: alex.tomita_at_attla.com
phone: (51) 1 610-5555 extension 2619
Av. Larco 1301 Torre Parque Mar - Miraflores

-----Mensaje original-----

De: Seema Singh [mailto:oracledbam_at_hotmail.com] Enviado el: Lunes, 04 de Marzo de 2002 02:08 p.m. Para: Multiple recipients of list ORACLE-L Asunto: How TO KNOW TABLE SIZE

Hi
How can I know how much space a table occupied? Can BLOCKS columns in dba_tables helps?
Or I have to use dba_extents and dba_segments tables? Thx
-Seema

MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx

--

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

Author: Seema Singh
INET: oracledbam_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------


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.com
--

Author: Tomita Koga, Alex - (Per)
INET: Alex.Tomita_at_attla.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------


To REMOVE yourself from this mailing list, send an E-Mail message