Hi Oracle Gurus,
I've got an interesting expdp today. My issue is rather strange and not even sure how to explain it here. Please bare with me as I am trying my best to explain.
I have a user sending me an import datapump. As usually i would create a dummy schema and dummy tablespace then check ddl_dump.txt so I can perform mapping without overriding the same tablespace in the same instance. I usually name the schema and tablespace the same so I can keep track and clean them up easier.
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP logfile=import_VDIADMIN_DUMP.DMP_log.txt sqlfile=ddl_dump.txt
Here is the snippet of the ddl_dump.txt contents. In the file I have VDIADMIN and vdi_data are the info I am looking for. Pretty straight forward and no other tablespaces in there.
######################################
-- CONNECT VDIADMIN
....
....
CREATE TABLE "VDIADMIN"."DEVICE"
( "SOMECOLUMNS" NUMBER(*,0) NOT NULL ENABLE,
) 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 "vdi_data" ;
########################################
Then, I perform the impdp as usual and getting "vdi_data" does not exist, The error is pretty self-explanatory and indicating that the vdi_data does not exist in the expdp file. However, through verifying the ddl_dump.txt, the "vdi_data" existed. I tried all of the following and yet, still getting "ORA-00959: tablespace 'vdi_data' does not exist"
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace=vdi_data:VDIADMINT
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace="vdi_data:VDIADMINT"
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace=VDI_DATA:VDIADMINT"
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace='vdi_data:VDIADMINT'
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace='vdi_data':VDIADMINT
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace="`vdi_data`:VDIADMINT"
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace="`vdi_data`:VDIADMINT"
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace='vdi_data:VDIADMINT'
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT remap_tablespace="vdi_data":VDIADMINT
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=VDIADMIN:VDIADMINT
impdp VDIADMINT/scott DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP
I got it to work by creating a new tablespace as vdi_data (in lower case) and then executing the following and the data were imported.
impdp VDIADMINT/scott@orcl DIRECTORY=kdump dumpfile=VDIADMIN_DUMP.DMP remap_schema=vdiadmin:VDIADMINT
### data successfully pumped into vdi_data.
SYS> SELECT TABLESPACE_NAME, COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME IN ('vdi_data','VDIADMINT') GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME COUNT(*)
------------------------------ ----------
vdi_data 1220
So, my question is, if it's possible to force remap_tablespace to take lower case tablespace name or if anyone can explain this to me? My user probably created the tablespace in the source database in lowercase, but I am more interested to find out an explanation for this.