Tablespace ‘XXX_SML_INDEX’ does not exist using impdb

dumpimportoracle

I am new in oracle. I am trying to import an Oracle database from one machine to another by using a dump file. I'm using the below command for the import

impdp MOBILETEST/MOBILETEST directory=MOBILETEST
dumpfile=MOBILETEST.dmp logfile=MOBILETEST.log full=y;

I get the error: "tablespace 'XXX_SML_INDEX' does not exist"

ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'XXX_SML_INDEX' does not exist Failing sql is:
DECLARE TEMP_COUNT NUMBER; SQLSTR VARCHAR2(200); BEGIN SQLSTR :=
'ALTER USER "MOBILETEST" QUOTA UNLIMITED ON "XXX_SML_INDEX"'; EXECUTE
IMMEDIATE SQLSTR;EXCEPTION WHEN OTHERS THEN IF SQLCODE = -30041
THEN SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
WHERE TABLESPACE_NAME = ''XXX_SML_INDEX'' AND CONTENTS =
''TEMPORARY'''; EXE ORA-39083: Object type TABLESPACE_QUOTA
failed to create with error:

  • I am trying to use remap_tablespace but dont know where I can get
    second TABLESPACE_QUOTA name, like remap_tablespace=XXX_SML_INDEX: ?

    enter image description here

Best Answer

Find the default tablespace at database level (which is not necessarily the default tablespace of your user):

SQL>  select property_value from database_properties 
      where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
--------------
USERS

Find the default tablespace for your user while logged in another user with adequate privileges for querying DBA_USERS:

SQL> select default_tablespace from dba_users 
     where username = 'BP';

DEFAULT_TABLESPACE
------------------------------
USERS

Find the default tablespace for your user while logged in as that user:

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
USERS

Using that information, remap the tablespace as: remap_tablespace=XXX_SML_INDEX:USERS.

Related Question