I’ve always appreciated the CREATE OR REPLACE functionality in Oracle for views, packages and triggers, and I’ve often wanted a similar CREATE OR REPLACE TABLE. Many times, I’ll find myself needing to quickly drop and re-create a table while I’m… 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
How to tell if you have a multi-pass hash join
A hash join can be either an optimal hash join, a one-pass hash join, or a multi-pass hash join. Optimal hash joins – The entire build table fits nicely into memory One-pass hash joins – The build table did not… Read More
Handy trick to avoid ORA-54013: INSERT operation disallowed on virtual columns
In some regards, virtual columns are great: Virtual columns don’t take up physical disk space, which allow for skinnier tables. However, an annoyance with virtual columns is Oracle will give you an ORA-54013 error if you try to insert a… Read More
What is a “Skinny Table” (aka “Narrow Table” or “Strip Table”)?
A skinny table (also called a “Narrow Table” or a “Strip Table”) is a table that has very few columns and/or very narrow columns. This means the table has an small average row length. You can make a skinny table… Read More
ORA-54012: virtual column is referenced in a column expression
ORA-54012 is caused when you try to create a table where one virtual column is attempting to reference a different virtual column. Oracle has created the restriction of preventing one virtual column from referencing another. This helps to prevent complexities… Read More
How to set a Sequence Value in Oracle
You might expect there to be an easy “alter sequence” command to set a sequence to a certain value, but I don’t know of one. Today I ran into a situation where I needed to set a sequence to a… 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