Top 60 Oracle Blogs

Recent comments


Free space requirements for ALTER TABLE

One of the very cool features from 12.2 onwards is that the vast majority of segment operations that require rewrite of the existing data can be done online without interruption to service. But whether an operation is being done online or not, since DDL operations need to be atomic, the general rule is that when an object is being reorganised this work cannot be done in place. The reorganised segment must be written elsewhere in order for the original segment to be left untouched in the event of the operation failing midway. (No-one wants their table to vanish just because someone pulled out the wrong power cable!)

General consensus from the “blog-sphere” is that you thus need enough space to hold the existing segment and space for the new segment to be created, but I thought I’d explore that a little further, and there’s a few surprises.

Why you keep getting "ORA-01653: unable to extend table"

Space management used to be hard

Way back in the good old days…In fact, before I continue, rest assured that whenever a database person says “good old days” what they really mean is a time totally warped by their imagination where they have managed to block out all of the horror stories and problems they faced, to only remember the tiny nugget of positivity about some antiquated version of the database, so that they come to you and say … “Way back in the good old days” Smile. But I digress…

How to make queries to DBA_FREE_SPACE faster

The other day as my PC popped up its usual “Hey, you really should look at buying a bigger disk” Smile message, I was motivated to see if I could clear down some of the space utilization on the myriad of databases on my machine. On the majority them I encountered no issue doing that, but on one of them, the query to DBA_FREE_SPACE revealed an odd performance issue: