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 grant the permissions that a user needs. This prevents someone from doing accidental damage (e.g. accidentally dropping a column). Also, if someone compromises your account, it limits the damage an attacker can do. The following script will create a user if the user doesn’t already exist. If the user does exist, this script will convert them into a “god user.” The sc ..read more

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 often find myself having to use SQL*Plus to do this. Running queries on SQL*Plus is a bit of an ordeal, because then you have to worry about formatting the results of your query to fit your console window. Here is a query that formats everything to fit nicely in my full-screen PuTTy window in SQL*Plus: set linesize 400 set pagesize 10000 col parameter_name for a64 col parameter_desc ..read more

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 callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "hash": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, lls, lls_compat, no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc ..read more

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 318th power, mod 367). SQL ought to be able to handle this computation, since the answer will only be between 1 and 366 inclusive. But it runs into issues when we try to raise 2 to the 318th power: Oracle starts losing precision with large numbers Oracle is unable to handle really, really large numbers with precision, so we get a bunch of trailing zeros at the end of our number. So if we t ..read more

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 PL/SQL functions and try to re-write them as “plain SQL” expressions. Example Exercise I’m using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production for this example. Let’s start with an EMPLOYEES table. create table employees ( employee_id constraint pk_employees primary key , employee_name , employee_salary ) as -- Using the "generator" techniqu ..read more

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 schema “B” contains tables owned by schema “C.” Schema “B” has SELECT privileges on the tables owned by schema “C” but not SELECT…WITH GRANT OPTION. You can fix the error by using the query below to figure out which grants you need. CAUTION: The below query is a bit like killing a fly with a sledgehammer, because it lists ALL the grants needed. Think befor ..read more

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 the same mechanism exists for an unnested subquery that uses a FILTER operation. My thought was this–If I have a query that uses 1 table within a FILTER operation, I’d have 1024 buckets (same as what we had with the scalar subquery cache size) but…occasionally, a FILTER operation will call multiple tables. My thought is–if I have more than one table in my scalar ..read more

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 in prod, and it might be necessary to migrate a baseline for the good plan to prod. That’s not the *only* reason why a plan might perform well in one environment vs another…for example, a dev database might have a smaller amount of data; therefore, queries might run faster. But if you want to try to get an apples-to-apples comparison, one way would be to capture a baseline from the environ ..read more

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 plan is to do the following. If you’re using SQL*Plus as your database client, you’ll first need to do a SET SERVEROUTPUT OFF. This is also true if you have a database client that emulates SQL*Plus, such as Toad or SQL Developer. By setting the server output off, it makes it easy to capture the last-run-query’s execution plan without serveroutput interferi ..read more

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 the columns qualified. Note that views do not offer any performance advantage over manually writing out the query using the underlying tables. In fact, if a view contains unnecessary tables, a view can actually be a performance disadvantage. Looking at your query’s expanded text will help you to understand if you have a well-written qu ..read more