2 Connecting to Oracle Database

You can connect to Oracle Database only through a client program, such as SQL*Plus or SQL Developer.

Note:

After connecting to Oracle Database through a client program, you enter and run commands in that client program. For details, see the documentation for your client program.

This chapter contains the following sections:

Connecting to Oracle Database from SQL*Plus

SQL*Plus is a client program with which you can access Oracle Database. This section shows how to start SQL*Plus and connect to Oracle Database.

Note:

For steps 3 and 4 of the following procedure, you need a user name and password.

To connect to Oracle Database from SQL*Plus:

  1. If you are on a Windows system, display a Windows command prompt.

  2. At the command prompt, type sqlplus and press the key Enter.

    SQL*Plus starts and prompts you for your user name.

  3. Type your user name and press the key Enter.

    SQL*Plus prompts you for your password.

  4. Type your password and press the key Enter.

    Note:

    For security, your password is not visible on your screen.

    The system connects you to an Oracle Database instance.

    You are in the SQL*Plus environment. At the SQL> prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands.

    To exit SQL*Plus, type exit and press the key Enter.

    Note:

    Exiting SQL*Plus ends the SQL*Plus session, but does not shut down the Oracle Database instance.

    Example 2-1 starts SQL*Plus, connects to Oracle Database, runs a SQL SELECT statement, and exits SQL*Plus. User input is bold.

Example 2-1 Connecting to Oracle Database from SQL*Plus

> sqlplus
SQL*Plus: Release 11.2.0.0.1 - Beta on Mon Jun 9 15:31:26 2008
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
Enter user-name: your_user_name
Enter password: your_password
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.0.0 - Development
With the Partitioning, Data Mining and Real Application Testing options
 
SQL> select count(*) from employees;
 
  COUNT(*)
----------
       107
 
SQL> exit
 
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.0.0 - Development
With the Partitioning, Data Mining and Real Application Testing options
> 

See Also:

Connecting to Oracle Database from SQL Developer

SQL Developer is a client program with which you can access Oracle Database. This section assumes that SQL Developer is installed on your system, and shows how to start it and connect to Oracle Database. If SQL Developer is not installed on your system, see Oracle Database SQL Developer User's Guide for installation instructions.

Note:

For the following procedure:
  • The first time you start SQL Developer on your system, you must provide the full path to java.exe in step 4.

  • For step 7, you need a user name and password.

  • For step 9, you need a host name and port.

To connect to Oracle Database from SQL Developer:

  1. Access the menu from which you can select SQL Developer:

    • On a Windows system: From the Start menu, select All Programs.

    • On a Linux system:

      • On Gnome: Click Application.

      • On KDE: Click K.

  2. Select Oracle - ORACLE_HOME.

  3. Select Application Development.

  4. Select SQL Developer.

    If this is the first time you have started SQL Developer on your system, you are prompted to enter the full path to java.exe (for example, C:\jdk1.5.0\bin\java.exe). Either type the full path after the prompt or browse to it, and then press the key Enter.

    The Oracle SQL Developer window opens.

  5. In the navigation frame of the window, click Connections.

    The Connections pane appears.

  6. In the Connections pane, click the icon New Connection.

    The New/Select Database Connection window opens.

  7. In the New/Select Database Connection window, type the appropriate values in the fields Connection Name, Username, and Password.

    For security, the password characters that you type appear as asterisks.

    Near the Password field is the check box Save Password. By default, it is deselected. Oracle recommends accepting the default.

  8. In the New/Select Database Connection window, click the tab Oracle.

    The Oracle pane appears.

  9. In the Oracle pane:

    • For Role, accept the default.

    • For Connection Type, accept the default (Basic).

    • In the fields Hostname and Port, type the appropriate values.

    • Select the option SID.

    • In the SID field, type the appropriate value.

  10. In the New/Select Database Connection window, click the button Test.

    The connection is tested. If the connection succeeds, the Status indicator changes from blank to Success.

    Description of success.gif follows
    Description of the illustration success.gif

  11. If the test succeeded, click the button Connect.

    The New/Select Database Connection window closes. The Connections pane shows the connection whose name you entered in the Connection Name field in step 7.

You are in the SQL Developer environment.

To exit SQL Developer, select Exit from the File menu.

Note:

Exiting SQL Developer ends the SQL Developer session, but does not shut down the Oracle Database instance. The next time you start SQL Developer, the connection you created using the preceding procedure still exists. SQL Developer prompts you for the password that you supplied in step 7 (unless you selected the check box Save Password).

See Also:

Connecting to Oracle Database as User HR

This section shows how to unlock the HR account and connect to Oracle Database as the user HR, who owns the HR sample schema that the examples and tutorials in this document use.

To do the tutorials and examples in this document, and create the sample application, you must connect to Oracle Database as the user HR from SQL Developer. The HR sample schema is the development environment for the sample application.

Topics:

Unlocking the HR Account

By default, when the HR schema is installed, the HR account is locked and its password is expired. You can connect to Oracle Database as the user HR only if the HR account is unlocked.

Note:

For the following procedure, you need the name and password of a user who has the ALTER USER system privilege (for example, SYSTEM).

To unlock the HR account and reset its password:

  1. Using SQL*Plus, connect to Oracle Database as a user with the ALTER USER system privilege.

    For instructions, see "Connecting to Oracle Database from SQL*Plus".

  2. At the SQL> prompt, unlock the HR account and reset its password:

    Caution:

    Choose a secure password. For guidelines for securing passwords, see Oracle Database Security Guide.
    ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;
    

    The system responds:

    User altered
    

    The HR account is unlocked and its password is password.

Now you can connect to Oracle Database as user HR with the password password. For instructions, see either "Connecting to Oracle Database from SQL*Plus" or "Connecting to Oracle Database from SQL Developer".

See Also:

Connecting to Oracle Database as User HR from SQL*Plus

This section shows how to connect to Oracle Database as the user HR from SQL*Plus, if the HR account is unlocked.

To connect to Oracle Database as user HR from SQL*Plus:

Note:

For this task, you need the password for the HR account.
  1. If you are connected to Oracle Database, close your current connection.

  2. Follow the directions in "Connecting to Oracle Database from SQL*Plus", entering the user name HR at step 3 and the password for the HR account at step 4.

    You are now connected to Oracle Database as the user HR.

See Also:

SQL*Plus User's Guide and Reference for an example of using SQL*Plus to create an HR connection

Connecting to Oracle Database as User HR from SQL Developer

This section shows how to connect to Oracle Database as the user HR from SQL Developer, if the HR account is unlocked.

Note:

For the following procedure, you need the password for the HR account.

To connect to Oracle Database as user HR from SQL Developer:

  • Follow the directions in "Connecting to Oracle Database from SQL Developer", entering the following values at steps 7 and 9.

    At step 7:

    • For Connection Name, enter hr_conn.

      (You can enter a different name, but the tutorials in this document assume that you named the connection hr_conn.)

    • For Username, enter HR.

    • For Password, enter the password for the HR account.

    At step 9:

    • For Role, accept the default.

    • For Connection Type, accept the default (Basic).

    • For Hostname, enter localhost.

    • For Port, enter 1521.

    • For SID, enter orcl.

    At step 11, the name of the connection, hr_conn, appears in the Connections pane of the Oracle SQL Developer window.

You are now connected to Oracle Database as the user HR.