1 Reply Latest reply: Jun 25, 2010 10:03 AM by Dean Gagne-Oracle RSS

    IMPDP -ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:

    577198
      Hello

      I am using IMPDP... getting error ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:

      Parfile
      -------------------------
      FULL=N
      EXCLUDE=STATISTICS
      EXCLUDE=TABLE:"LIKE 'AQ$%'"
      EXCLUDE=GRANT
      TRANSFORM=SEGMENT_ATTRIBUTES:N,OID:N
      TABLE_EXISTS_ACTION=REPLACE
      DIRECTORY=DIR_ZZZ
      PARALLEL=1
      SCHEMAS=s1,s2,s3

      Only difference between sourcedb (sdb ) & target db (tdb) is

      source db has individual tablespaces for Schema s1,s2,s3
      and
      in target db i am putting all schemas into USERS tablespace.

      If I understand correctly "TRANSFORM=SEGMENT_ATTRIBUTES:N,OID:N" option should make IMPDP ignore the tablespace criteria & not throw these errors.


      Errors
      ---------------------

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


      Has anyone encountered these errors ?

      Thanks for your time.
        • 1. Re: IMPDP -ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
          Dean Gagne-Oracle
          Hi,

          I'm pretty sure it will still create the table with the tablespace clause. It will just not use the stored extent information and will use the default extent information. If you want to verify this, run the impdp command with

          sqlfile=mytest.sql

          Then look at the create table statements in mytest.sql

          I think what you need to do is add some remap_tablespace transforms

          remap_tablespace=old_tablespace_1:user remap_tablespace=old_tablespace_2:user

          etc. This will create all of the tables in the user tablespace.

          Dean