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 the require rewriting 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.

Video : Using Expressions in Initialization Parameters in Oracle Database 21c

In today’s video we demonstrate using expressions in initialization parameters, introduced in Oracle database 21c.

The video is based on this article.

The star of today’s video is Deiby Gómez, who is a fellow Oracle ACE Director, and was kind enough to take me sightseeing when I visited Guatemala for a conference.



New Year … new idea … new channel

I’ve done a lot of presentations over the years, written a ton of blog posts, and over the past  5 years cranked out hundreds of tech videos of my YouTube channel.

But with the current state of the world, I also know that over the last year we’ve been awash with tech content that now comes exclusively over a virtual medium that generally requires us to stop our “day jobs” in order to focus on the content, whether it be a live presentation, recorded video or reading a long form blog post.

The invisible restore point

I regularly create and drop tablespace on my databases here at home, mainly because they are obviously not used in a “Production-like” manner.  Typically I’m tinkering with tablespaces to help answer customer questions on AskTOM about space allocation, reclaiming space and the like, so tablespaces come and go frequently. (Sidebar: If you’re regularly dropping/creating tablespaces on your Production database, and its not for the purposes of transportable tablespaces, then please drop me a line with why, because most of the time its not a great strategy)

This morning I went to drop a tablespace I had used to answer a question and this popped up.

The AskTOM multimedia experience!

Most people know AskTOM as the go-to resource for getting answers to the most common and sometimes the most tricky questions on the Oracle Database. AskTOM runs on the robust Application Express architecture, which is a large part of the reason that it has been in operation for over 20 years without any problems in terms of upgrades, obsolescence etc. It just keeps….on….working.

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…

Compression default settings

If you are loading a data warehouse or similar environment where your default approach would probably be to compress all objects because they are predominantly read only, you do not need to head back into your DDL scripts and add a “COMPRESS” clause to every table.

You can set the default compression level at the tablespace level. Here’s an example of that in action

DBMS_JOB and 19c – code changes needed

Here’s a “gotcha” brought to my attention by one of our AskTOM readers. I’ve mentioned in the past that DBMS_JOB, having been deprecated in favour of DBMS_SCHEDULER, got a new lease of life in 19c because under the covers we translated calls to create a job under DBMS_JOB to the same mechanism in DBMS_SCHEDULER.

The benefit of that is that we don’t need to maintain our older DBMS_JOB code base, but your existing code is fine to keep running. However, as I said in the other post, you do need to alter your privileges, but here is another discovery that might impact you as well.

How to control resource usage on Autonomous

When you connect to your Autonomous Database, you get to choose from some predefined services. The services available depends on whether you are using a transaction processing (ATP) or a data warehouse instance (ADW) of the database, but for example, for an ATP database you get the following:


Note: This is a screen of the docs as of time of writing this post. Over time, that may change so always be sure to consult the docs directly in future.

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: