avg_space field in DBA_TABLES

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 Received on Mon Oct 26 2009 - 21:03:55 CDT