I’ve been toggling back and forth two different oracle homes by editing the environment variables from the Control Panel in Windows which is a pain in the ***. What I did was, to create two different command prompt shells with different oracle home environments. I got this idea after a glimpse from FWTools.

  1. Create a Command Prompt Shortcut and drag it to your oracle directory, i.e, E:\oracle\Oracle Shell Local

  2. Edit the target as:

C:\WINDOWS\system32\cmd.exe /K "E:\oracle\setlocal.bat"
  1. Create “setlocal.bat”
@echo off
SET ORACLE_HOME=E:\oracle\product\10.2.0\db_2
set PATH=E:\oracle\product\10.2.0\db_2\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

Do the same for your remote oracle but with a different bat file.

  1. Repeat steps 1 and 2 but name it “Oracle Shell Remote” and “E:\oracle\setremote.bat”

  2. Create “setremote.bat”

@echo off
SET ORACLE_HOME=E:\oracle\product\10.2.0\client_1
set PATH=E:\oracle\product\10.2.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

Note: This assumes that you have installed an Oracle Client on E:\oracle\product\10.2.0\client_1

Ok, now let’s test. I know that I have different record count for a table in my local and remote

  1. Fire up “Oracle Shell Local”. Run sqlplus

    sqlplus username/password@instance_name_defined_in_local_tnsnames.ora

Note: E:\oracle\product\10.2.0\db_2\NETWORK\ADMIN\tnsnames.ora

  1. Fire u p “Oracle Shell Remote”. Run sqlplus

    sqlplus username/password@instance_name_defined_in_remote_tnsnames.ora

Note: E:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora

So what? Well its very useful when doing export and imports. Say I want to export a table from my local and import it to my remote oracle. I’ll just fire up two shells, issue an export in my “Oracle Shell Local” and run an import command in my “Oracle Shell Remote”