Below is a list of possibilities as to why Oracle might pick a subpar execution plan: Your tables/indexes have missing or stale statistics (or stats are missing in table partitions/subpartitions or index partitions/subpartitions) Your data violates “uniformity” assumptions that Oracle… Read More
ORA-21000: error number argument to raise_application_error of %s%s is out of range
For some reason when using the raise_application_error() function in my code, I can never remember the valid numeric values. The answer is a simple mnemonic that’s in the title of this blog post. The valid range for an error is… Read More
How to Set a Sequence Value in Oracle
If you are running a version of Oracle that is prior to 12.1, there’s no “easy” way to set a sequence value. This changes in Oracle 12.1 though; you can simply issue the ALTER SEQUENCE .. RESTART command to set… Read More
Use DBMS_METADATA to get DDL for Row Level Security (RLS) Policy
I needed to drop a Row Level Security Policy from a table in an Oracle 12.1 database; but before I removed the policy, I wanted to make a backup of the code that would be necessary to restore the policy… Read More
ORA-14047 When trying to rename a table
A convenient way to rename tables in Oracle is to use the RENAME command. For example, However, if the object that you’re trying to rename does not exist in whatever your CURRENT_SCHEMA is currently set to, then you can’t use… Read More
ORA-01439 When trying to change CHAR to VARCHAR2
I recently had a coworker ask me about a strange situation: They were trying to modify a column on a table to go from a CHAR(4 BYTE) to a VARCHAR2(5 BYTE). Oracle, however, didn’t seem to want to cooperate. Oracle… Read More