How to find out Average free space in a table?

Posted on 2009-02-17
Last Modified: 2012-05-06

I have learned that every data block allocated to a table may not be completely filled and is governed by the PCTFREE and PCTUSED parameters.

I am trying to write a unix script which will automatically determine tables which are candidate for TABLE reorg.

I can imagine a code which first determines which tables have block fragmentation and then list such tables as candidate for table reorg.

For this, I would need some parameter called 'Average freespace' in a table.

Can anyone tell me how to find out the Average Free Space in a table ?

Question by:cryptosid
    LVL 34

    Accepted Solution

    First, you would only be talking about tables where deletes occur.  If you are not deleting, then you are not creating enough of holes where you would think a reorg is required.

    You need updated statistics, that is about the only way you can get to most of the numbers you will be looking for.

    In DBA_TABLES, you can look at AVG_ROW_LEN and NUM_ROWS.  Keep in mind these are all estimates.  If you mulitply those 2 you get the approximate size of your table.  Compare this to the number of blocks allocated below the high water mark (BLOCKS in DBA_TABLES).  Keep in mind there is the overhead of PCTFREE as well.

    Another thing to look at is AVG_SPACE, but I would think that if the table is large enough, the results may be skewed.

    I would think the only reason to reorg would be when a significant percentage of the table has been deleted and will not be repopulated.  Otherwise, as long as your storage parameters are set correctly, the space would be reused.
    LVL 18

    Assisted Solution

    You can also  use the built-in DBMS_SPACE ..
    LVL 11

    Assisted Solution

    The attached query will give you an idea of how densely used your blocks are. It will show you how many bytes are used on each block on AVERAGE, assuming you have fresh statistics.
    Now, do yourself a favour and don't use ALTER TABLE MOVE unless:
    • You try it out and you actually gain something: watch out for pctfree, initrans, and just weird data patterns. You probably gain less than you think
    • You're NOT inserting data again in the near future: Oracle will reuse the blocks' space if you have a reasonable PCTUSED or use ASSM in LOCAL tablespaces
    • You can cope with the outage you'll be generating: ALL your table indexes will be rendered UNUSABLE, you'll have to rebuild them all before they are available again
    For the last bullet, don't forget to rebuild using the COMPUTE STATISTICS clause!!!

    select owner,table_name, pct_free,ini_trans,blocks,empty_blocks,
    round(num_rows*avg_row_len/blocks) avg_data_per_block from dba_tables
    where blocks>10000 --Only check tables with lots of used blocks
    order by avg_data_per_block

    Open in new window

    LVL 34

    Assisted Solution

    "I have learned that every data block allocated to a table may not be completely filled..."
    True!  The values for PCTFREE and PCTUSED control this in combination with how the data in each table is used by the application (and that may be very different from table-to-table).

    "I am trying to write a unix script which will automatically determine tables which are candidate for TABLE reorg."
    This looks like the hard way to get the job done!  This is essemtially an Oracle issue, not an O/S issue, so it may be be much easier to handle in an Oracle *.SQL script that you execute in SQL*Plus, than with a UNIX script that has to invoke SQL*Plus and evaluate the output from SQL commands in order to determine which action(s) to take, if any.

    If the application has some tables that get inserts only, and no updates or deletes, they should be exported, dropped (or renamed) and recreated with PCTFREE=0, then import the exported data (or copy from the renamed table to the new table).  In either case, you need to check for: constraints, grants, triggers and default values in the original table and re-create them for the new table.

    If the application has some "work" or "queue" tables that get lots of inserts and deletes, a simple "truncate" of the table (if it is empty!) may recover the space much easier than a table rebuild.

    If the application has some tables which get inserts of records that have null column values, then they get updated later to non-null values, these may not show up as tables that have unused space, may they nay be a significant performance problem, if they have "chained rows".  Tables like this should be recreated a PCTFREE higher than the default (10%) to avoid row chaining in the future, if you canot change the application to avoid this.
    LVL 3

    Assisted Solution

    IMHO, table and index reorganization is overrated.  If you have Enterprise Edition and can do online table redefinition (DBMS_REDEFINITION) then I'd go for it.  Otherwise, the risk reward ratio stinks and the downtime is not likely to be worth it.
    LVL 34

    Assisted Solution

    Oops!  I just spotted a couple mistakes in the last paragraph of my previous comment.  I intended that to read:
    ""...these may not show up as tables that have unused space, but they may be a significant performance problem..."

    In fact, these can be as big of a performance problem as unused space in a table.
    LVL 5

    Author Closing Comment

    Thank you all for your responses, i have some good stuff to take away from this.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now