0

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

Improve this question
3
  • What tablespaces exist on the target database? Have you created any? select * from DBA_TABLESPACES will give you a list
    – Philᵀᴹ
    Apr 28, 2019 at 10:59
  • I dont have any tablespace in new database
    – Sushant Gosavi
    Apr 28, 2019 at 11:26
  • of course you have tablespaces in the new database. You can't have a database without tablespaces. You say you tried the 'remap_tablespace', but I see no evidence of such in anything you show.
    – EdStevens
    Apr 28, 2019 at 13:29

2 Answers 2

Reset to default
3

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.

Improve this answer
4
  • OR . . . simply create TS XXX_SML_INDEX before running the impdp operation.
    – EdStevens
    Apr 28, 2019 at 17:41
  • Thanks It work - Give me response as Job "MOBILETEST"."SYS_SQL_FILE_FULL_01" successfully completed at 15:11:04
    – Sushant Gosavi
    Apr 30, 2019 at 9:42
  • How I can ensure and check all tables is inserted or not ? for this i have try "select * from T_MAPP_USERMASTER" but result is "ORA-00942: table or view does not exist "
    – Sushant Gosavi
    Apr 30, 2019 at 9:45
  • @sushantgosavi compare log files from expdp and impdp. It will list table names,size and rows exported/imported
    – user153556
    May 2, 2019 at 5:16
0

Find the list of existing tablespaces in target database (where you are planning to import), then

select distinct tablespace_name from dba_tablespaces;

Then use remap_tablespace:

impdp MOBILETEST/MOBILETEST directory=MOBILETEST dumpfile=MOBILETEST.dmp logfile=MOBILETEST.log full=y remap_tablespace=XXX_SML_INDEX:CUSTOMER_TS
Improve this answer

Your Answer

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

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