Re: Smart way to calculate size of a table!

I've got a few answers about this question:

• First answer Hi,

The size of a table can be determined by the following method.

1. analyze table <table_name> compute statistics.

This command will populate the user_tables.

2) The size can be determined by querying the user_tables.

SQL> desc user_tables;

 Name                            Null?    Type
------------------------------- -------- ----
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                 NOT NULL VARCHAR2(30)
 CLUSTER_NAME                             VARCHAR2(30)
 PCT_FREE                        NOT NULL NUMBER
 PCT_USED                        NOT NULL NUMBER
 INI_TRANS                       NOT NULL NUMBER
 MAX_TRANS                       NOT NULL NUMBER
 INITIAL_EXTENT                           NUMBER
 NEXT_EXTENT                              NUMBER
 MIN_EXTENTS                              NUMBER
 MAX_EXTENTS                              NUMBER
 PCT_INCREASE                             NUMBER
 FREELISTS                                NUMBER
 FREELIST_GROUPS                          NUMBER
 BACKED_UP                                VARCHAR2(1)
 NUM_ROWS                                 NUMBER
 BLOCKS                                   NUMBER
 EMPTY_BLOCKS                             NUMBER
 AVG_SPACE                                NUMBER
 CHAIN_CNT                                NUMBER
 AVG_ROW_LEN                              NUMBER
 DEGREE                                   VARCHAR2(10)
 INSTANCES                                VARCHAR2(10)
 CACHE                                    VARCHAR2(5)
 TABLE_LOCK                               VARCHAR2(8)

The query is select * from user_tables where table_name = <tablename>;

The size of the table can be determined from the following

 NUM_ROWS                                 NUMBER
 BLOCKS                                   NUMBER
 EMPTY_BLOCKS                             NUMBER
 AVG_SPACE                                NUMBER
 AVG_ROW_LEN                              NUMBER

Please refer Oracle& Server Reference Release 7.2 for description of the columns for USER_TABLES.

3) After obtaining the information, delete the statistical information by the
following command.

SQL> analyze table <tablename> delete statistics;

This will delete the statistics generated.

select bytes from user_segments where segment_name=upper(<table>);

 The last answer is without a doubt the simplest, but does it give you a fair result ?

Is one method preferrable to the other ? The first seems to be more accurate.

/Magnus Rosenquist Received on Tue Feb 13 1996 - 00:00:00 CET