Hi,
I am using Oracle 19c RDBMS and I am trying to add an identity column onto a table in another schema and I get an Insufficient Privileges error.
I am logged in as USER1.
I try to execute:
ALTER TABLE USER2.MYTABLE ADD (HISTORYID NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 MAXVALUE 9999999999999999999 MINVALUE 1 CACHE 20 NOT NULL );
I get:
Error starting at line : 1 in command -
ALTER TABLE USER2.MYTABLE ADD (HISTORYID NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 MAXVALUE 9999999999999999999 MINVALUE 1 CACHE 20 NOT NULL )
Error report -
ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
I have granted the following to USER1:
grant create any sequence to USER1;
grant select any sequence to USER1;
grant alter any sequence to USER1;
grant create any table to USER1;
grant alter any table to USER1;
I even temporarily granted DBA to USER1, none of this has helped.
I tried following the instructions in "Creating IDENTITY Column In Another Schema Fails With ORA-1031, Even With CREATE ANY SEQUENCE Granted (Doc ID 2222821.1)"
but this didn't work. It also refers to creating a table with an identity column in another schema. I am trying to alter an existing table in another schema and add this column.
Could anyone point out what permissions I need?