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 next 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.
------
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673296 is a reply to message #673294] Wed, 14 November 2018 07:19 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Try creating a parameter file and specify:
SCHEMAS="EGRD_HDFC_QA "
Note the trailing space in double quotes.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673297 is a reply to message #673296] Wed, 14 November 2018 07:31 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Additionally, I believe that the Windows escape character is the caret (^) character. The document you referenced doesn't make that clear.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673298 is a reply to message #673296] Wed, 14 November 2018 07:32 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That looks like someone messed up by creating a schema with a trailing space, noticed what they did and then recreated it with the correct name.
I'd check if that's the case and if it is solve all your problems by simply dropping the schema with the trailing space.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673299 is a reply to message #673298] Wed, 14 November 2018 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if that isn't the case I'd still concentrate on getting rid of or renaming the schema with the trailing space.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673300 is a reply to message #673299] Wed, 14 November 2018 08:09 Go to previous messageGo to next message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
Thanks for the response gazzag. Unsuccessful... as below
1.par content
SCHEMAS="EGRD_HDFC_QA "

E:\ORCL_EXPORT>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 parfile=1.par

Import: Release 11.2.0.1.0 - Production on Wed Nov 14 19:15:13 2018

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

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": 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 parfile=1.par
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"EGRD_HDFC_QA" already exists
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"EGRD_HDFC_QA"."CSR_USR_PERM_GRP_SEQ" already exists
ORA-31684: Object type SEQUENCE:"EGRD_HDFC_QA"."CASE_ACTION_HISTORY_ID_SEQ" already exists
ORA-31684: Object type SEQUENCE:"EGRD_HDFC_QA"."CARD_CLASS_ID_SEQ" already exists



Thanks cookiemonster ,
Agree, the Application teams created the schema with trailing space and objects under it.

Renaming the schema option shouldn't work in 11.2.0.1, tried in Source db from where FULL EXPORT was taken.
E:\ORCL_EXPORT>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 14 19:37:18 2018
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> alter user scott rename to scot identified by Test123$$;
alter user scott rename to scot identified by Test123$$
*
ERROR at line 1:
ORA-00922: missing or invalid option
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673301 is a reply to message #673300] Wed, 14 November 2018 08:13 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
robertsonhp wrote on Wed, 14 November 2018 14:09

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 14 19:37:18 2018
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> alter user scott rename to scot identified by Test123$$;
alter user scott rename to scot identified by Test123$$
*
ERROR at line 1:
ORA-00922: missing or invalid option
You cannot rename a user. You must:

1. Export the relevant user.
2. Drop the user from the database.
3. Import the user exported in step 1 with the REMAP_SCHEMA parameter.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673302 is a reply to message #673294] Wed, 14 November 2018 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is
"include=\"schema:= 'EGRD_HDFC_QA '\""

But I agree with cookiemonster, the best way is to first rename the schema before export or if you can no more export you can rename it during import adding
remap_schema="EGRD_HDFC_QA :EGRD_HDFC_QA"

Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673304 is a reply to message #673301] Wed, 14 November 2018 08:23 Go to previous messageGo to next message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
Agree gazzag.
FULL EXPORT contains the schema having trailing space's data.
Eventhough, unable to expdp only that schema having trailing space in its name.

For now, ignoring the drop option from Source db, till data is imported into target db from FULL EXPORT.

REMAP_SCHEMA option also tried and seems not to be working. Believe, first the schemas option syntax need to be correctly determined for to be applied under remap_schema.
E:\ORCL_EXPORT>impdp system@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE2_14Nov.log remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT schemas=\'EGRD_HDFC_QA^ \' remap_schema=\'EGRD_HDFC_QA^ \':EGRD_HDFC_QA

Import: Release 11.2.0.1.0 - Production on Wed Nov 14 19:48:13 2018
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
UDI-00014: invalid value for parameter, 'remap_schema'


Believe, ORACLE SUPPORT is the only option left out.
Should be leaving for the day.. as its 8 PM IST in Bangalore. Will revert tomorrow. FYI - This is an QA environment and migration process underway with this challenge.

Thanks all for reverting... plus to who all will revert later in the posts.

Regards, Robertson Bhadrachalam
Sr. Oracle Database Architect
+91-9886321339
robertsonhp@gmail.com / robertson.b@wibmo.com
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673306 is a reply to message #673304] Wed, 14 November 2018 08:29 Go to previous messageGo to next message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
Thanks for quick response Michel Cadot,

