We help IT Professionals succeed at work.

How to find out Average free space in a table?

cryptosid asked
Medium Priority
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 ?

Watch Question

Senior Oracle DBA
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
You can also  use the built-in DBMS_SPACE ..
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

Mark GeerlingsDatabase Administrator
"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.
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.
Mark GeerlingsDatabase Administrator
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.


Thank you all for your responses, i have some good stuff to take away from this.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.