Solved

Data and Temp files near the limit

Posted on 2003-11-10
10
1,662 Views
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!
0
Comment
Question by:iit
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 5

Accepted Solution

by:
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.

DJ
0
 
LVL 34

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.
0
 

Expert Comment

by:johnster_uk
ID: 9721760
Try this. It works for me.

alter tablespace temporary_data default storage (pctincrease 0)

0
 

Author Comment

by:iit
ID: 9722814
thanks all for the comments.

DrJekyll:
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!
0
 
LVL 34

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
or
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!
0
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.

 

Expert Comment

by:johnster_uk
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.
0
 

Author Comment

by:iit
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!
0
 
LVL 5

Expert Comment

by:DrJekyll
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.

DJ
0
 
LVL 34

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.
0
 

Author Comment

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

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

757 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

21 Experts available now in Live!

Get 1:1 Help Now