I am having a problem with the dba_free_space view. We have a large amount of purging going on in our database recently, and the space is not being reflected in dba_free_space because of the HWM. Is there any better way to get a true measurement of the space used in a situation like this ?
The DBA_FREE_SPACE view will show you how much free space has not been allocated in a tablespace. Unfortunately as you've found out, when you delete data from a table you do not reset the HWM. You also do not free up any space in any extents for that table.
To get more accurate measures of the amount of actual space in a table, use the DBMS_SPACE package, particularly the UNUSED_SPACE procedure of this package. This will tell you how much space *below the HWM* is in the table. For more information on this procedure, refer to the Oracle docs (http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/dbms_sp2.htm#1002300).
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.