Shrinking data files oracle

In that case, we will need to rebuild few tables or indexes to free up the free space. We can check for highest allocated extent in datafile using the below query and try moving that table or index and then again trying the resize statement. Check highest allocated extent in datafile likely slow when having many extents. I hope this will be helpful on how to shrink datafile in oracle. Please do provide the feedback and problems you face while doing this. Related Articles tablespace in oracle : This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements ORA : ORA error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database.

The question remains: Which data files do you want to alter? At this point, you can use a generally accepted standard, which allows tablespaces to be 70 percent to 90 percent full. If a tablespace is below the 70 percent mark, one way to bring the number up is to de-allocate some of the space. So how do you achieve percent full? While there are a number of different ways, simple is usually ideal.

Here's how it works. Amount of data file space used:. Total available data file space:. According to the command, a selection has been made based on tablespace. What if you want to resize based on file? It's crucial to remember that multiple files can exist in any tablespace. Plus, only space that is after the last data block can be de-allocated. So the next step should be to find the last data block:.

Now that the command to find the last data block has been inserted, it is time to find the free space in each file above that last data block:.

Select a. So far, so good. How is it possible, then, to combine commands to ensure the correct amount will be resized? In fact, it's fairly easy. Double Checking the Data file shrink. Now, the thing to do is ensure that the right amount of space - not too much, not too little - has been de-allocated. The rule of thumb to follow: Do not go above 70 percent of the tablespace being used. One last thing to do: Add a statement to indicate what is being changed.

At last, here's a script that will create the script. Even so, it's important to pay careful attention when applying the created script. Because Rollback, System and Temporary tablespaces are vastly different creatures, and each should not necessarily be held to the 70 percent rule. By the same token, there might be a very good reason for a tablespace to be over allocated -- like the giant load that will triple the volume tonight. A word of caution, too: Be sure that extents can still be allocated in each tablespace.

There may be enough free space, but it may be too fragmented to be useful. That problem will be the focus of another article. Oracle Training from Don Burleson The best on site " Oracle training classes " are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!