In Oracle, how do you change the 'default' database?

Question

I really should know this, but would someone tell me how to change the default database on Linux?

For example:

I have a database test1 on server1 with ORACLE_SID=test1. So, to connect to test1 I can use:

sqlplus myuser/password

Connects to the default database, test1

I would now like the default sqlplus connection to go to database test2 on server server2.

So, I've updated tnsnames so that the old test1 entry now points to test2@server2. I've also added a separate entry for test2 that points to the same place. However, the default connection still seems to go to test1@server1.

The following both work fine and go to database test2 on server2:

sqlplus myuser/password@test1
sqlplus myuser/password@test2

But the default connection, sqlplus myuser/password, goes to test1@server1.

Any ideas?

Thanks.

Answer 1

I think it is set in your environment, can you echo $ORACLE_SID?

Answer 2

Assuming you're logged into server1, you'll need to connect to test2 using

sqlplus myuser/password@test2

because you have to go through a listener to get to server2. The string test2 identifies an entry in your tnsnames.ora file that specifies how to connect to test2. You won't be able to connect to a different server using the first form of your sqlplus command.

If both instances (test1, test2) were on server1, then you could, as @kerchingo states, set the ORACLE_SID environment variable to point at another instance.

Answer 3

Defining a enviroment variable LOCAL with the tns alias of your database.

> set LOCAL=test1
> sqlplus myuser/password
> ... connected to test1
> set LOCAL=test2
> sqlplus myuser/password
> ... connected to test2

This works on windows client, not shure about other os.

Answer 4

To expand on kerchingo's answer: Oracle has multiple ways to identify a database.

The best way -- the one that you should always use -- is USER/PASSWORD@SERVER. This will use the Oracle naming lookup (tnsnames.ora) to find the actual server, which might be on a different physical host every time you connect to it. You can also specify an Oracle connection string as SERVER, but pretend you can't.

There are also two ways to specify a default server via environment variables. The first is TWO_TASK, which uses the naming lookup, and the second is ORACLE_SID, which assumes that the server is running on the current machine. ORACLE_SID takes precedence over TWO_TASK.

The reason that you should always use an explicit connect string is that you have no idea whether the user has set TWO_TASK, ORACLE_SID, both, or neither; nor do you know what they might be set to. Setting both to different values is a particularly painful problem to diagnose, particularly over the phone with a person who doesn't really understand how Oracle works (been there, done that).