0

My OS=CENTOS 7
Oracle=18c XE

I am trying to import a DMP file that is exported from XE 11g2 and now trying to import it in 18c XE. Here are the errors:

[root@linux gsw]# impdp user/pass remap_tablespace=SYSTEM:myUserName remap_tablespace=USERS:myUserName dumpfile=myUserName.dmp directory=myUserName

Import: Release 18.0.0.0.0 - Production on Thu Jun 4 01:09:34 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "myUserName.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1142
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "SYS.KUPV$FT", line 1035
ORA-06512: at "SYS.KUPV$FT", line 1023

What I did is:

create TABLESPACE myUserName
DATAFILE '/opt/oracle/oradata/XE/myUserName01.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;

ALTER DATABASE 
DATAFILE '/opt/oracle/oradata/XE/myUserName01.dbf'
AUTOEXTEND ON
MAXSIZE UNLIMITED;

SQL> ALTER USER myUserName quota unlimited on myUserName;

User altered.

SQL> create user myUserName identified by pass;

User created.

SQL> grant connect, resource to myUserName;

Grant succeeded.

SQL> grant read, write on directory myUserName to myUserName;

Grant succeeded.

SQL> grant create database link to myUserName;

Grant succeeded.

SQL> grant create table to myUserName;

Grant succeeded.

UPDATE-1

SQL> GRANT CREATE ANY TABLE TO myUserName;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO myUserName;

Grant succeeded.

Now the errors changed as:

Import: Release 18.0.0.0.0 - Production on Thu Jun 4 01:39:50 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/opt/oracle/admin/XE/dpdump/myUserName/myUserName_110516.dmp" may be an original export dump file

Best Regards

2
  • Did you create it on the CDB or the PDB? Are you importing into the PDB? Jun 3, 2020 at 23:27
  • I have exported as expdp myUserName/myUserName DIRECTORY=myUserName DUMPFILE=myUserName.dmp logfile=myUserName.log SCHEMAS=myUserName Jun 3, 2020 at 23:29

2 Answers 2

0

Add the following to the impdp command:

VERSION=11.2.0
2
  • Please expand on this answer - references and quotes from websites for example!
    – Vérace
    Jun 4, 2020 at 4:01
  • @pmdba VERSION=11.2.0 not works impdp user/pass remap_tablespace=SYSTEM:USER remap_tablespace=USERS:USER dumpfile=file_110516.dmp directory=user VERSION=11.2.0 same error throwing as I written in my UPDATE-1 part of my question Jun 4, 2020 at 8:09
0

Problem #1: Trying to stored objects into a tablespace on which your have no quota.

While you created a tablespace for your account, you did not make that the default tablespace for your user so, when creating objects without an explicit tablespace clause, it would have tried to put them into a [wrong] tablespace on which the account had no quota. Boom!

Problem #2: bad dump file specification

You cannot specify the full path to the dump file, as we once did with imp.

Instead you use a [database] Directory object and place the dump file into the [file system] directory referenced by that Directory:

SQL> create or replace directory MY_DIR 
     as '/opt/oracle/admin/XE/dpdump/myUserName' ; 

SQL> grant read, write on directory MY_DIR to myUserName ; 

$ impdp user/pass \
    directory=MY_DIR \
    dumpfile=myUserName.dmp \
    remap_tablespace=SYSTEM:myUserName \
    remap_tablespace=USERS:myUserName \

I would suggest that "borrowing" objects from the SYSTEM tablespace is Bad Idea.
I do not see why you would ever want to do this.

If you're going to be doing this on a regular basis, I would strongly recommend you look into Wallets for storing credentials, rather than specifying on the command-line.

4
  • Phill W. this is output of your recommendations Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. UDI-01017: operation generated ORACLE error 1017 ORA-01017: invalid username/password; logon denied Username: system/password Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39143: dump file "/opt/oracle/admin/XE/dpdump/gsw/gsw_110516.dmp" may be an original export dump file Jun 4, 2020 at 10:38
  • "ORA-39143: dump file "/opt/oracle/admin/XE/dpdump/gsw/gsw_110516.dmp" may be an original export dump file" Was the file created by expdp or by the original exp (which won't work).
    – Phill W.
    Jun 4, 2020 at 11:19
  • W. Its working when I use imp with one warning Jun 4, 2020 at 14:23
  • Then you need to use expdp to create another dump file. The one that you have, created by imp, will not work with impdp.
    – Phill W.
    Jun 5, 2020 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.