connecting to different databses in oracle?

by neelima
(hyderabad)

Question:

I am using Oracle DBCA for creating a new database. While creating new database it asks for hostname & SID. The hostname of my system is empmngt & SID is also empmngt. How can I switch the database from my previous database to this new database through SQL*Plus? And how can I tell to which database I connected?

Comments for connecting to different databses in oracle?

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 22, 2016
Rating
starstarstarstarstar

by: John

In SQL*Plus it is very easy to switch from one database to another. All you need to do is issue the command

CONNECT <user>@<dbsid>

where <user> is the username/schema you wish to access by default and <dbsid> is the sid or service name of the database you wish to connect to.

You can optionally specify the password on the command line as well, separating it from the user name with a '/'.

For example the following command when issued in SQL*Plus will connect you to the HR schema in a database called XE

SQL> CONNECT hr/hr@xe

Note that from Oracle 11g onwards passwords are case sensitive by default. The case sensitivity of passwords is controlled by the Boolean parameter SEC_CASE_SENSITIVE_LOGON and can be changed by the SQL command ALTER SYSTEM like this:

SQL> alter system set SEC_CASE_SENSITIVE_LOGON=false;

We've seen that we can use the CONNECT command and the Oracle sid of a database to connect to that database, however Oracle also provides a shortcut by means of the environment variable ORACLE_SID (this applies to UNIX/Linux environments as well as MS Windows environments). This means if you don't specify a database name in the CONNECT command, Oracle will use the value of the ORACLE_SID environment variable as the name of the database to connect to.

In UNIX/Linux environments this is set by the following command

$ export ORACLE_SID=hr

and in Windows the equivalent command is

c:\ set ORACLE_SID=hr

This enables you to connect without specifying the database name, as follows:

SQL> connect scott/tiger

to connect as user SCOTT to the HR database.

To connect to a different database you just need to change the value of the ORACLE_SID environment variable.

The 2nd question asks how to tell which database you're connected to. One way to this is to set the
SQLPROMPT variable in Oracle SQL*Plus. You can set this manually every time you connect to a different database like so

SQL> set sqlprompt 'HR> '

That would change the SQL*Plus prompt to HR>
or you can automate it by modifying the file login.sql and adding the following command:

set sqlprompt '&_CONNECT_IDENTIFIER> ' .

By doing this when you connect to a database the prompt will automatically include the database name.

Another way to do this is to echo the system environment variable from within SQL*Plus with the command

SQL> $echo %ORACLE_SID%

for MS Windows environments or

SQL> $echo $ORACLE_SID

in UNIX/Linux environments.

As a final alternative, if you have the privilege, you can use SQL to retrieve the name from the data dictionary by use of this command:

select name from v$database

But that only works if you have select permission on that view.

If you're interested in formal Oracle training (either on-line or in a classroom) we can help - see our Oracle training page for more information.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.