CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloper
Cmen535
Cmen535

Reputation: 25

import dmp file failed in oracle[ORA-31685,ORA-39083,ORA-01932,ORA-31684]

What am I doing

I have been given a UAT env., dmp file for local database setup.I followed the procedure described here.

Errors

The below are the errors I am getting, seems like my local username is missing some roles which I am unable to figure out. I did granted ADMIN/DBA privilege to 'SYSTEM' user though.

Master table "SYSTEM"."UAT" successfully loaded/unloaded
Starting "SYSTEM"."UAT":
Processing object type SCHEMA_EXPORT/USER
ORA-31685: Object type USER:"SYSTEM" failed due to insufficient privileges. Failing sql is:
ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'DABD3C3A967782B6' DEFAULT TABLESPACE "UAT01" TEMPORARY TABLESPACE "TEMP01"
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01932: ADMIN option not granted for role 'CONNECT'
Failing sql is:
GRANT "CONNECT" TO "SYSTEM"
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01932: ADMIN option not granted for role 'RESOURCE'
Failing sql is:
GRANT "RESOURCE" TO "SYSTEM"
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
ORA-31685: Object type DEFAULT_ROLE:"SYSTEM" failed due to insufficient privileges. Failing sql is:
ALTER USER "SYSTEM" DEFAULT ROLE "CONNECT", "RESOURCE"
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-31685: Object type TABLESPACE_QUOTA:"SYSTEM" failed due to insufficient privileges. Failing sql is:
DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "SYSTEM" QUOTA UNLIMITED ON "UAT01"';  EXECUTE IMMEDIATE SQLSTR;EXCEPTION   WHEN OTHERS THEN    IF SQLCODE = -30041 THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''UAT01'' AND CONTENTS = ''TEMPORARY''';      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;      IF TEMP_COUNT

After providing the CONNECT,RESOURCE roles to system user WITH admin option, I am getting below errors.

SYSTEM USER WITH ROLES

Master table "SYSTEM"."IMPORT_JOB_SQLDEV_101" successfully loaded/unloaded
Starting "SYSTEM"."IMPORT_JOB_SQLDEV_101":  
Processing object type SCHEMA_EXPORT/USER
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'UAT01' does not exist
Failing sql is:
ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'DABD3C3A967782B6' DEFAULT TABLESPACE "UAT01" TEMPORARY TABLESPACE "TEMP01"
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'UAT01' does not exist
Failing sql is:
DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "SYSTEM" QUOTA UNLIMITED ON "UAT01"';  EXECUTE IMMEDIATE SQLSTR;EXCEPTION   WHEN OTHERS THEN    IF SQLCODE = -30041 THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''UAT01'' AND CONTENTS = ''TEMPORARY''';      EXECUTE IMMEDIATE SQLSTR INTO TEMP

Thanks all in Advance !!

Upvotes: 0

Views: 7925

Answers (1)

Giova
Giova

Reputation: 1127

you need to create all needed tablespaces before importing the dump. The dump import does not create physical files needed by the database

Upvotes: 2

Related Questions