Search code examples
oracle-databaseoracle11goracle-sqldeveloperprivileges

ORA-01031: insufficient privileges-creating a table?

I have schema X, I have created table A,B,C in it. Table A references table B and C. Now as per my current requirement I need to move table A to Schema Y. I don't want to change the table definition here.

I am trying to create table A in schema Y but getting

ORA-01031: insufficient privileges

To provide grant I have run

GRANT SELECT,INSERT,UPDATE,DELETE ON X.B TO Y; 
GRANT SELECT,INSERT,UPDATE,DELETE ON X.C TO Y

Still I am not able to create table A in schema Y. I am getting

ORA-01031: insufficient privileges

I am stuck here . Please help me in that

Solution

  • If table A references tables B and C (which now reside in a different schema), I presume you're talking about foreign key constraints. If that's so, then privileges you granted won't help. You need to grant the REFERENCES privilege, i.e.

    grant references on b to y;