Oracle: Setting Up Target User Privileges

When making import into the target database you need to be sure that the target database and all the required schemas exist on the target server. Also the user, that you utilize to connect to the target database and to load all the objects and data, should have appropriate privileges to be able to do that.

Below is the list of privileges that are required to make import into the target database.

In Oracle database a new user can be created following the below statement:

create user iq2ora identified by ora
DEFAULT TABLESPACE USERS QUOTA 100M ON USERS
TEMPORARY TABLESPACE TEMP QUOTA 10M ON TEMP

Then you will need to assign connect privileges and create/alter/delete privileges to that user, so that it will be able to connect and create/alter/delete all types of the objects. To assign these privileges you can use the statements below:

GRANT CREATE SESSION TO iq2ora
/
GRANT CREATE TABLE TO iq2ora
/
GRANT CREATE PROCEDURE TO iq2ora
/
GRANT CREATE TRIGGER TO iq2ora
/
GRANT CREATE VIEW TO iq2ora
/
GRANT CREATE SEQUENCE TO iq2ora
/
GRANT ALTER ANY TABLE TO iq2ora
/
GRANT ALTER ANY PROCEDURE TO iq2ora
/
GRANT ALTER ANY TRIGGER TO iq2ora
/
GRANT ALTER PROFILE TO iq2ora
/
GRANT DELETE ANY TABLE TO iq2ora
  /
GRANT DROP ANY TABLE TO iq2ora
/
GRANT DROP ANY PROCEDURE TO iq2ora
/
GRANT DROP ANY TRIGGER TO iq2ora
/
GRANT DROP ANY VIEW TO iq2ora
/
GRANT DROP PROFILE TO iq2ora
/

Or you can just grant DBA privilege to your Oracle user:

GRANT DBA TO user_name. 

If you have any other questions, please contact us: support@ispirer.com