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

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 Sat Aug 14 1999 - 17:51:50 CDT