3
Connecting to the Database
To interact with Oracle Database XE, you must typically connect to the database as a database user. The interaction might be through the SQL Command Line, through SQL Developer, or through utilities invoked from the system command line.
This chapter focuses on the SQL Command Line (SQL*Plus). For information about using database connections in SQL Developer, see the section about creating database connections in
Oracle Database Express Edition Getting Started Guide
.
This chapter covers the following topics:
About Local and Remote Connections
Setting Environment Variables
Connecting Locally with the SQL Command Line
Connecting Remotely with the SQL Command Line
Environment Variables Reference for Linux
See Also:
The following documents, for information on how to connect to the database from your appli
cation:
Oracle Database Express Edition Java 2 Day Developer Guide
Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Oracle Database Express Edition 2 Day + PHP Developer's Guide
About Local and Remote Connections
Oracle Database XE supports connections between the SQL Command Line (SQL*Plus) and the database either
locally
, or
remotely
over a TCP/IP network. The method that you use to connect to Oracle Database XE with the SQL Command Line depends on whether you are initiating a local connection or a remote connection. Local and remote connections are explained in the following sections:
About Local Connections
About Remote Connections
See Also:
"Connecting Locally with the SQL Command Line"
"Connecting Remotely with the SQL Command Line"
"Managing Network Connections"
for information on how Oracle Database XE accepts connection requests over the network.
About Local Connections
Connect
ing
locally
means running the SQL Command Line (or any other Oracle command-line utility) on the same host computer where Oracle Database XE is installed (the "Oracle Database XE host computer") and then initiating a database connection from the SQL Command Line (or other utility), or using SQL Developer to connect as a local database user. To connect locally, you must supply only a database user name and password. For example,
Figure 3-1
shows a user connecting locally with the SQL Command Line and entering a
connect
command that supplies the user name
hr
and password
hr
.
Figure 3-1 Local Connection
Description of "Figure 3-1 Local Connection"
Environment Variables
Before making a local connection on Linux, you must set environment variables. See
"Setting Environment Variables"
for more information.
Note:
Logging in to the Oracle Database XE host computer with an
ssh
(or
telnet
) session and then starting the SQL Command Line (or other Oracle command-line utility) is considered a local connection, even though you start the
ssh
(or
telnet
) application on a remote computer.
About Remote Connections
Connectin
g
remotely
means running the SQL Command Line (or any other Oracle command-line utility) on a computer other than the Oracle Database XE host computer, and then initiating a database connection from the SQL Command Line (or other utility) over the network.
Oracle Client Software
The remote computer must have Oracle client software installed. It is through Oracle client software that Oracle command-line utilities (and your applications) connect to the database. Oracle Database XE accepts connections from all of the following types of Oracle clien
t software:
Oracle Database Express Edition Client (Oracle Database XE)
When you install Oracle Database XE, Oracle Database Express Edition Client is also installed on the same computer. You can install Oracle Database XE separately on remote computers. It is available at
http://www.oracle.com/technetwork/database/express-edition/
Instan
t Clie
nt
Instant Client is available at
http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html
Oracle client software for Oracle Database Enterprise Edition or Standard Edition (all supported releases of Oracle Database)
All Oracle client software includes Oracle Net, which is the Oracle network software that enables client applications on one computer to connect to databases on another computer over a network.
Connect Strings
To connect remotely, you must supply not just a user name and password, but a complete Oracle Net conne
ct string. In addition to the database user name and password, a
connect string
includes a host name or host IP address, an optional TCP port number, and an optional database service name. These additional parameters are required to help Oracle Net find the right host computer and connect to Oracle Database XE. An Oracle Net connect string has the following format:
username
/
password
@[//]
host
[:
port
][/
service_name
]
where:
//
is optional
host
is the host name or IP address of the computer that is running Oracle Database XE
port
(optional) is the TCP port number on which the Oracle Net listener is listening. If not specified, the default port number 1521 is assumed.
service_name
(optional) is the name of the database service to which to connect. For Oracle Database XE, the service name is
XE
. If
service_name
is omitted, Oracle Database XE Client appends a request for the
default database service
, which is configured during installation as
XE
.
Note:
Only Oracle Database XE supports the notion of a default database service. If you connect remotely from any Oracle client software other than Oracle Database XE, you must include the
XE
service name.
For example,
Figure 3-2
shows a user connecting remotely with the SQL Command Line and entering a
connect
command that includes a connect string that supplies: the user name
hr
, the password
hr
, and the host name
dbhost.example.com
. This connect string connects to the default database service (
XE
) on the default port (
1521
).
Figure 3-2 Remote Connection
Description of "Figure 3-2 Remote Connection"
Environment Variables
Before making a remote connection from Linux, you must set environment variables. See
"Setting Environment Variables"
for more information.
Remote Connection Examples
In the following examples of the SQL Command Line
connect
commands, Oracle Database XE is running on the host computer
mydbserver.example.com
.
Example 1
This example initiates a remote connection from Oracle Database XE, using the default port number.
CONNECT system/[email protected]
Example 2
This example initiates a remote connection from Oracle Database XE, using a nondefault port number (1522):
CONNECT system/[email protected]:1522
Example 3
This example initiates a remote connection using the default port number, and includes the optional service name.
CONNECT system/[email protected]/XE
Setting Environment Variables
the SQL Command Line (SQL*Plus) and other Oracle utilities retrieve configuration information from operating system environment variables. This section explains how to set these environment variables, and contains the following topics:
Setting Environment Variables on the Windows Platform
Setting Environment Variables on the Linux Platform
Setting Environment Variables on the Linux Pla
tform
On the Linux platform, before running the SQL Command Line or other Oracle utilities from a terminal session, you must set some environment variables for that session. The procedure for setting environment variables depends on whether you are connecting locally, or connecting remotely from Oracle Database XE.
Setting Environment Variables for a Local Connection
To set environment variables when connecting locally, enter one of the following commands in a terminal session.
For Bourne, Korn, or Bash shell:
source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
For C shell:
source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.csh
See
"Environment Variables Reference for Linux"
for more information.
Note:
There is no need to set environment variables before running the SQL Command Line from the K menu (KDE) or Applications menu (Gnome).
Setting Environment Variables for a Remote Connection
To set environment variables when connecting remotely from Oracle Database XE, enter one of the following commands in a terminal session at the remote computer:
For Bourne, Korn, or Bash shell:
source /usr/lib/oracle/xe/app/oracle/product/11.2.0/client/bin/oracle_env.sh
For C shell:
source /usr/lib/oracle/xe/app/oracle/product/11.2.0/client/bin/oracle_env.csh
See
"Environment Variables Reference for Linux"
for more information.
See Also:
"About Local and Remote Connections"
"Connecting Locally with the SQL Command Line"
"Connecting Remotely with the SQL Command Line"
Connecting locally means running the SQL Command Line (SQL*Plus) and Oracle Database XE on the same computer. There are two ways to start a local connection with the SQL Command Line:
From the desktop
From a terminal session (Linux) or command window (Windows)
Starting the SQL Command Line from the Desktop
To start the SQL Command Line from the desktop and connect locally:
Do one of the following:
On Windows: Click
Start
, point to
Programs (or All Programs)
, point to
Oracle Database 11g Express Edition
, and then select
Run SQL Command Line
.
On Linux with Gnome: In the Applications menu, point to
Oracle Database 11g Express Edition
, and then select
Run SQL Command Line
.
On Linux with KDE: Click the icon for the K Menu, point to
Oracle Database 11g Express Edition
, and then select
Run SQL Command Line
.
A the SQL Command Line command window opens.
At the SQL Command Line prompt, enter the following command:
CONNECT
username
/
password
For example, to connect as user
HR
with the password
PEOPLE
, enter the following command:
CONNECT HR/PEOPLE
Starting the SQL Command Line from a Terminal Session or Command Window
To start the SQL Command Line from a terminal session or command window and connect locally:
If not already open, open a terminal session (Linux) or a command window (Windows).
(Linux only) If the required environment variables are not already set for your session, set them as described in
"Setting Environment Variables on the Linux Platform"
.
Enter the following command at the operating system prompt:
sqlplus /nolog
At the SQL Command Line prompt, enter the following command:
CONNECT
username
/
password
For example, to connect as user
HR
with the password
PEOPLE
, enter the following command:
CONNECT HR/PEOPLE
See Also:
"About Local and Remote Connections"
Connecting remotely means running the SQL Command Line (SQL*Plus) on one computer (the remote computer), and then initiating a connection to Oracle Database XE on a different computer.
To initiate a remote connection from the SQL Command Line using the Oracle Database XE:
On the remote computer, start a terminal session (Linux) or open a command window (Windows.)
If prompted for host credentials, log in to the remote computer.
(Linux only) If the required environment variables are not already set for your session, set them as described in
"Setting Environment Variables on the Linux Platform"
.
Enter the following command at the operating system prompt:
sqlplus /nolog
Enter a
CONNECT
command at the SQL Command Line prompt, supplying a connect string.
CONNECT
username
/
password
@[//]
host
[:
port
][/
service_name
]
See
"About Remote Connections"
for a description and examples of connect strings.
See Also:
"About Local and Remote Connections"
Environment Variables Reference for Linux
This section provides reference information for setting environment variables on Linux for the following two scenarios:
Connecting locally
Connecting remotely from Oracle Database XE.
Table 3-1
lists the environment variables that you must set for each of these scenarios.
Table 3-2
provides environment variable descriptions and required values.
Table 3-1 Require
d Linux Environment Variables for Connecting with Oracle Utilities
Connection Type
Required Environment Variables
For local connection:
/usr/lib/oracle/xe/app/oracle/product/11.2.0/server
For remote connection with Oracle Database XE:
/usr/lib/oracle/xe/app/oracle/product/11.2.0/client
Locale (language and territory used by client applications and the database; character set used by client applications)
(The desired language, territory, and character set. See
Oracle Database Express Edition Installation Guide for Linux x86-64
for details.)
Defaults to
AMERICAN_AMERICA.US7ASCII
Search path used by the SQL Command Line (SQL*Plus) for
*.sql
scripts. Contains a colon-separated list of paths. Must include the location of the site profile script,
glogin.sql
.
$ORACLE_HOME/sqlplus/admin
Example
The following are the Bash shell commands that set the required environment variables for a local connection on a Linux installation in the United States:
ORACLE_SID=XE;export ORACLE_SID
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/11.2.0/server;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH;export PATH
NLS_LANG=AMERICAN_AMERICA.AL32UTF8;export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
Environment Variable Sc
ripts
Oracle Database XE and Oracle Database XE ship with two shell scripts that you can use to easily set environment variables. The scripts are located in
$ORACLE_HOME/bin
and are named as follows:
oracle_env.sh
(for Bourne, Korn, or Bash shell)
oracle_env.csh
(for C shell)
You can invoke
these scripts from within dot files so that environment variables are set automatically each time that you start a new terminal session (start a new shell). The following is an example of a command that you can add to the
.cshrc
file in your home directory:
source /usr/lib/oracle/xe/app/oracle/product/11.2.0/server/bin/oracle_env.csh
