Home » RDBMS Server » Server Utilities » How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 (Oracle 11gR2 Windows 2008R2 (64 bit))  () 1 Vote
How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673294] Wed, 14 November 2018 06:30 Go to previous message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 bit ) 11gR2 db version.
Any quick helps in a day or two appreciated... !

Sample part of expdp log displaying two schemas "EGRD_HDFC_QA" and "EGRD_HDFC_QA " ( having a single trailing space )

expdp system@SOURCE directory=expdp dumpfile=exp_QAHDFC_Full_31Oct2018_%U.dmp logfile=exp_QAHDFC_Full_31Oct2018.log parallel=4 compression=all full=y
. . exported "EGRD_HDFC_QA "."MAKER_CHECKER_REQUESTS" 220.8 KB 337 rows ( Source schema with trailing space )
. . exported "EGRD_HDFC_QA"."CARD_PROFILE" 580.8 KB 23124 rows ( Source schema without trailing space )
. . exported "EGRD_HDFC_LT"."CARD_PROFILE" 580.7 KB 23124 rows
. . exported "EGRD_HDFC_QA"."CSR_AUDIT_TRAIL_201808" 173.5 KB 10103 rows
. . exported "OTP_HDFC_USR3"."OTP_MASTER_201709" 342.0 KB 8373 rows
. . exported "EGRD_HDFC_QA"."DEVICE_ID_LIST" 68.14 KB 13932 rows
. . exported "OTP_HDFC_USR3"."OTP_MASTER_201712" 241.1 KB 5973 rows
. . exported "EGRD_HDFC_QA "."CARD_PROFILE" 580.7 KB 23124 rows ( Source schema with trailing space )

Verified from SOURCE Db, there exists two schemas "EGRD_HDFC_QA" & "EGRD_HDFC_QA " (Having single trailing space )

select owner,object_type,count(object_name) from dba_objects where owner in (select username from dba_users where account_status='OPEN' and username not in ('SCOTT','DBSNMP','SYSTEM','SYS','MGMT_VIEW')) group by owner,object_type order by 1,3 desc;

OWNER OBJECT_TYPE COUNT(OBJECT_NAME)
------------------------------ ------------------- ------------------
EGRD_HDFC_LT INDEX 501
EGRD_HDFC_LT TABLE 143
EGRD_HDFC_LT LOB 27
EGRD_HDFC_LT SEQUENCE 10
EGRD_HDFC_LT TRIGGER 5

EGRD_HDFC_QA INDEX 513
EGRD_HDFC_QA TABLE 139
EGRD_HDFC_QA LOB 90
EGRD_HDFC_QA SEQUENCE 17
EGRD_HDFC_QA TRIGGER 6

EGRD_HDFC_QA (WITH SPACE) INDEX 501
EGRD_HDFC_QA TABLE 142
EGRD_HDFC_QA LOB 27
EGRD_HDFC_QA SEQUENCE 10
EGRD_HDFC_QA TRIGGER 6

OTP_HDFC_USR3 INDEX 740
OTP_HDFC_USR3 TABLE 154
OTP_HDFC_USR3 TRIGGER 2
OTP_HDFC_USR3 SEQUENCE 2
OTP_HDFC_USR3 LOB 1
20 rows selected.
-----
Referred to metalink doc
DataPump - How To Import A Schema Containing Space Or Case Sensitive Letters In The Schema Name? (Doc ID 1275011.1)
You need to escape the double quotes with a backslash, so the impdp command line will be:
#> impdp system/password directory=dpu dumpfile=diff_users.dmp schemas=\'\"EXTERNAL SUPPORT\"\',\'\"MOUSER20xx\"\'

C:\Users\eguardapp>impdp system@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE_14Nov.log
remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT schemas=\'\"EGRD_HDFC_QA \"\'
Import: Release 11.2.0.1.0 - Production on Wed Nov 14 17:41:08 2018
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39165: Schema '"EGRD_HDFC_QA "' was not found.

C:\Users\eguardapp>impdp system@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE_14Nov.log
remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT schemas=\'EGRD_HDFC_QA^ \'
Import: Release 11.2.0.1.0 - Production on Wed Nov 14 17:43:15 2018
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39165: Schema ' was not found.
ORA-39165: Schema 'EGRD_HDFC_QA was not found.
------
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Export job finishing time
Next Topic: Issue in importing the fully exported dump file.
Goto Forum:
  


Current Time: Fri Mar 29 10:37:50 CDT 2024