connecting to different databses in oracle?

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?

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 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 theALTER SYSTEM SQL command 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_SIDenvironment 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.

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.