Re: Free Space within Table or Index

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/08/12
Message-ID: <33F12DAC.387B@postoffice.worldnet.att.net>#1/1

To find out the amount of free space in a table, run ANALYZE TABLE <table_name> COMPUTE STATISTICS. Then, run the following SQL statement:

SELECT table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
FROM user_tables
WHERE table_name='<table_name>';

Relevant for what you're trying to find out will be the values in BLOCKS, EMPTY_BLOCKS (data blocks within the table that contain no data), AVG_SPACE (average number of free bytes in a data block allocated to your table), AVG_ROW_LEN.

Similarly, for an index run ANALYZE INDEX <index_name> COMPUTE STATISTICS and then look in table INDEX_STATS. Here the relevant values are in BLOCKS, BTREE_SPACE, USED_SPACE, PCT_USED.

Alternatively, you could calculate how much space the index or table takes up using the USER_EXTENTS table and/or the ROWID (for table only), and comparing the results to how much you pre-allocated for the object.

Hope this helps.

Michael Serbanescu

---

Neil Boemio wrote:
>
> When creating a new table or index and after loading up the data, I'd
> like to be able to tell if I have sized the object too small or much too
> big. Is there any view which will tell me how much free space exists
> within a table or index?
>
> ___________________________________________
> _/_/_/ Neil Boemio _/_/_/
> _/_/_/ nboemio_at_bway.net _/_/_/
> _/_/_/ http://www.bway.net/~nboemio/ _/_/_/
Received on Tue Aug 12 1997 - 00:00:00 CDT