6 Connecting to Oracle Database XE
Connecting Locally using OS Authentication
When you install Oracle Database XE, the oracle user is granted SYSDBA privileges. You can use the following commands to connect to the database.
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.
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 Linux: Version 21.0.0.0.0 - Production Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File /opt/oracle/homes/OraDB21Home1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/dbhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost.example.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=dbhost.example.com)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "77f81bd10c818208e053410cc40aef5a" has 1 instance(s). Instance "XE", status READY, 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
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, thelsnrctl
status command does not show any services you can connect to.
Connecting to Oracle Database Using Easy Connect Naming Method
-
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.
cd <oracle_home>/bin sqlplus system@dbhost.example.com:1521
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 orabasehome/network/admin/tnsnames.ora
on database clients. See
Configuring the Local Naming Method for more
details.
See Also:
-
2 Day DBA for more information about the listener and network configuration
- Oracle Database Development Guide for more information about connection strategies for database applications
- Oracle Database Net Services Administrator's Guide and Oracle Database Net Services Reference for more information about Net Services and their configuration.
- Oracle Multitenant Administrator’s Guide for more information about containers and connecting to a container in a CDB.