Close Menu
Oracle

ORA-01031 — Insufficient Privileges When Creating an IDENTITY Column

HenriqueBy Updated:4 Mins Read

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

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;

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;

Solution

Grant the CREATE SEQUENCE privilege to the 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.

All Three IDENTITY Variants Require CREATE SEQUENCE

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

VariantBehaviorAlso fails without CREATE SEQUENCE?
GENERATED ALWAYS AS IDENTITYOracle always generates the value; explicit INSERT into the column is blocked✅ Yes
GENERATED BY DEFAULT AS IDENTITYOracle generates the value by default; explicit INSERT is allowed✅ Yes
GENERATED BY DEFAULT ON NULL AS IDENTITYOracle 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;

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,
  ...
);

References

Share.

Related Posts

Add A Comment
Leave A Reply