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
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