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

  • What tablespaces exist on the target database? Have you created any? select * from DBA_TABLESPACES will give you a list – Philᵀᴹ Apr 28 at 10:59
  • I dont have any tablespace in new database – sushant gosavi Apr 28 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 at 13:29
2

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.

  • OR . . . simply create TS XXX_SML_INDEX before running the impdp operation. – EdStevens Apr 28 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 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 at 9:45
  • @sushantgosavi compare log files from expdp and impdp. It will list table names,size and rows exported/imported – Sam May 2 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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