Data and Temp files near the limit

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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
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.
Try this. It works for me.

alter tablespace temporary_data default storage (pctincrease 0)

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

iitAuthor Commented:
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!
Mark GeerlingsDatabase AdministratorCommented:
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!
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.
iitAuthor Commented:
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!
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.

Mark GeerlingsDatabase AdministratorCommented:
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.
iitAuthor Commented:
Thank you very much DrJekyll and markgeer!
I doubled the points to 60 and gave 30 each.
Your comments are very helpful!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.