Home » SQL & PL/SQL » SQL & PL/SQL » switching between databases on the same computer
switching between databases on the same computer [message #2582] Mon, 29 July 2002 08:04 Go to next message
Trish
Messages: 7
Registered: July 2002
Junior Member
Hello,
Please excuse me if this is the wrong mailing list to be asking this question in. I'm a very newbie.
Using Oracle's Database Configuration Assistant it is possible to create numerous databases on a single computer. If there is more than one database created on the same computer, when you use SQL *Plus to connect to a database there seems to be no way of choosing which database you would like to connect to. By default you are automatically connected to the most recently created database. Typeing something like :
sqlplus scott@first_database/tiger
doesn't work because you are trying to connect to another database on the same computer that you are already on, not a remote database.

Can anybody help please ?
Thanks,
Trish.
Re: switching between databases on the same computer [message #2583 is a reply to message #2582] Mon, 29 July 2002 08:57 Go to previous messageGo to next message
Silpa
Messages: 23
Registered: July 2002
Junior Member
connect as

sqlplus username/password@service_name

Service_Name is the name given in TNSNAMES.ora for the connecting database.

Alternate method is

export ORACLE_SID to which ever database you want to connect

and connect as
sqlplus username/password
Re: switching between databases on the same computer [message #2594 is a reply to message #2583] Tue, 30 July 2002 06:08 Go to previous messageGo to next message
Trish
Messages: 7
Registered: July 2002
Junior Member
Thank you to those people who replied to my query. I tried :
scott/tiger@HumRes
(HumRes is the database I want to connect to )
I got:
ERROR:
ORA-12154: TNS:could not resolve service name

Here is a section from my tnsnames.ora file:
# TNSNAMES.ORA Network Configuration File: C:oracleora90networkadmintnsnames.ora
# Generated by Oracle configuration tools.

HUMRES =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ucd-opmwaj2f8gy)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = HumRes)
)
)

I read somewhere that this error might be due to my file being configured incorrectly. Can you tell if it looks okay ? It was created automatically by Oracle.

David Eaton and Silpa suggested that I should try to export ORACLE_SID to which ever database you want to connect. Can you tell me how you would go about doing this on Win2000 please ?

Thanks,
Trish.
Re: switching between databases on the same computer [message #2597 is a reply to message #2583] Tue, 30 July 2002 07:14 Go to previous messageGo to next message
ctg
Messages: 146
Registered: July 2002
Senior Member
you can open a dos window:
c: set ORACLE_SID=instance_name
c: sqlplus username/password
sql> select instance_name from v$instance;
Re: switching between databases on the same computer [message #2602 is a reply to message #2597] Tue, 30 July 2002 10:08 Go to previous message
Trish
Messages: 7
Registered: July 2002
Junior Member
Hello,
thank you all for your help. I have naother question that I would really appreciate help with please.

ctg suggested that I should use :
c: set ORACLE_SID=instance_name
c: sqlplus username/password
sql> select instance_name from v$instance;

I've tried this to connect to my HumRes database and it works. Typeing
select instance_name from v$instance;
returns the instance name to the screen.
INSTANCE_NAME
----------------
humres

I then tried this technique to connect to another of my databases Glossary, this time:
C:>set ORACLE_SID = Glossary
But when I typed:
select instance_name from v$instance;
I still got told that the instance name was humres.

I tried to opening up a new command prompt and trying again. In the new command prompt Oracle connected to my current database Glossary by default (the most recent database I've created). But when I tried to confirm that I actually was in the Glossary databse by typeing the line:
select instance_name from v$instance;
I got an error message:
select instance_name from v$instance
*
ERROR at line 1:
ORA-00942: table or view does not exist

Can someone explain what is happening please ? Apologies for the length of this message and thank you all agin for your help. I've included the portion of my tnsnames.ora file which contains the service name information in it for the Glossary database.

GLOSSARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ucd-opmwaj2f8gy)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = Glossary)
)
)
Previous Topic: Using Bulk Binds in Stored Procedures
Next Topic: fetching the next and previous column value for the current record
Goto Forum:
  


Current Time: Thu Mar 28 04:41:55 CDT 2024