Connecting to Oracle Database XE

6 Connecting to Oracle Database XE

Connecting Locally using OS Authentication

When you install Oracle Database XE, your Windows user is automatically added to the ORA_DBA operating system group, which grants you the SYSDBA privileges. You can use the following commands to connect to the database. In the commands, replace "<oracle_home>" with the path to your Oracle Home; see Table 5-1 for more details about the Oracle Home:

cd <oracle_home>\bin
sqlplus / as sysdba

These commands connect you to the root container CDB$ROOT of the multitenant database (CDB) as database user SYS. This method of connecting to the database works even if the Net Services listener is not running.

Connecting to Oracle Database Using Easy Connect Naming Method

You can connect to the database using the following Easy Connect strings:

Multitenant container database: host[:port]
Pluggable database: host[:port]/service_name

XEPDB1 is the service name defined for the first PDB created by default. You can replace XEPDB1 with the name of another PDB you want to connect to.

Specifying the port is optional when the listener is setup with the default port 1521. You must specify the port number if you use another port.

Connection strings for local connections were provided on the final screen of the install. If you are connecting from a remote computer, you need to provide the hostname (where XE is installed) instead of localhost.

Please note that, Net Services database listener must be running on the database host on the specified port for the connections to succeed.

For example, you can connect to the root container of the database from a client computer with SQL*Plus using the following commands:

cd <oracle_home>\bin
sqlplus system@dbhost.example.com:1521

You can connect to the default pluggable database XEPDB1 using the following commands:

cd <oracle_home>\bin
sqlplus system@dbhost.example.com:1521/XEPDB1

Replace dbhost.example.com with your database host name. If needed, replace 1521 with the port number the listener listens on. You can replace XEPDB1 with the name of another PDB you want to connect to.

To shorten connect strings or to avoid hardcoding host name and port in application code and DBA scripts, you can define an alias for the connect string in the configuration file <oracle_base_home>\network\admin\tnsnames.ora on database clients. See Configuring the Local Naming Method for more details.

The Net Services Listener and Default Services

The Net Services database listener for your XE database allows you to connect to the database over TCP/IP from the same machine or other machines on the network. The configuration of the Listener can be viewed using the following commands run from the command prompt:

cd <oracle_home>\bin
lsnrctl status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\app\username\product\21c\homes\OraDB21Home1\network\admin\listener.ora
Listener Log File         C:\app\username\product\21c\diag\tnslsnr\den01mrn\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost.example.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=C:\app\username\product\21c\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "15288327eb2b45268e6a23a805a53f8e" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
SERVICE "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

The output from the lsnrctl command shows values of a number of important parameters:

the port the listener listens on
the list of services registered with the listener
the port EM Express listens on
the name of the configuration file used by the listener
the name of the log file

You specify a service when connecting to the database through the listener. The default services created by Oracle Database XE are XE and XEPDB1. The XE service connects you to the root container of the database (CDB$ROOT) and the XEPDB1 service connects you to the default pluggable database (XEPDB1) created at the installation time. For each new pluggable database (PDB) in the database, there will be a new default service created with the same name as the PDB.

Note:

If the Oracle Database XE instance is shut down, the lsnrctl status command does not show any services you can connect to.