Data and Temp files near the limit

Posted on 2003-11-10
Last Modified: 2008-03-03
Hi all:

One of my tablespace and temp tablespace are almost full, 99.8%. I have autoextend future enabled on both of them. I noticed some unnecessary data in my tables belonging to my tablespace and deleted them assuming this would release free space. But, it didn't and my tablespace file size is at 99.8% limit to its capacity. Does oracle use free space released by the deleted records? When and how the data in temp space deleted?

Thank you very much for all you pointers!
Question by:iit
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1

Accepted Solution

DrJekyll earned 30 total points
ID: 9717902
The free space in your datafiles has nothing to do with file size. The only way to reduce your file size is to shrink the datafile. You can only shrink to the last used block in your datafile.  Other than that you would have to reorganize your tablespace by rebuilding it.  Oracle will use all free space in the datafile if it is large enough.  The unused portion is just waste and is what causes fragmentation which is no longer an issue nowadays. If your initial=next extent all objects are sized to use those extents you will have very little waste.

You can coalesce the free space in a tablespace via.

alter tablespace <tablespace_name> coalesce.

LVL 35

Expert Comment

by:Mark Geerlings
ID: 9718376
You didn't tell us which version of Oracle you have, and this does vary a bit with different versions.  I've noticed with both Oracle8.1.7 and Oracle9.2 that the temp tablespaces in our system are regularly 99.9% shortly after we restart our database each weekend (or at least be relatively early in the work day on Mondays).

In a data tablespace, just deleting records from tables *WILL NOT* increase the free space, because the space formerly used by those records is still allocated to the tables.  The only way to recover that free space is to export the tables where you have deleted lots of records, then truncate them, then import the data.

Expert Comment

ID: 9721760
Try this. It works for me.

alter tablespace temporary_data default storage (pctincrease 0)

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 9722814
thanks all for the comments.

Sorry for the confusion. I didn't expect the total *file size* to decrease after delteing the records, but just the *used* space. I am using OEM and in the details for Datafiles, it always shows up the *used*space is 99.8%. So, I deleted several thousands records assuming it will free at least some space and that the *used* space would drop to 80% or so. But, it doesn't drop and it is still at 99.8%

markgeer: Oh, I see what you are saying. So, export the table. truncate it and import back. Consider this case, TableA has 1000 records, i wan to delete 990 of them. So, I first export it. How do I truncate it now? Before importing shouldn't I delete the records in my original TableA. If yes, then there is some downtime. Can this downtime be avoided?

johnster_uk: alter tablespace temporary_data default storage (pctincrease 0)
I suppose this wont increase the tablespace size when it is full. This will result in errors when trying to insert records after the tablespace is full. Am I correct? But, I want to increase the tablespace size whenever it is full so that manual intervention isn't necessary.

Thanks againf or all your help!
LVL 35

Expert Comment

by:Mark Geerlings
ID: 9723692
Yes, there will be downtime (or at least some time when some records are unavailable) if you need to delete records and recover free space.  If you plan to delete 90% of the records in a table, your best approach may be to create a new table based on a select that just gets the records you want to keep, then drop the old table and rename the new table.  Remember to issue grants (if needed) for the new table, and to recreate indexes and constraints (if any) and to recompile any views or PL\SQL objects that refer to the table(s) you re-create this way.

If you don't want to recreate all of the grants, indexes and constraints and recompile PL\SQL objects, then you have to either:
1. delete the records you want removed, export the remaining records, truncate the table, then import the table from the export dump file
2. create a work table that just has the records you want to save, truncate the table, then reload it using "select * from [work_table]".

If you are not familiar with the truncate command, try it on a small test table first.  It is a very fast, low-overhead way to remove all records from a table, but be aware that it is *NOT* reversible like a delete would be.  If you accidentally delete records from a wrong table, or the wrong set of records from a table, you can simply "rollback" rather than commit, and your records are restored.  With "truncate" there is no rollback option - it is automatically committed!

Expert Comment

ID: 9723750
I think smon is supposed to free the used space in the temp tablespace but it does not seem to be doing it's job. If you use the command I sent you will see the tablespace being freed up. I have a cron job that does it hourly and it frees the temp tablespace with no problems.

Author Comment

ID: 10267817
Some one please clarify me on this point:

I deleted about 50,000 records in a table. The tablespace size didn't reduce and it is expected behavior (from discussions above). Will the space occupied by the delted records reused later?

Thank you very much!

Expert Comment

ID: 10270123
The tablespace size,i.e., datafiles  will NEVER reduce as a result of deleting records unless you explicitly shrink
the datafiles.  The space occupied by the deleted records can be reused if large enough. However the High Water Mark in the table will not lower as a result of deleting records.  Only truncate and drop/recreate table will reset HWM.

LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 30 total points
ID: 10271401
Don't worry about the lack of free space in the "temp" tablespace.  That is typical in Oracle from version 8.1 and higher.  That space is recovered (freed) when the instance is shut down and restarted.

Free space in a data (or index, or the SYSTEM) tablespace is a different matter.  When you delete records from a table, the free space created is only in that particular table and its index(es) - it is *NOT* general free space that shows up in OEM.  Also, even that space in the table is only usable for new inserts into that same table if the free space in individual database blocks is more than the setting for pctused for that table (the default is 40%).  So if the deleted rows were not contiguous, and the result is that the remaining rows keep each block above 40% full, the "free space" from the delete is not directly usable.  The only way to recover it is to export the remaining rows, truncate the table (this will make the space true free space visible in OEM and usable by any object) then import the remaining rows.

Author Comment

ID: 10283233
Thank you very much DrJekyll and markgeer!
I doubled the points to 60 and gave 30 each.
Your comments are very helpful!!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

732 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