ORA-01031- IDENTITY Column Needs CREATE SEQUENCE

This post is also available in: Português (Portuguese (Brazil))

You try to create a table with an identity column and Oracle throws a privilege error- even though you have CREATE TABLE. Here’s why it happens and how to fix it.

SQL> CREATE TABLE orders (
  2    id   INT GENERATED BY DEFAULT AS IDENTITY,
  3    desc VARCHAR2(100)
  4  );
  id   INT GENERATED BY DEFAULT AS IDENTITY,
*
ERROR at line 2:
ORA-01031: insufficient privileges

SQL> CREATE TABLE orders (
  2    id   INT GENERATED BY DEFAULT AS IDENTITY,
  3    desc VARCHAR2(100)
  4  );
  id   INT GENERATED BY DEFAULT AS IDENTITY,
*
ERROR at line 2:
ORA-01031: insufficient privileges

The error appears even with CREATE TABLE granted. The culprit is a different privilege that’s easy to overlook.

Why It Happens

Identity columns were introduced in Oracle 12c as a shorthand for the classic sequence + default pattern. Under the hood, when Oracle creates an identity column, it automatically creates an implicit sequence tied to that column. That sequence is managed by the database, but it’s still a sequence- and creating one requires the CREATE SEQUENCE privilege.

Without it, CREATE TABLE fails with ORA-01031 before the table itself is even touched.

Diagnosis

Check the current user’s system privileges:

-- System privileges for the current session
SELECT privilege
FROM   session_privs
ORDER BY privilege;

-- System privileges for the current session
SELECT privilege
FROM   session_privs
ORDER BY privilege;

If CREATE SEQUENCE is not in the list, that’s the issue.

To check a specific user (as DBA):

SELECT privilege
FROM   dba_sys_privs
WHERE  grantee = 'MY_USER'
ORDER BY privilege;

SELECT privilege
FROM   dba_sys_privs
WHERE  grantee = 'MY_USER'
ORDER BY privilege;

Solution

Grant the CREATE SEQUENCE privilege to the user:

-- Connected as DBA
GRANT CREATE SEQUENCE TO my_user;

-- Connected as DBA
GRANT CREATE SEQUENCE TO my_user;

After the grant, table creation works as expected:

SQL> CREATE TABLE orders (
  2    id   INT GENERATED BY DEFAULT AS IDENTITY,
  3    desc VARCHAR2(100)
  4  );

Table created.

SQL> CREATE TABLE orders (
  2    id   INT GENERATED BY DEFAULT AS IDENTITY,
  3    desc VARCHAR2(100)
  4  );

Table created.

All Three IDENTITY Variants Require CREATE SEQUENCE

The behavior is identical across all three identity column forms available since Oracle 12c:

Variant	Behavior	Also fails without CREATE SEQUENCE?
`GENERATED ALWAYS AS IDENTITY`	Oracle always generates the value; explicit INSERT into the column is blocked	✅ Yes
`GENERATED BY DEFAULT AS IDENTITY`	Oracle generates the value by default; explicit INSERT is allowed	✅ Yes
`GENERATED BY DEFAULT ON NULL AS IDENTITY`	Oracle generates the value when NULL is passed on INSERT	✅ Yes

Regardless of which variant you choose, Oracle always creates an implicit sequence behind the scenes- and CREATE SEQUENCE is always required.

How to Reproduce in a Lab

-- 1. Create a test user (connected as DBA)
CREATE USER lab_identity IDENTIFIED BY lab123;
GRANT CREATE SESSION TO lab_identity;
GRANT CREATE TABLE    TO lab_identity;
-- Intentionally WITHOUT CREATE SEQUENCE

-- 2. Connect as lab_identity and reproduce the error
sqlplus lab_identity/lab123@ORCLPDB1

CREATE TABLE t_identity (
  id   INT GENERATED BY DEFAULT AS IDENTITY,
  name VARCHAR2(50)
);
-- Expected: ORA-01031: insufficient privileges

-- 3. Switch back to DBA and grant CREATE SEQUENCE
CONNECT / AS SYSDBA
GRANT CREATE SEQUENCE TO lab_identity;

-- 4. Reconnect as lab_identity and confirm the fix
sqlplus lab_identity/lab123@ORCLPDB1

CREATE TABLE t_identity (
  id   INT GENERATED BY DEFAULT AS IDENTITY,
  name VARCHAR2(50)
);
-- Expected: Table created.

-- 5. Confirm the implicit sequence created by Oracle
SELECT sequence_name, min_value, max_value, increment_by, cache_size
FROM   user_sequences;

-- Cleanup
DROP TABLE t_identity;

-- 1. Create a test user (connected as DBA)
CREATE USER lab_identity IDENTIFIED BY lab123;
GRANT CREATE SESSION TO lab_identity;
GRANT CREATE TABLE    TO lab_identity;
-- Intentionally WITHOUT CREATE SEQUENCE

-- 2. Connect as lab_identity and reproduce the error
sqlplus lab_identity/lab123@ORCLPDB1

CREATE TABLE t_identity (
  id   INT GENERATED BY DEFAULT AS IDENTITY,
  name VARCHAR2(50)
);
-- Expected: ORA-01031: insufficient privileges

-- 3. Switch back to DBA and grant CREATE SEQUENCE
CONNECT / AS SYSDBA
GRANT CREATE SEQUENCE TO lab_identity;

-- 4. Reconnect as lab_identity and confirm the fix
sqlplus lab_identity/lab123@ORCLPDB1

CREATE TABLE t_identity (
  id   INT GENERATED BY DEFAULT AS IDENTITY,
  name VARCHAR2(50)
);
-- Expected: Table created.

-- 5. Confirm the implicit sequence created by Oracle
SELECT sequence_name, min_value, max_value, increment_by, cache_size
FROM   user_sequences;

-- Cleanup
DROP TABLE t_identity;

Quick Checklist

-- 1. Check session privileges
SELECT privilege FROM session_privs ORDER BY privilege;

-- 2. Grant CREATE SEQUENCE (as DBA)
GRANT CREATE SEQUENCE TO my_user;

-- 3. Re-run the table creation
CREATE TABLE my_table (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  ...
);

-- 1. Check session privileges
SELECT privilege FROM session_privs ORDER BY privilege;

-- 2. Grant CREATE SEQUENCE (as DBA)
GRANT CREATE SEQUENCE TO my_user;

-- 3. Re-run the table creation
CREATE TABLE my_table (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  ...
);

Search

Rolling Upgrade: Percona Server for MongoDB 8.0.23-10 to 8.0.26-11

Clone a MongoDB User and Preserve the Password Hash

ORA-06553: DBCA Fails on catalog.sql with PLS-213

ORA-01031- Insufficient Privileges When Creating an IDENTITY Column

ORA-06553: DBCA Fails on catalog.sql with PLS-213

Oracle Data Pump: How to Remove Orphaned Jobs from the Database

Oracle SQL Patch: Inject Optimizer Hints Without Changing Code

ORA-29548- How to Fix “Java System Class Reported” in Oracle Database

INS-06006 – Passwordless SSH Connectivity Not Set Up

PRVG-2002- How to Fix “Encountered Error in Copying File” in Oracle RAC