Database Software

Hello all, my first post here and I am not a DBA, I am an application owner who is seeking help whilst trying to perform some DBA functions.

I have a applications that make use of an Oracle database(s), one development and one production, each application has a user/schema, so the dev app DAPP has DUSER schema and uses database DEVDB, the prod app PAPP has schema PUSER and uses database PRODDB - I am trying to create a path to live from Dev to Prod, there are many more schema's on DEVDB so in my mind I do not want to take the whole DB, just the DUSER schema (all objects & data).

My question is - can you export a schema from one database and import into a different database (obviously I need to copy the dump file between servers)?

STEP1. export from DEV: expdp DUSER/DUSER@DEVDB SCHEMAS=DUSER DIRECTORY=data_pump DUMPFILE=DEVDB.dmp LOGFILE=export.log

STEP2. import into PROD: impdp PUSER/PUSER@PRODDB REMAP_SCHEMA=DUSER:PUSER TABLE_EXISTS_ACTION=REPLACE DIRECTORY=data_pump DUMPFILE=DEVDB.dmp LOGFILE=import.log

Can anyone see any problems with this approach, I am looking to replace the whole schema on PROD with the schema & data from DEV.



Now I had a little play with this earlier and I would just like someone to confirm to me that I have not created a problem, from an application view it looks ok but I am worried I may have caused some issues in the database, the scenario above was slightly different in the fact it was all in the same database/instance, I was just trying to export/import from one schema to another - but within the same DB.

I first exported schema "A" ok
expdp userA/userA@DB SCHEMAS=userA DIRECTORY=data_pump DUMPFILE=DB.dmp LOGFILE=export.log

I then tried to import schema "A" into schema "B"
impdp userB/userB@DB SCHEMAS=userB TABLE_EXISTS_ACTION=REPLACE DIRECTORY=data_pump DUMPFILE=DB.dmp LOGFILE=import.log

I got an error stating that SCHEMA "userB" could not be found, I think I mistakenly thought that the command above would import the .dmp file into SCHEMA "userB" but I now think the impdp command was looking within the .dmp file for the schema "userB" and could not find it - I believe I should have used the REMAP_SCHEMA command as in my path to live example above - what I went onto do next was take away the SCHEMAS=userB option from the impdp command and it seemed to work, or at least I could see things were being imported, in the screen logging I could see though that the target OBJECT/SCHEMA was "userA" - CAN ANYONE TELL ME WHAT COULD HAVE HAPPENED HERE, event though I was running the impdp as userB and with the SCHEMA=userB option removed - which schema if any would have been replaced? userA with userB, userB with userA or something else?


Any help at this point with either of my questions will be greatly appreciated and again apologies for maybe some nieve questions.

Mark.