Importing sql file to a new user throws insufficient privilege error in sqldeveloper

Question

I have created a user in AWS oracle RDS and have my tables,functions and all other code. As I needed the same replica of this DB structure, I exported it to an sql file using SQL Developer. Created another user, and now want to import the sql file to this new user, but it is throwing insufficient privilege error.

ORA-01031: insufficient privileges

1031. 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 privileges to the user. Is it because of the user1 which is present in the create table statement?

CREATE TABLE "user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) )

Could you please help. Thank you, Manju

Answer 1

CREATE TABLE"user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) )`

This statement builds a table in the "user1" schema. The command will fail if the current schema is not "user1" and the current user lacks the CREATE ANY TABLE privilege.

how can we have separate users with same schema and different data?

A schema is a set of objects owned by a user. A schema has the same name as the user which owns it. To achieve your aim all you need to do is remove "user1". from all the SQL statements, then run the script as USER2 (or whoever).

For future reference, the SQL Developer Export DDL tool has a checkbox for including the schema name; by default it is ticked (i.e. include) but we can unset it if we will want to run the scripts as different users.