connor_mc_d's picture

When can I use SQL Macros?

One of the very cool features we’ve been talking about for 20c is SQL Macros. But you no longer need to wait for a future release of the database to get access to all the goodness of SQL Macros.

Why? Because much of the functionality has now been backported to 19c, and is also now officially in the documentation so there’s no ambiguity as to whether you are supported to use them or not. They’ll be coming soon to an RU near you </p />
    <div class=»

connor_mc_d's picture

Age calculations – Just how old ARE you?

Any database application that stores information about people will very likely store a person’s date of birth as part of that data. Whether it be for marketing purposes, or part of the security checks for a password reset, or part of the authentication when the inevitable call centre calls you with their “latest, greatest offer”, the date of birth is common.

But when it comes to reporting, typically it is not the date of birth that we present on our screen or hard-copy, but the person’s age. Clearly we cannot store a persons age in the database because it changes every day, month or year depending on the granularity with which you present it. Which leads to the ponderance – Surely it cannot be that hard to calculate someone’s age from their date of birth?

connor_mc_d's picture

The CEIL function, dates and floating point numbers

Oh yes indeed, I have dug deep into the well of cheesy blog post titles for this one Smile. But hey, I have two teenage children so I figure that I have reached the age where you’ll need to permit me my share of terrible Dad-joke style puns.

Consider the two dates below (both of which include a time component):

04-SEP-2020 00:00:00
05-SEP-2020 00:08:02

Let us do some quick arithmetic in our heads. How many minutes are there between those two dates? To avoid any reader headaches I’ll throw in the fact that there are 1440 minutes in a day. So there is one day between them (1440) plus an additional 8 minutes past midnight bringing us to 1448, plus a couple of seconds left over.

connor_mc_d's picture

Docs hack to save you time!

The tech world is a big and varied place, so whilst there are some database customers running on 19c and have applied the latest RUs etc to get them to (at of time of writing) 19.8, there are plenty of customers running on 18c, plenty running on 12.2, plenty on 12.1 and so forth all the way down the version tree.

What this means is that we have a plethora of versions of the documentation available to serve those customers, and since all of those versions are in active use, the various search engines of the world have to decide which versions of the documentation best match the search items you have entered.

Thus if you search for “DBMS_JOB”, then your search engine of choice might show you the 12c version, or the 10.2 version, or the 19c version, all dependent on the multitude of things that search engines use to decide what you should be shown first.

connor_mc_d's picture

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.

connor_mc_d's picture

Frequency histogram – where did that estimate come from?

Frequency histograms in any version of Oracle are pretty cool things, so whenever you have a distribution of data that potentially skewed and the number of distinct values fall under the limit of how many histogram buckets you can have, then a frequency histogram is pretty much a no-brainer. In particular, if you don’t have a large number of distinct values, the nice thing is that you can nominate the largest bucket size possible, and we’ll only create the buckets required to contain the frequency information.

For example, I’ll create table with only 3 distinct values (1,3 and 5) and the distribution of the data is skewed. Then I’ll ask for a 254-bucket histogram, but you can see by querying USER_HISTOGRAMS that only 3 buckets were required to hold the histogram.

connor_mc_d's picture

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.

connor_mc_d's picture

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:

connor_mc_d's picture

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:

connor_mc_d's picture

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


Get your environment setup as per the patch notes.

To prevent automated spam submissions leave this field empty.
Syndicate content