This article details how to make a “god user” in the database. Creating a god user isn’t something you want to do with your production databases. Generally, you want to follow the principle of least privilege. In other words, only… Read More
View Hidden Parameters in Oracle
Occasionally I need to view hidden parameters in Oracle. There are plenty of queries and scripts that already do this out there. But these queries have to be run as SYS. So when I need to view hidden parameters, I… Read More
KUP-01005: syntax error: found “hash” (How to fix on an External Table)
In this article, I’ll be discussing how to fix the “KUP-01005: syntax error: found “hash”” error when selecting from an external table. Here’s the error in its entirety:ORA-29913: error in executing ODCIEXTTABLEOPEN calloutORA-29400: data cartridge errorKUP-00554: error encountered while parsing… Read More
Finding mod(power(x,y)) for Larger Values
The following piece of Pl/SQL uses a clever bit of math to be able to raise a number to a very large power without having overflow problems. Suppose, for example, you wanted to calculate 2318 % 367 (2 to the… Read More
Writing Efficient Pl/SQL is Hard
In the databases that I currently manage, PL/SQL functions are used everywhere. Honestly, one of the things I do when I try to get serious about tuning a SQL query is to (if it can be easily done) eliminate any… Read More
Selecting From a View: ORA-01031: insufficient privileges
If you’re selecting from a view in a different schema, and you’re encountering ORA-01031: insufficient privileges, this is probably why: You’re logged in under schema “A” You’re trying to select from a view owned by schema “B.” The view in… Read More
Calculating Scalar Subquery Cache Size, Part 2
I’ve written earlier about how to calculate a query’s scalar subquery cache size, and I’ve had an interesting thought since the last blog entry. The last entry was all about scalar subqueries that appear in a query’s select clause, but… Read More
How to Migrate a Baseline From One Database to Another
If you have a query that performs well in in one environment (such as dev) but performs poorly in a different environment (such as prod), it might be because Oracle selects a good plan in dev, and a bad plan… Read More
How to Get A Query’s Execution Plan (With All The Details)
In Oracle, if you want to view an execution plan for a query, you use the DBMS_XPLAN.DISPLAY_CURSOR() procedure. NOTE: The execution plan is totally different than an explain plan; don’t confuse these two. The easiest way to get an execution… Read More
Using DBMS_UTILITY.EXPAND_SQL_TEXT
If you have a query that contains views, or a query where the columns are not all qualified, the DBMS_UTILITY.EXPAND_SQL_TEXT function might be useful. The DBMS_UTILITY.EXPAND_SQL_TEXT function shows you what your query looks like without the views, and with all… Read More