Search code examples
oracle11goracle-xetablespaceimpdpexpdp

ORA-00959: tablespace 'USERS' does not exist. But I have it in select results

I'm trying to import a DB dump {Oracle XE 11g (11.2.0.2.0)}, created by using the EXPDP command. Following is the command that I used to import.

impdp vnp/vnp directory=MY_DATA_PUMP_DIR dumpfile=EXPDP_DUMP_26_01_2018.DMP remap_schema=VNP_ADMIN:VNP remap_tablespace=SYSTEM:USERS,DATA:USERS;

When I run this command, I get a lot of errors containing the same reason

ORA-00959: tablespace 'USERS;' does not exist

However, when I run select tablespace_name from dba_tablespaces; I see that USERS tablespace is present.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

After reading a few related questions, I saw that it could be related to privileges for the user VNP and I have provided privileges too.

SQL> alter user vnp quota unlimited on users;
User altered.

SQL> grant UNLIMITED TABLESPACE to vnp;
Grant succeeded.

Still I'm getting the same error when I try to import this DB dump. Can you please point me to a correct direction as to why this happens..? Thanks in advance.

Solution

  • How trivial!

    ORA-00959: tablespace 'USERS;' does not exist
                            ^
       tablespace doesn't really have a semi-colon as a part of its name, eh?

    IMPDP is ran at the operating system command prompt. As such, it doesn't require (and shouldn't have) a terminator (as opposed to SQL commands).

    Additionally, if it still doesn't work once you remove the semi-colon, try to split REMAP_TABLESPACE in two:

    remap_tablespace=SYSTEM:USERS remap_tablespace=DATA:USERS