switching between databases on the same computer [message #2582] |
Mon, 29 July 2002 08:04 |
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 |
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 |
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 #2602 is a reply to message #2597] |
Tue, 30 July 2002 10:08 |
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)
)
)
|
|
|