1

I have a dump file with a table but when I execute the import it gives me the error that the tablespace 'TB_SYS_DAT' does not exist and that the user 'ADMIN' does not exist either.

How can I make the import perform the creation of the table with the user 'USERPROD' in the tablespace 'TB_DATA'

impdp userprod/oracleu_23 directory=DIR_DMP_FILES dumpfile=DWH_PLAN_PARAM_20220104.dmp logfile=DWH_PLAN_PARAM_20220104.log

[16:07:43] lldata:~/files_dmp$ impdp userprod/oracleu_23 directory=DIR_DMP_FILES dumpfile=DWH_PLAN_PARAM_20220104.dmp logfile=DWH_PLAN_PARAM_20220104.log

Import: Release 19.0.0.0.0 - Production on Sat Jan 8 16:08:13 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "USERPROD"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "USERPROD"."SYS_IMPORT_FULL_01":  userprod/******** directory=DIR_DMP_FILES dumpfile=DWH_PLAN_PARAM_20220104.dmp logfile=DWH_PLAN_PARAM_20220104.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"ADMIN"."PLAN_PARAM" failed to create with error:
ORA-00959: tablespace 'TB_SYS_DAT' does not exist

Failing sql is:
CREATE TABLE "ADMIN"."PLAN_PARAM" ("PLAN_PARAM_ID" NUMBER(20,0) NOT NULL ENABLE, "PLAN_ID" NUMBER(20,0) NOT NULL ENABLE, "PLAN_PARAM_CODE" VARCHAR2(32 BYTE), "PLAN_PARAM_NAME" VARCHAR2(128 BYTE), "PLAN_PARAM_DESC" VARCHAR2(1024 BYTE), "IS_MANY" VARCHAR2(1 BYTE), "MIN_OCCURS" NUMBER(5,0), "MAX_OCCURS" NUMBER(5,0), "DATA_TYPE" VARCHAR2(1 BYTE), "CDATA_TYPE" NUMBER(20,0), "INSTANTIATE_TYPE" VARCHAR2(1 BYTE) NOT NULL ENABLE, "USING_PRIVATE_GUI" VARCHAR2(1 BYTE), "PRIVATE_GUI_DEFINITION" CLOB, "PUBLIC_GUI_ID" NUMBER(5,0), "PRIVATE_FLAG" VARCHAR2(1 BYTE), "MEASURE_TYPE" VARCHAR2(1 BYTE), "MEASURE_ID" NUMBER(5,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TB_SYS_DAT"  LOB ("PRIVATE_GUI_DEFINITION") STORE AS BASICFILE ( TABLESPACE "TB_SYS_DAT" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"ADMIN"."PK_PLAN_PARAM" skipped, base object type TABLE:"ADMIN"."PLAN_PARAM" creation failed

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"ADMIN"."PK_PLAN_PARAM" skipped, base object type TABLE:"ADMIN"."PLAN_PARAM" creation failed

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "USERPROD"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Sat Jan 8 16:08:18 2022 elapsed 0 00:00:04

[16:08:19] lldata:~/files_dmp$
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Jeniffer Lorena Guillen Castro
  • 75
  • 2
  • 8

1 Answers1

1

You can use the REMAP_TABLESPACE parameter:

The Oracle Data Pump Import command-line mode REMAP_TABLESPACE parameter remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.

And the REMAP_SCHEMA parameter:

The Oracle Data Pump Import command-line mode REMAP_SCHEMA parameter loads all objects from the source schema into a target schema.

So you can add those to your command line, e.g.:

impdp ... userprod/oracleu_23 REMAP_SCHEMA=ADMIN:USERPROD REMAP_TABLESPACE=TB_SYS_DAT:TB_DATA DIRECTORY=...
Alex Poole
  • 183,384
  • 11
  • 179
  • 318