In today's digital world, data is everywhere--from your online shopping history to the metrics logged by industrial IoT sensors. But how is all the data stored, organized, and retrieved with consistency across millions of users? The answer lies in the database.
A database, particularly a relational database, is essentially a systematically organized collection of data values stored in tables with formally defined relationships between them, which allows the data to be securely accessed and managed by multiple users. Whether it's a small app tracking your daily tasks or a massive system powering global banking transactions, databases are invisible engines that uphold the integrity of today's digital world. The software responsible for managing such systems is called a relational database management system (RDBMS).
Introducing Oracle Database XE
In 1977, Larry Ellison, Bob Miner, and Ed Oates founded a company called Software Development Laboratories with the aim of developing a database management system for a CIA project codenamed "Oracle." What began as a government-focused initiative quickly evolved into a broader commercial vision: to create a market-ready implementation of E. F. Codd's relational data theory. In 1979, the company released its first product--Oracle Version 2--which is one of the earliest commercial-software solutions to manage a relational database. The company later adopted the name Oracle, and over the decades, it grew into the global leader in enterprise software and database technology. Today, Oracle Database is trusted by governments, Fortune 500 companies, and developers alike to handle everything from transaction processing to data analytics.
 |
| Oracle co-founders Ed Oates, Bruce Scott, Bob Miner and Larry Ellison mark the company’s first year of operations in 1978 |
To make its product more accessible, Oracle introduced Oracle Database Express Edition (XE), a free and lightweight version of its flagship product. Designed for learners and individual developers, Oracle XE provides core database features without any licensing costs, allowing its users to build, test, and deploy applications running on Oracle Database. The latest release, Oracle XE 21c, supports up to two CPU cores, 2 gigabytes of RAM, 12 gigabytes of data storage, accommodating up to three pluggable databases. Despite its compact footprint, Oracle XE is more than sufficient for practicing SQL and PL/SQL, as well as for prototyping robust database applications.
How to Install Oracle XE on Your PC: A Step-by-Step Guide
Prerequisites
- Windows 10 x64--supported editions include Pro, Pro for Workstations, Enterprise, and Education
- 8.5 GB of disk space for Oracle software plus 2 GB or more for data storage and minimum 2 GB of RAM
- Administrator privileges
Important: Before proceeding, check if the ORACLE_HOME or TNS_ADMIN environment variables have already been configured on your machine. They must be deleted before continuing if present, as they can interfere with a clean installation. Follow these steps to check:
- Open the Windows Start Menu.
- Type "Environment Variables" and select the option that appears: "Edit the system environment variables." This opens the System Properties window.
- In the System Properties window, click the Environment Variables... button.
- The Environment Variables window is split into two boxed: User variables and System variables.
- Carefully check both the User variables and System variables lists for any entry named ORACLE HOME or TNS_ADMIN.
Step 1: Download and Launch the Installer
- Navigate to the official Oracle Database XE downloads page.
- Locate and download the Microsoft Windows x64 version of the Oracle Database XE 21c installer (it will be a large ZIP file).
- Note: You will need an Oracle account to initiate the download, but creating one is free.
- Extract the downloaded ZIP file (OracleXE213_Win64.zip) to a convenient, temporary location on your machine.
- Open the newly extracted folder.
- Locate and double-click the installation executable (setup.exe), to launch the InstallShield Wizard.
Click "Next >" to start installation.
Step 2: Review the License Agreements
Review the license agreements for Oracle Database 21c XE, and select "I accept the terms in the license agreement". Then click "Next >" to proceed.
Step 3: Select the Installation Directory
Next, you'll be prompted to choose a destination folder for the installation. This directory will house the software source files, database files, and system logs. By default, it is set to C:\app\<username>\product\21c.
- C:\app\<username>\product\21c\ -- Oracle Base: Serves as the root directory for the Oracle XE installation, containing all related subdirectories.
- C:\app\<username>\product\21c\dbhomeXE -- Oracle Home: The main installation directory for Oracle XE, including executable files, configuration files, and network files.
- C:\app\<username>\product\21c\oradata\XE -- Database Files: Stores the physical data files for the XE database instance.
- C:\app\<username>\product\21c\diag\rdbms\XE\XE\trace -- Diagnostic Logs: Contains trace files and diagnostic logs used for trouble shooting and performance analysis.
- C:\app\<username>\product\21c\cfgtoollogs\ -- Configuration Logs: Records logs related to database installation, creation, and configuration processes.
Step 4: Set Password
If you prefer to choose a different installation directory, click "Change." Make sure that the selected directory path does not contain any spaces or non-Roman alphabet characters to avoid potential installation issues.
Following that, enter and confirm a password to use for the SYS, SYSTEM, and PDBADMIN database accounts. Password recovery is not easy, and any unauthorized access or file modification without proper knowledge may potentially result in data corruption. So, please select a secure yet memorable password.
Step 5: Review Installation Summary and Start Installation
Lastly, review your installation settings. If everything looks correct, click "Install" to begin the database installation. If you need to make changes, click "< Back" to adjust the settings as needed.
After a few minutes, the installation will complete. Be sure to record the connection details provided for multitenant container database, pluggable database, and EM Express URL, as you'll need them for post-installation setups covered in the next section. When you're ready, click "Finish" to close the installer.
Post-Installation Setups
Installing Database Schemas on Oracle XE
For those new to Oracle Database, self-learners preparing for Oracle certifications, or developers working on database applications, Oracle sample schemas can be a practical starting point. They provide hands-on examples to explore database features, understand schema design principles, and test application functionalities in a realistic scenarios.
In the following subsection, we'll walk through the process of installing a schema using Oracle sample database. Before diving in, we first take a brief look at Oracle's multitenant architecture to better understand the context behind the schema installation process. Let's get started!
A Brief Explanation of Oracle's Multitenant Architecture
Starting with version 12c, Oracle introduced the multitenant architecture. In the multitenant architecture, Oracle uses a single container database (CDB) that hosts one or more pluggable databases (PDBs). The CDB does not store any user data or application objects; instead, it merely serves as the root container for the PDBs installed on it.
Prior to version 12c, each database required its own dedicated Oracle instance. For example, each of the HR, customer orders, and sales history systems would need to run under a separate instance--even if hosted on the same physical server. This approach drove up operational costs as companies added new systems to support their business functions, because they had to pay for separate licensing for each individual database instance.
In this new database architecture, on the other hand, users pay only for the single CDB that supports multiple PDBs, though the Multitenant Option is required as an additional license if more than three user-created PDBs are hosted (in version 19c and later). A CDB can contain zero, one, or many customer-created PDBs. Each PDB is a portable collection of database objects and appears as an independent database to the clients.
How to Install Oracle Sample Schemas: A Step-by-Step Guide
Step 1: Download and Extract Sample Schemas
Go to GitHub repository for Oracle sample schemas and download "Source code (zip)." Once the zip file is downloaded, extract the it and move all contents to:
C:\app\<username>\product\21c\dbhomeXE\oradata\sample\If you chose different path during Oracle XE setup, place the files in the \oradata\sample under your Oracle Home directory.
Step 2: Open Command Prompt and Navigate to Schema Folder
Next, open a new Windows Command Prompt and navigate to the schema directory you want to install. For example, to install the HR schema:
cd <oracle_home>\sample\HRStep 3: Connect to the Database as SYSDBA
Launch SQL*Plus[1] with administrative privilege:
sqlplus / as sysdbaThis connects you to the root container (CDB$ROOT) as the SYS user. You can verify this by querying the V$DATABASE view as follows:
Step 4: Identify Available Pluggable Databases
Retrieve the list of available PDBs to identify which one you need to switch to:
SELECT NAME FROM V$PDBS;Then, alter the current session to your selected PDB (typically XEPDB1 for Oracle XE):[2]
ALTER SESSION SET CONTAINER=XEPDB1;Step 5: Run the Schema Installation Script
Next, run the @<schema>_install.sql script for your chosen schema:
.PNG)
During the installation, you will be prompted for:
- Password: Enter an Oracle Database compliant password
- Tablespace: If you do not enter a tablespace, the default database tablespace is used.
Note that if the schema already exists, it is removed/dropped and a fresh schema is installed. After installation, type EXIT to quit SQL* Plus. You can install the other schemas using the same way.
Optional: Creating a New User with Cross-Schema Access
In Oracle, a schema is essentially synonymous with a database user.[3] Once the sample schema installations are complete, new users--HR, CO, and SH--are created. Each of these users owns and access their respective schema objects, such as tables, views, indexes, and stored procedures. For example, the HR user manages employee-related data. CO and SH represent different business domains with their own schema objects.
However, it could be more convenient to have another user that can access all three sample schemas for developing inter-business applications or practicing cross-schema queries. To create a new user, open another Windows Command Prompt and start SQL*Plus. You will be prompted to enter a username and password. Enter system as the username and the password you specified during installation (inside quotes.) This will connect you to the root container.
Then, alter session to your pluggable database as before:
ALTER SESSION SET CONTAINER=XEPDB1;Next, create a new user by entering the following command (after replacing username ora_dev and password pw1234):
CREATE USER ora_dev IDENTIFIED BY pw1234;Following that, grant unlimited tablespace and DBA privilege to the newly created user:
GRANT UNLIMITED TABLESPACE TO ora_dev;GRANT DBA TO ora_dev;Note: This would probably not be something that your typical production DBA would do. Tablespaces are collected by database administrators. A typical DBA generally creates uniquely named tablespaces and carefully allocates space quotes to them. We, however, aren't concerned with any of that for now.
Setting Up SQL Developer for Oracle Database
SQL Developer is a free, integrated development environment that simplifies database management, data modeling, and PL/SQL application developments for Oracle Database. It has a user-friendly interface for executing SQL queries, browsing schema objects, and managing database workflow efficiently.
To set up SQL Developer on your PC, go to the SQL Developer download page on Oracle's website and download the version for Windows 64-bin with JDK included. Once the download is complete, extract the ZIP file to your preferred destination. You'll find the "sqldeveloper" icon in the folder. Double-clicking it will open the application; no further installation for the software is required.
Connecting to Databases with Oracle SQL Developer
On the upper left corner on the connections tab, click the "+" button to create a new database connection. Then in the "New / Select Database Connection" window that appears, enter the Name (connection name), username (the database user created when you run @hr_install.sql for example). Optionally, you can specify the color for the appearance of your connection. Next, select "Service name" radio button and enter the PDB name where the user object resides (e.g., XEPDB1).
Click "Test" button to check if the connection is valid. If you see the message "Status : Success," click "Save" to save the connection settings and "Connect." Entering HR and its password will connects you to the HR schema.
Similarly, you can connect to the other schemas. For example, to connect to the ora_dev user that we created earlier:
This user have an access to all three sample schema objects. For example:
First Look at Oracle SQL Developer
Let's take a brief look at how to use the SQL Developer. On the menu bar, click "SQL Worksheet" button to open a new SQL worksheet with a connection. This is the main place where you can write your SQL codes.
Right click on the left side of the worksheet and select "Toggle Line Numbers," for better interface.
On the left-hand side panel, you'll see a section named "Connections." This section lists your established database connections. Each connection, when expanded, displays database objects, such as tables, views, or indexes.
If you click on an object, SQL Developer will open a new tab that describes the object.
Dragging-and-dropping a table or view object will automatically generate a draft statement for common tasks.
You can also use its GUI to create a new table. Right click on the "Tables" folder and select "New Table...". In the new window that appears, you can select schema where the new table will be located, specify the table name, and define its columns.
Oracle SQL Developer also supports auto formatting, which automatically arranges the appearance of your code. The shortcut for auto formatting is [Ctrl] + [F7], by default. To modify the formatter settings, go to "Tools" → "Preferences..." on the top menu bar, and select "Code Editor."
After writing an SQL query script, you can click either "Run Statement" or "Run Script" button on the worksheet. Basically, the "Run Statement" button will run the selected SQL statement in the worksheet, while the "Run Script" button will run the entire SQL script on the sheet. For example, let's take a look at the following SQL script.
This script creates a new table with a single column, inserts a data value into the table with a commit, retrieves all rows from the table, and finally drops the created table. Now, let's click the "Run Script" button and see what happens.
We see that the entire statements were executed in an order. On the other hand, the "Run" button only runs the selected statement. For the SELECT statement, it will show the query result in a new tab. For example:
However, if you select the entire script and click the "Run" button instead of "Run Script," you might not see any retrieved results. This happens because there are two different threads happening: one selects the data and the other drops the table. So, depending on which gets there first, you won't have any data to retrieve. If the table is dropped first, then there is no data to select.
Generating ERDs for the Sample Schemas
An ERD, or Entity Relationship Diagram, is a visual representation of the data structure within an RDBMS. It illustrates how different entities--such as employees or departments--relate to one another in a system. Each entity is shown as a box, with its attributes listed inside, and relationships between entities are depicted using lines and connectors.
To create an ERD using an existing schema in Oracle SQL Developer, follow these steps:
- Step 1: Open SQL Developer and go to "File" → "Data Modeler" → "Import" → "Data Dictionary."
- Step 2: In the Data Dictionary Import wizard, select your database connection and click "Next."
- Step 3: Choose the schema/database that you want to include in the ERD, then click "Next."
- Step 4: Select the schema objects (e.g., tables, views) to import and click "Next."
- Step 5: Review the design summary, then click "Finish" to generate the ERD.
The ERD above depicts the structure and relationships among entities in an HR schema. Each yellow box represents a table, listing its columns along with their data types and constraints. For example, the departments table contains four columns:
- department_id
- department_name
- manager_id
- location_id
The department_id column is marked with a 'P', indicating that the column acts as the primary key for the table. That is, each row in the table is uniquely identified by this column. In contrast, manager_id and location_id are marked with an 'F', signifying they are foreign keys that reference other tables.
Optional: Uninstalling Oracle Database
If you have followed the installation process as outlined so far, there should be no issues. However, in case where you do encounter any problems, it is good to know how to properly uninstall Oracle so you can reinstall it later.
On your Windows PC, navigate to the "Control Panel." Then select "System and Security" - "Administrative Tools" and "Services" and click "Stop" on any services that starts with "Oracle."
Next, navigate to Oracle Home directory, which is: C:\app\<username>\product\21c\dbhomeXE. You'll find a folder named "deinstall." Open the folder and run the "deinstall.bat" file as administrator.
In the Windows Command Prompt that appears, specify the Oracle Database listeners and the name of the database that you want to delete (it will show up inside the brackets.) Then enter y to delete the database.
0 Comments