title: How to set up multiple Oracle databases (schemas) with the same user?
body: <p>Our company inherited some software that runs on C# Visual Studio 2010, Windows 7 and Oracle 11g. After some effort we got the software working and got a stable database (schema) set up.</p>
  
  <p>We are now starting the process of migrating some data from an old system to this "new" system. However, I don't want to mess up our working schema as I expect a bit of trial and error work will be needed with our data import.</p>
  
  <p>I wanted to do the following:
  Let's say our existing schema is called PROD. I wanted to create a second schema called TEST that we can use for the imported data. Then, in the C# code I can just switch the name of the datasource when switching between our two database schemas. The catch is that the username and password for this connection appears in a multitude of places scattered in the code. To avoid having to change user credentials in multiple places every time we switch between "db environments", I wanted to create a single user to have access to PROD and to TEST.</p>
  
  <p>However, <a href="https://stackoverflow.com/questions/6955814/how-to-grant-user-privilege-on-specific-schema">how to grant user privilege on specific schema?</a> suggests this is not possible. <a href="https://stackoverflow.com/questions/198952/correct-way-to-give-users-access-to-additional-schemas-in-oracle">Correct way to give users access to additional schemas in Oracle</a> suggests a method for granting access on an object level, but this is insufficient: <em>I basically want one single user to have access to two identical schemas (PROD and TEST)</em>. Once I've achieved this, I want to start modifying TEST to start with our data import.</p>
  
  <p>I have also tried creating TEST as a separate Oracle Database installation on a different port, but when trying to create my user on this new instance I still get a conflict that the user already exists (since it was created for PROD in the original database installation).</p>
  
  <p>My user already exists and has access to PROD. How do I give him access to TEST as well? Or how would one solve the more general problem of having a PROD and TEST database defined in an application that uses Oracle?</p>
  
  <p>In MySQL this would be trivial, but I don't have any idea how to do this in Oracle. I am very new to Oracle.</p>