1

I am trying to import an oracle 11g dump file using impdp utility but while doing so, inter alia, I am facing two major errors:

  1. First, It is showing the following error:
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE:"HIS_USER" failed to create with error:
ORA-01119: error in creating database file '/oracle/app/oracle/oradata/dwhrajdr1/his_user13.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

so to solve this, I have created the tablesapce with same name but now it is showing that 'HIS_USER' tablespace already exists.

  1. Second, I am getting thousands of errors, where it is showing user or role does not exist:
Failing sql is:
GRANT EXECUTE ANY ASSEMBLY TO "DSS"
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'DSS' does not exist

Please suggest how to solve these errors! How can I import the dumpfile without making hundreds of users/roles or tablespaces?

chandersheel
  • 35
  • 6

1 Answers1

1

you can generate sql statement using impdp the following way.

http://www.dba-oracle.com/t_convert_expdp_dmp_file_sql.htm

then adjust parameter accordingly.

scott

scott yu
  • 125
  • 1
  • 3
  • I have generated SQL statement where I can see that there are numerous tablespaces, schemas, profiles, roles etc. and then each tablespace has many datafiles. Likewise there are hundred of other objects. My requirement is to import only data/tables and not the whole database. Is there any easy way, where I can avoid making all the tablespaces, schemas and other objects?? – chandersheel Nov 04 '20 at 06:59
  • 1
    for impdp only table , you can use impdp username/password dumpfile=mydumpfile.dmp logfile=mylogfile directory=mydirectory tables=schema.table1,schema.table2 – scott yu Nov 04 '20 at 13:25