Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

impdp tablespace error in 11g

user3266490Feb 29 2012 — edited Feb 29 2012

Hi,

Db :11.1
Os : Redhat

We got the error while importing using impdp with full=y option.

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

Once create the tablespace, then can we run again impdp with full=y option? or any other method for partucular tablespace only?

2,The tablespace (Ex: ARCH__CORE03) already there.So Why throwing the below error(ORA-31684).
Anyone please explain this one?

ORA-31684: Object type TABLESPACE:"DISCUN_IDX03" already exists
ORA-31684: Object type TABLESPACE:"DISCUN_IDX04" already exists
ORA-31684: Object type TABLESPACE:"DISCUN_TAB03" already exists
ORA-31684: Object type TABLESPACE:"DISCUN_TAB04" already exists
ORA-31684: Object type TABLESPACE:"DISCUP_IDX03" already exists
ORA-31684: Object type TABLESPACE:"DISCUP_IDX04" already exists
ORA-31684: Object type TABLESPACE:"DISCUP_TAB03" already exists
ORA-31684: Object type TABLESPACE:"DISCUP_TAB04" already exists
ORA-31684: Object type TABLESPACE:"DISCUY_IDX03" already exists
ORA-31684: Object type TABLESPACE:"DISCUY_IDX04" already exists
ORA-31684: Object type TABLESPACE:"DISCUY_TAB03" already exists
ORA-31684: Object type TABLESPACE:"DISCUY_TAB04" already exists
ORA-31684: Object type TABLESPACE:"ARCH__CORE03" already exists
ORA-39083: Object type TABLESPACE failed to create with error:

ORA-31684: Object type TABLESPACE:"ARCH__CORE03" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/DATA/oradata/PT032/arch__core0401.dbf'
ORA-27038: created file already exists
Additional information: 1
Failing sql is:
CREATE TABLESPACE "ARCH__CORE04" DATAFILE '/DATA/oradata/PT032/arch__core0401.dbf' SIZE 787480576 AUTOEXTEND ON NEXT 131072000 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/DATA/oradata/PT032/arch__trans0301.dbf'
ORA-27038: created file already exists

Thanks & Regards,
VN