avg_space field in DBA_TABLES

Hi Oracle-L,

I understood from the documentation that avg_space field in dba_tables
should contain "Average amount of free space, in bytes, in a data
block allocated to the table".

So, I figured that if I create a table and insert a bunch of rows in
there, and then I'll delete 50% of the rows and immediately analyze
the table - I should see avg_space of more or less 4k. makes sense?

Here's what I did (on 11.1.0.7):
SQL> CREATE TABLE T1
  2  (N NUMBER,
  3  NAME VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> insert into T1 select rownum, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' from dual connec
t by level <= 100000;

100000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> delete from t1 where mod(n,2)=0;

50000 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> execute dbms_stats.gather_table_stats(user,'t1', estimate_percent=>100)

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from dba_tables whe
re table_name='T1' and owner='SYS';

TABLE_NAME                         BLOCKS   NUM_ROWS AVG_ROW_LEN  AVG_SPACE
------------------------------ ---------- ---------- ----------- ----------
T1                                    511      50000          31          0


Where's my free space? Am I getting the definition of avg_space wrong
or is it a problem with my understanding of how row delete will work?

Thanks,
Chen Shapira
--
http://www.freelists.org/webpage/oracle-l

Other related posts: