Avg_space
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Avg_space

  1. #1
    Join Date
    Jan 2003
    Location
    Bahrain
    Posts
    109

    Avg_space

    Hi Gurus,

    What is the avg_space in dba_tables?

    create table tz(t number) storage (initial 2K next 2K pctincrease 0 minextents 5) tablespace users;

    SQL> insert into tz values(1);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> analyze table tz compute statistics;

    Table analyzed.

    SQL> select blocks,empty_blocks,avg_space,num_freelist_blocks
    2 from dba_tables where table_name='TZ';

    BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
    ---------- ------------ ---------- -------------------
    1 4 8091 1


    SQL> begin
    2 for i in 1..50 loop
    3 insert into tz values(i);
    4 end loop;
    5 end;
    6 /

    PL/SQL procedure successfully completed.

    SQL> commit;

    Commit complete.

    SQL> analyze table tz compute statistics;

    Table analyzed.

    SQL> select blocks,empty_blocks,avg_space,num_freelist_blocks
    2 from dba_tables where table_name='TZ';

    BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
    ---------- ------------ ---------- -------------------
    1 4 7541 1


    When we are adding the rows, the value of avg_space is decreasing. Can anyone explain this pelase?

    Seelan

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    AVG_SPACE
    The average available free space in the table

  3. #3
    Join Date
    Jan 2003
    Location
    Bahrain
    Posts
    109
    Hi,

    In one book,

    They are using the following query find the tables which are having more than 10% of space below the higher water mark

    select table_name,avg_space from dba_tables where avg_space < blocks*.10;

    Is it correct?

    Seelan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width