Search

Top 60 Oracle Blogs

Recent comments

DBA

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:

Upgrading…Its Time!

Gough Whitlam was an Australian politician who rose to power in the 1970s with the campaign slogan “It’s Time!”. Politics aside, it loosely ran on the premise that not to have the occasional dramatic change ultimately leads to stagnation in social and economic progress.

428px-Gough_Whitlam_1973

Say NO to default DATE formats

Today’s quick but important message is…don’t be lazy!

Look….I get it Smile

If given the choice of typing:


'01-JUL-20'

or typing this:


to_date('01-JUL-20','DD-MON-RR')

then more often than not, we all are going to opt for the first alternative because we all can get lazy. But that reliance on the default date format mask in a session can be fraught with danger. Because a script that worked in one circumstance, can silently fail in another.