Posts

Showing posts from April, 2017

Temporarily Disable a Generated Always As Identity Column

Oracle Database 12c allows you to define a column to be an IDENTITY column.
It can be either  GENERATED ALWAYS or BY DEFAULT.

A GENERATED ALWAYS AS IDENTITY column does not allow you to INSERT a value for it.
When it comes to moving data from one table to another and this type of column is present, there are a few steps to "disable" the GENERATED ALWAYS AS IDENTITY column and after the data move "enable" it.

set echo on drop table test_identity; create table test_identity(id number generated always as identity); --This insert will fail as the column is generated always --and it does not accept values insert into test_identity(id) values(1); --Change the identity column to generated by default. --This will allow us to insert our values alter table test_identity modify id generated by default as identity; --This insert works. as will Copy to Oracle insert into test_identity(id) values(1); select * from test_identity; --Change the identity column back to gener…