Here’s a fun little experiment (I’m using Oracle 19.0 for this). First, let’s start off by creating a couple of tables. Cool! Now that we got our tables, let’s join them in a query and look a the plan. Here’s… Read More
How To Do a CREATE OR REPLACE TABLE In Oracle
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
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