E:\ORCL_EXPORT>impdp system@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE2_14Nov.log remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT "include=\"schema:= 'EGRD_HDFC_QA '\"" remap_schema="EGRD_HDFC_QA :EGRD_HDFC_QA"

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
UDI-00014: invalid value for parameter, 'remap_schema'

E:\ORCL_EXPORT>impdp system@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE2_14Nov.log remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT "include=\"schema:= 'EGRD_HDFC_QA '\"" remap_schema='EGRD_HDFC_QA ':EGRD_HDFC_QA
UDI-00014: invalid value for parameter, 'remap_schema'

E:\ORCL_EXPORT>impdp system@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE2_14Nov.log remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT "include=\"schema:= 'EGRD_HDFC_QA '\"" remap_schema=\"'EGRD_HDFC_QA '\":EGRD_HDFC_QA
UDI-00014: invalid value for parameter, 'remap_schema'

Its all about Escaping and making oracle understand the trailing space. ORACLE SUPPORT should be having some info, which they have not made public.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673307 is a reply to message #673306] Wed, 14 November 2018 08:30 Go to previous messageGo to next message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
Its all about Escaping the Space and making oracle understand the trailing space
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673308 is a reply to message #673307] Wed, 14 November 2018 08:33 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
As I said, you need to use the caret symbol on Windows:

SCHEMAS="EGRD_HDFC_QA^ "
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673309 is a reply to message #673306] Wed, 14 November 2018 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What I posted work:
SQL> create user "TOTO " identified by michel;

User created.

SQL> create table "TOTO ".t (v int);

Table created.
E:>expdp michel/michel full=y "include=\"schema:='TOTO '\"" reuse_dumpfiles=y

Export: Release 11.2.0.4.0 - Production on Mer. Nov. 14 15:13:48 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MICHEL"."SYS_EXPORT_FULL_02":  michel/******** full=y include="schema:='TOTO '" reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
. . exported "TOTO "."T"                                     0 KB       0 rows
Master table "MICHEL"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_FULL_02 is:
  E:\ORACLE\SAVE\EXPDAT.DMP
Job "MICHEL"."SYS_EXPORT_FULL_02" successfully completed at Mer. Nov. 14 15:15:24 2018 elapsed 0 00:01:34
E:>impdp michel/michel dumpfile=EXPDAT.DMP full=y "remap_schema=\"TOTO :toto\""

