ORA-01031 – GENERATED BY DEFAULT AS IDENTITY

This post is also available at: Português

When creating a simple table, an insufficient privileges error occurred:
ERROR at line 1: ORA-01031: insufficient privileges.
This happened because the command “GENERATED BY DEFAULT AS IDENTITY” requires the user to have, in addition to the CREATE TABLE privilege, the CREATE SEQUENCE privilege.

SQL> create table t5 (id int GENERATED BY DEFAULT AS IDENTITY);
Table created.

In Oracle 12c, the development team has included the Identity Columns feature, similar to the AUTO_INCREMENT column in MySQL or the IDENTITY column in SQL Server. With this introduction, developers can now define a column as an identity column directly during table creation. Here is an example of how to create a table with an identity column:

CREATE TABLE Exemplo (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY,
    Nome VARCHAR2(50)
);

In this example, Oracle defines the ID column as an identity column (IDENTITY) and automatically generates incremental values for this column whenever a new record is inserted into the table.