Re: UNUSED SPACE IN TABLE

From: Anton Buijs <remove_aammbuijs_at_xs4all.nl>
Date: Sat, 26 Apr 2003 17:26:40 +0200
Message-ID: <3eaaa56a$0$49105$e4fe514c@news.xs4all.nl>

GAURAV MITTAL <gaurav82online_at_yahoo.com> schreef in berichtnieuws 4329cc8c.0304260657.d37c66f_at_posting.google.com...
| how do i find the unused space in a table.actually my table contained
| 7.8 million records.i deleted 4 million records.the count(*) on the
| table shows 3.8 million records.However when i queried the num_rows in
| dba_tables it showed 7.8 million rows.
| also the space occupied before delete was fired showed 50
| gigs in the bytes column in dba_tables and the same was showed in the
| dba_tables(bytes column) after the delete was done.
|
| does anybody have anwser??

My advise is you learn more about how Oracle (de)allocates space and about gathering statistics on tables and indexes.

dba_tables.num_rows is set when you gather statistics on a table.
dba_tables.last_analyzed will show the last date you did that.
dba_tables.bytes does not exist, dba_tables.blocks does and is also

calculated when you gather statistics and should indicate the blocks that really contain rows. The cost based optimizer takes this and other things into account when making execution plans. You should query dba_segments to see what is allocated by a segment.

When rows are inserted in a table, the freelist(s) is searched for a block with space. When there is no space left a next extent is allocated. When you delete rows, the allocated blocks are never de-allocated from the table. When blocks becomes empty enough (see pct_used storage parameter) they are added on the freelist again so new inserts are possible.

You could try "alter table <table> deallocated unused;" to free up the space above the high water mark, but I doubt it will decrease the size significantly.
If you really want the space back, you need to reorganize the table: - export, drop, import the table
- create table as select (before or after renaming the existing table) and rebuild the indexes.
- use "alter table move" to move it to another tablespace but I'am not sure if it will free up the space.
The second option was the best to start with, because you did not had to do that massive deletes then. Received on Sat Apr 26 2003 - 10:26:40 CDT