Search

Top 60 Oracle Blogs

Recent comments

DBA

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:

image

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:

How to do a GRANT on an entire schema

TL;DR: There is no schema grant command in Oracle, but this routine lets you get close.

Why no schema level grant?

A very common request on the various forums that exist for the Oracle Database is a “schema level grant”. The idea is that you could do something like


grant select on HR to SCOTT;

The concept sounds relatively straightforward but there would ne some nuances to cover off, for example:

How to upgrade to Oracle 19.8 on Windows

As always, you should follow the instructions in the README.html that comes with the Release Update, but because I know many people like to see what the experience of others is like, here’s my run of the patch application.

As always don’t forget to run your CMD prompt as Administrator, otherwise OPatch will typically have trouble accessing and locking the software inventory

Prerequisites

Get your environment setup as per the patch notes.

Modifying Scheduler Windows

There are a few possible reasons why you might be one of those people why you might be using your database late in the evening. These include

Level up your audit trigger game

A weekend audit

Often if you see the words “weekend audit” in a non-technical sense, it means that awkward moment where you look at the state of your house/bedroom/bathroom/bookshelf/shed etc and say to yourself “Yup, it is time we had a weekend audit of all this crap” Smile

DDL gets my goat

First things first. If you are not familiar with the term “gets my goat” then you can find a definition here Smile. But put simply, it means things that you find frustrating or bothersome.

What is getting my goat today is the time it takes to get the DDL for a database object using the DBMS_METADATA package. It always seem slow to me. Now I must admit, this is very much me exhibiting “never happy” syndrome, because before DBMS_METADATA came along, generating DDL from the database was an absolute pain in the proverbial. Your choices were either: