My Oracle Support Banner

Table with Identity column creation fails with ORA-01031 (Doc ID 2853974.1)

Last updated on MARCH 08, 2022

Applies to:

Autonomous Database on Shared Infrastructure - Version N/A and later
Information in this document applies to any platform.

Goal

Two schema created and one schema is granted with create any table privilege to create table in another schema

The create table creation fails with ORA-1031 even though create any table privilege granted.

SQL> create user tcusera identified by xxx;

User created.

SQL> create user tcuserb identified by xxx;

User created.

SQL> alter user tcusera quota unlimited on DATA;

User altered.

SQL> alter user tcuserb quota unlimited on DATA;

User altered.

SQL> grant create session,create any table,create any index to tcusera;

Grant succeeded.

SQL> connect tcusera/xxx@<servicename>
Connected.

SQL> create table tcuserb.emp(person_id number generated by default as identity,first_name varchar2(50) not null,last_name varchar2(50) not null,primary key (person_id));
create table tcuserb.emp(person_id number generated by default as identity,first_name varchar2(50) not null,last_name varchar2(50) not null,primary key (person_id))
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.