I asked this question in stackoverflow but no one was able to answer it so am trying my luck here if anyone knew what am talking about. Here is the link original question.

What i wanted to do is to import all contents from dump file to another database which has a different schema. I tried the command below but got error since oracle doesn't allow SYSTEM to SYSTEM remap.

C:\>impdp SYSTEM/password@testing directory=imp_dir dumpfile=dumpFile.dmp logfile=dump_log.log full=yes remap_schema=SYSTEM:SYSTEM

My question is there a way to import all contents of dump file without having to repeat the command below over and over again until all schemas are copied? Thanks

C:\>impdp SYSTEM/password@testing directory=imp_dir dumpfile=dumpFile.dmp logfile=dump_log.log full=yes remap_schema=schema1:schema1
  • Why do you want to import the SYSTEM schema? Something's not quite right in what you're wanting to do. – Philᵀᴹ Apr 4 '14 at 6:52
  • SYSTEM schema have access to every schema in the dump file, so i was thinking if import that schema then it will dump all contents to the new DB. But oracle doesn't allow it, so i have to import each schema from dump file one by one to the new DB – dimas Apr 4 '14 at 6:55
  • 3
    The SYSTEM schema contains internal Oracle objects, and you shouldn't have created any objects in it yourself - it doesn't need importing. Does the database you're importing into already contain any data? Sounds like you just need to do the import as SYS without remap_schema – Philᵀᴹ Apr 4 '14 at 6:59
  • Yes it contains tens of GB of data. Forgive me am not an oracle expert, but if I import as SYS and set Full=y without remap_schema flag, i think it will only copy schema that exists in both DB. For example my dumpFile contains A,B,C schema and DB_B only contain A schema. After import only A will be copied the rest won't be copied to new DB am I right on this? – dimas Apr 4 '14 at 7:07
  • 2
    It will create the schemas for you. You may need to run with remap_tablespace if the target databases physical structure is different. Are you saying you have created 10Gb+ of objects in the SYSTEM schema? – Philᵀᴹ Apr 4 '14 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.