Re: How to know the size change of a table under SQL*plus?

Hi, Alan,

Thank you very much for answering my question.

I am interested in knowing the change of number of bytes of data in the table. I tried "select num_rows * avg_row_len from user_tables where table_name = 'VALID_VALUES'; ", I got result for some tables, but I got nothing for some others. Also, for the table from which I got result, after I inserted over 800 rows, the num_rows*avg_row_len still remained the same.

I appreciate if you could solve the problem for me when you get time.

Lucy

Alan Moor wrote:

> Depends on what you mean by "size". If you mean the number of data blocks
> allocated to the table, then USER_SEGMENTS is the place. It you want
> something more esoteric, like number of bytes of data in the table, then you
> have to work a little harder. For instance, if you have a table named
> "valid_values", the following SQL will show the number of blocks that
> actually have rows in them:
>
> select count(distinct(blockid)) from (
> select substr(rowid,1,8) || substr(rowid,15) blockid from valid_values)
>
> (This is from V7.3 table, V8 has a slightly different rowid structure, but
> the concept is still valid)
>
> The following sequence of command will yield the approximate number of bytes
> actually used by a table:
>
> analyze table valid_values compute statistics;
> select num_rows * avg_row_len from user_tables where
> table_name = 'VALID_VALUES';
>
> Good Luck
> Alan
>
> Lucy Xu wrote in message <37B4592D.251056A6_at_columbia.edu>...
> >Hi,
> >
> >I have problem with getting to know the size change of a table in bytes
> >under SQL*plus. I tried using SELECT SEGMENT_NAME, BYTES FROM
> >USER_SEGMENTS. I found the table name, the number of bytes. But after I
> >deleted over 500 rows in that table, the number of bytes for the table
> >still remained the same. I may need to use some other statements to know
> >if a table's size changes. Would someone like to help me on this? I
> >really appreciate it.
> >
> >Thanks a lot.
> >
> >
> >Regard,
> >
> >Lucy
> >
> >
> >
Received on Mon Aug 16 1999 - 12:31:41 CDT