RE: Moving tables into new tablespace

>To answer your second question I believe your best bet is the
 following:
>
>select owner||'.'||index_name name,
>distinct_keys*avg_data_blocks_per_key est_block_usage
>from dba_indexes;
>
>This should provide a rough idea of the actual size of the
 index. I say
>rough due to the "average data blocks per key" value in the
calculation.

This is not correct. avg_data_blocks_per_key represents the number of data blocks a key points to on the table. It has no relevancy for space usage in the index itself. In fact, nothing in DBA_INDEXES can tell you very much about index space allocation. For this, you need to look at INDEX_STATS, which is populated when you do a VALIDATE INDEX {index name} on the index. Look at the USED_SPACE or PCT_USED columns, which tells you how much space is actually allocated in the b*tree, including deleted keys. del_lf_rows_len tells you how much space is taken up by deleted keys.

Also, I should point out the the original poster that empty_blocks in DBA_TABLES does not tell you how much space is free in a table. It only tells you which blocks have never been used. A block might have had all data deleted out of it, and it still won't be included in empty_blocks. Multiplying avg_row_len*num_rows will tell you the amount of space used by data in the table. The rest is free space or headers.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Wed Jun 28 2000 - 00:00:00 CDT