avg_space field in DBA_TABLES
Date: Mon, 26 Oct 2009 19:03:55 -0700
Message-ID: <e37540930910261903k29b02865jd77811523db3f66b_at_mail.gmail.com>
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
Received on Mon Oct 26 2009 - 21:03:55 CDT