Import: Release 11.2.0.4.0 - Production on Mer. Nov. 14 15:17:49 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MICHEL"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MICHEL"."SYS_IMPORT_FULL_01":  michel/******** dumpfile=EXPDAT.DMP full=y remap_schema="TOTO :toto"
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "TOTO"."T"                                      0 KB       0 rows
Job "MICHEL"."SYS_IMPORT_FULL_01" successfully completed at Mer. Nov. 14 15:17:55 2018 elapsed 0 00:00:05
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673310 is a reply to message #673309] Wed, 14 November 2018 08:38 Go to previous messageGo to next message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
E:\ORCL_EXPORT>impdp system@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE2_14Nov.log remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT "include=\"schema:= 'EGRD_HDFC_QA '\""

Import: Release 11.2.0.1.0 - Production on Wed Nov 14 20:01:20 2018

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

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/********@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE2_14Nov.log remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT include="schema:= 'EGRD_HDFC_QA '"
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "EGRD_HDFC_QA "."SYS_EXPORT_SCHEMA_01" 345.6 KB 2960 rows
. . imported "EGRD_HDFC_QA "."MAKER_CHECKER_REQUESTS" 220.8 KB 337 rows
. . imported "EGRD_HDFC_QA "."CARD_PROFILE" 580.7 KB 23124 rows
. . imported "EGRD_HDFC_QA "."CSR_AUDIT_TRAIL" 43.10 KB 4413 rows
. . imported "EGRD_HDFC_QA "."CSR_AUDIT_TRAIL_201607" 46.35 KB 1411 rows
. . imported "EGRD_HDFC_QA "."RULES" 12.51 KB 88 rows
. . imported "EGRD_HDFC_QA "."RULES_DP" 12.61 KB 89 rows
.................
.............
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_02" successfully completed at 20:07:33

Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673311 is a reply to message #673310] Wed, 14 November 2018 08:40 Go to previous messageGo to next message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
Thanks Michel Cadot,
Was able to successfully import with same schema name having trailing space using the include option.

Let me create another schema and try to import using the remap_option to get rid of schema with trailing space
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673312 is a reply to message #673311] Wed, 14 November 2018 09:04 Go to previous messageGo to next message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
Thanks Michel Cadot,
The syntax provided from your end worked for remap_schema too.... !
Let me know, where to Rate you.. for the excellence.

E:\ORCL_EXPORT>impdp system@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE2_14Nov.log remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT "include=\"schema:= 'EGRD_HDFC_QA '\"" "remap_schema=\"EGRD_HDFC_QA :EGRD_HDFC_QA2\""
Import: Release 11.2.0.1.0 - Production on Wed Nov 14 20:22:05 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/********@ORCL directory=EXPDP dumpfile=EXP_QAHDFC_FULL_31OCT2018_%U.dmp logfile=imp_EGRD_HDFC_SPACE2_14Nov.log remap_tablespace=USERS:EGRD_HDFC_LT,SYSTEM:EGRD_HDFC_LT,EGRD_HDFC_QA:EGRD_HDFC_LT include="schema:= 'EGRD_HDFC_QA '" remap_schema="EGRD_HDFC_QA :EGRD_HDFC_QA2"
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"EGRD_HDFC_QA2" already exists
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "EGRD_HDFC_QA2"."SYS_EXPORT_SCHEMA_01" 345.6 KB 2960 rows
. . imported "EGRD_HDFC_QA2"."MAKER_CHECKER_REQUESTS" 220.8 KB 337 rows
. . imported "EGRD_HDFC_QA2"."CARD_PROFILE" 580.7 KB 23124 rows
. . imported "EGRD_HDFC_QA2"."CSR_AUDIT_TRAIL" 43.10 KB 4413 rows
. . imported "EGRD_HDFC_QA2"."CSR_AUDIT_TRAIL_201607" 46.35 KB 1411 rows
. . imported "EGRD_HDFC_QA2"."RULES" 12.51 KB 88 rows
. . imported "EGRD_HDFC_QA2"."RULES_DP" 12.61 KB 89 rows
. . imported "EGRD_HDFC_QA2"."CSR_AUDIT_TRAIL_201608" 33.90 KB 1577 rows
. . imported "EGRD_HDFC_QA2"."EXPORT_JOB_SQLDEV_241" 29.32 KB 1311 rows
. . imported "EGRD_HDFC_QA2"."IMPORT_JOB_SQLDEV_243" 29.92 KB 1330 rows
..........
.....
. . imported "EGRD_HDFC_QA2"."SUMMARY_201608" 0 KB 0 rows
. . imported "EGRD_HDFC_QA2"."SUMMARY_201611" 0 KB 0 rows
. . imported "EGRD_HDFC_QA2"."VEERU201454" 0 KB 0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 1 error(s) at 20:24:58


1* 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
SQL> /

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 (WITH SPACE) INDEX 501
EGRD_HDFC_QA TABLE 142
EGRD_HDFC_QA LOB 27
EGRD_HDFC_QA SEQUENCE 10
EGRD_HDFC_QA TRIGGER 6

EGRD_HDFC_QA2(NewSchema to replace schema with space)
EGRD_HDFC_QA2 INDEX 501
EGRD_HDFC_QA2 TABLE 142
EGRD_HDFC_QA2 LOB 27
EGRD_HDFC_QA2 SEQUENCE 10
EGRD_HDFC_QA2 TRIGGER 6


15 rows selected.

Dropped the schema with trailing space in target db, as the data is now remapped to EGRD_HDFC_QA2
SQL> drop user "EGRD_HDFC_QA " cascade;
User dropped.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673313 is a reply to message #673312] Wed, 14 November 2018 09:23 Go to previous messageGo to next message
robertsonhp
Messages: 9
Registered: November 2018
Junior Member
Kind of any one's assistance, in how to close this thread as SOLVED, humble apologies as I joined ORAFAQ forum today for the first time.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673314 is a reply to message #673313] Wed, 14 November 2018 09:25 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
robertsonhp wrote on Wed, 14 November 2018 15:23
I joined ORAFAQ forum today for the first time.
Good result then Smile

Anyway, we don't close threads here. They are simply left open.
Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673315 is a reply to message #673314] Wed, 14 November 2018 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We don't rate people either. Smile

Re: How to Import(IMPDP) an Schema having trailing space from an FULL export dump in Windows 2008R2 ( 64 [message #673316 is a reply to message #673315] Wed, 14 November 2018 09:44 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Not in public at any rate Razz
Previous Topic: Export job finishing time
Next Topic: Issue in importing the fully exported dump file.
Goto Forum:
  


Current Time: Thu Mar 28 17:32:43 CDT 2024