ORA-01031: insufficient privileges is probably my least favorite error, because more often than not, it’s an indication of a people problem, and people problems are oftentimes more difficult to fix than technical problems. Having said that, sometimes people issues can… Read More
ORA-30354: Query Rewrite Not Allowed on SYS Relations
The most common reason people get the ORA-30354: Query Rewrite Not Allowed on SYS Relations error is because they’re trying to create a materialized view on a table that exists in the SYS schema. This is a security feature in… Read More
ORA-65049: Creation of local user or role is not allowed in this container
If you’re getting an ORA-65049 error, you’re probably trying to create a local database user (a local database user is a user without a c## prefix) in the CDB$ROOT container for a multi-tenant database. A few solutions exist: If you’re… Read More
“ORA-00904: invalid identifier” with FORALL Loop
Spent an embarrassingly long period of time on an error with a FORALL loop. Can you see what my error is? Every time I ran the above block of code, I would get the following error: Error report – ORA-06550:… Read More
How to Accidentally “Untune” a Query
Suppose one day that you’re at your desk, when you get a telephone call from someone complaining that a query is running slow. You’re given the following setup: And the query that you’re trying to tune looks like this: If… Read More
Filters in the JOIN Clause vs. WHERE Clause: Does It Matter?
Here’s a question I’ve been asked multiple times from multiple people: “Does it matter I put filters in the join clause vs in the WHERE clause? And if so, which one is better?” Example: vs. So does it matter? Answer:… Read More
When to Use APPROX_COUNT_DISTINCT
Since Oracle 12.1, users have had the ability to use the APPROX_COUNT_DISTINCT() function. This function is supposed to be significantly faster than a COUNT(DISTINCT …). Does it really offer that big of a payout? Let’s see: First, let’s make a… Read More
How to Install Oracle 19c on a DigitalOcean Droplet Without ASM (Easiest Install Ever With Tons of Pictures)
If you don’t already have a DigitalOcean account, you’ll want to use this link here to sign up: https://m.do.co/c/97b9d595c7d3 If you use the special link above to sign up, you will get $100 of credit over a 60-day period. Disclaimer… Read More
Over-The-Fence Tuning
One of the best tips on SQL Tuning I can offer you is this: DO NOT DO OVER-THE-FENCE TUNING. It’s taken me years to finally figure this out. What do I mean by over-the-fence SQL Tuning? It goes like this:… Read More
What is Cost? How Can I Use It to Tune SQL?
When most people first start learning to tune SQL, they develop a fascination with this mysterious column on the execution plan called “cost.” What is cost? And how can you use it to tune SQL? So here’s, high level, what… Read More