0

I have a properly functioning up to date 10g database locally that I don't want to mess with. I need to do some queries on a customer's database locally which is a couple versions behind from our current software. I had exported their full db using expdp. The user is the same, and the structure is pretty much the same. What is the proper way of having both databases loaded at the same time?

If I have worded this funny, or am going about this in the wrong way, please let me know! Thanks!

Edit: There is one main user, and another user for each component/application within the main app.

user83598
  • 161
  • 1
  • 3
  • 10
  • 2
    i suggest a VMWare instance with the other copy. you can reload it after testing - and it should expose a different TNSNAMES entry for querying simultaneously – Randy Nov 14 '12 at 17:26
  • You can create a new database with the Database Configuration Assistant. It will use the same software, but setup different services and files. It's a lot easier than a full installation, but it can still use up a lot of resources. But if you won't use the database a lot, you can set the PGA_AGGREGATE_TARGET and SGA_TARGET very low. – Jon Heller Nov 14 '12 at 19:17
  • @Randy I ended up getting it working in a KVM, so you win! hahaha I suspect the remap_schema option would work too if the db didn't have so many schemas to figure out if remapping them was necessary or not. – user83598 Nov 21 '12 at 18:02

1 Answers1

2

Use Import Data Pump (impdp) with the "remap schema" option to load the exported schema into another schema in your existing database:

http://www.database.fi/2011/05/using-expdp-impdp-and-changing-schemas-with-remap_schema/

ObiWanKenobi
  • 14,526
  • 11
  • 45
  • 51
  • This might be working :/ I forgot to realize that although there is one main user, there are 10 other (sub?) users. Do I have to remap all 10? remap_schema=user1:user1_new remap_schema=user2:user2_new ... ? I am getting a lot of ORA-31684: Object type SYNONYM:"USERPRODUCT"."PRODUCT" already exists – user83598 Nov 14 '12 at 18:03
  • @user83598 - yes, you'd need to remap all schemas that already exist in your database. Oracle has no way of knowing that you consider them 'sub' users. If they only have synonyms then you might not have done much damage, but if they have data too then you might have cleaning up to do. It might have been helpful to use the `sqlfle` option to check what it was going to do - easy to say that after the even of course... – Alex Poole Nov 14 '12 at 18:29