Can you use an index range scan with a LIKE if you have a leading wildcard in your expression? e.g. Most people would say “no” but, I actually found a way to “trick” Oracle into doing so using bind variables,… Read More
Oracle: Coding Around NULL Values
In Oracle, NULLs can complicate your code–a lot. Suppose, for example, you want to see if two different values are the same in a SQL query. You might be tempted to write something like this: The problem with the above… Read More
The *LOWEST* Possible Cost For a Query
Quick: If you had to pick the least-expensive-cost query you could think of, what would that query look like? …(hums Jeopardy theme)…. Ding! Time’s up!! Most people respond with something “select from a one-block table.” Let’s give it a shot:… Read More
Get the Oracle Database Version Number
If you’re looking for how to get the Oracle Database Version, there are lots of ways to do so, depending on what your end-goal is. Running A Quick Query The easiest, simplest, fastest method is to run the following query:… Read More
ORA-28104: input value for statement_types is not valid.
If you’re receiving the ORA-28104: input value for statement_types is not valid error, it might be because you are trying to apply a row-level security policy to a table in a database that has been upgraded to 12.2 or greater… Read More
Are Single-Table Hash Clusters USELESS?!?
I remember being at an Oracle conference a few years ago, and I attended a session that was related to SQL Tuning. One of the things that was discussed in this lecture was Oracle Single Table Hash Clusters, and I… Read More
Calculating Scalar Subquery Cache Size
Whenever you have a scalar subquery in Oracle, there are a few different options that Oracle can use for executing the subquery. Oracle can either run the query once for each row returned by the records in the from clause.… Read More
ORA-01031: insufficient privileges
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