Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to know the size change of a table under SQL*plus?

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

From: Michel Cadot <micadot_at_altern.org>
Date: Tue, 17 Aug 1999 09:34:26 +0200
Message-ID: <7pb3b9$9nl$1@oceanite.cybercable.fr>


Lucy,

num_rows and avg_row_len are available only after the statement analyze table.
After insertion of number of rows you have to run this statement to have correct values:
analyze table <table name> compute statistics; (if table not so big) analyze table <table name> estimate statistics

   sample <x> percent;
or sample <n> rows; (if table is big)

Regards.

Lucy Xu a écrit dans le message <37B84AFB.DEBC995E_at_columbia.edu>...
>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 Tue Aug 17 1999 - 02:34:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US