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 |
|
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.
------
|
|
|