Installing Oracle XE on Microsoft Windows Workstation

Version Information

Original Post: December 2016 Re-post on Medium: 3/21/2019

Introduction

I had some trouble when I first installed Oracle XE version on a Windows 7 machine. I captured some notes regarding Oracle XE installation/setup. The set up instructions below will work on Microsoft Windows 7 or Microsoft Windows 10.

Oracle XE (also called Oracle Database Express Edition 11g Release 2) is a local workstation version of Oracle RDMS software. The intended use of Oracle XE is for development purposes.

The software is free download and use provided you accept and abide by the OTN License Agreement for Oracle Database Express Edition 11g Release 2, which is available on Oracle’s web site. You will also need an Oracle user id to download the software.

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

Oracle XE Installation

You do need administrator rights on the Windows workstation in order to install Oracle XE. (The installation will create a Windows Service.) If you don’t have administrator rights on the Windows workstation, a local administrator can do the installation for you. After the installation, the administrator will need to change permissions on the Oracle XE installation directory so local user can write to that directory. When Oracle XE Windows Service begins, log files are written in Oracle XE directory. If a local user doesn’t have write permissions in that directory, then Oracle XE will not work.

Oracle XE Configuration

There are a couple steps to configure Oracle XE before the system is ready to run. Oracle XE will need a TNS Names entry in the tnsnames.ora in order for a client to connect. I created a directory on my C:\ drive, then pointed the Windows environment variable to use that location.

1. Create a directory called c:\SQLNet

2. Create tnsnames.ora text file in c:\SQLNet directory and add the following TNS Names entry:

xe = (DESCRIPTION =(ADDRESS=(PROTOCOL=tcp)(HOST=localhost) PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=xe)) )

3. Control Panel | System | Advanced System Settings

Environment Variables Under “User Variables”, add the following Variable: TNS_ADMIN Value: C:\SQLNet

You will need to reboot computer after creating a Windows Environment Variable.

Oracle Environment Configuration

Since it is generally considered a bad practice to use Oracle SYSTEM account for everything, a new user should be created for application use after installation. The examples I will use a datascience user schema in Oracle.

1. From a Windows command prompt, login to Oracle XE as SYSTEM user

C:> sqlplus system@xe

NOTE: SYSTEM password will be established during Oracle XE installation.

2. SQLPLUS — AS SYSTEM user (note: substitute %%&&password&&%% with actual password you intend to use with this new user id):

SQL> CREATE USER DATASCIENCEUSER IDENTIFIED BY %%&&password&&%%;

SQL> GRANT ALL PRIVILEGE TO DATASCIENCEUSER;

Oracle SQL Developer

Oracle SQL Developer is an IDE for Oracle Database developer and management activities. Oracle SQL Developer software is free but bound to OTN License Agreement.

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Download SQLDeveloper ZIP file. Extract ZIP on C:\ drive into a directory. Open SQLDeveloper using sqldeveloper.exe program.