Oracle Database: what it is and how it works

Oracle Database is a re­la­tion­al database man­age­ment system (RDBMS) from the American software and hardware man­u­fac­turer Oracle. Being database software, Oracle Database optimises the man­age­ment and security of data sets by creating struc­tured database schemas ac­cess­ible only to au­thor­ised ad­min­is­trat­ors.

Oracle Database is a market leader along with SAP HANA, Microsoft SQL Server, and IBM Db2 in the field of re­la­tion­al database man­age­ment systems (RDBMS for short). According to DB-Engines Ranking, Oracle ranks first among the 380 most popular database systems, im­me­di­ately followed by MySQL and Microsoft SQL Server. Although Oracle, founded in 1977 by Lawrence J. Ellison, now offers a broad portfolio of products and services, Oracle Database remains the flagship of the American man­u­fac­turer. The first version came on the market in 1979. Currently, the long-term version 19c and the In­nov­a­tion Release 21c are available (as of October 2021).

In a nutshell, Oracle Database forms the core of companies’ IT en­vir­on­ments. Databases can be dif­fer­en­ti­ated into various hier­arch­ic­al, network, object, or document-ori­ent­ated models, depending on the struc­tur­ing pattern. Oracle uses a re­la­tion­al database model for Oracle Database, which makes it possible to store and display company and customer data as organised data sets. Data sets are struc­tured into columns, tables, and rows, and data points are related using at­trib­utes. Oracle Database is suc­cess­ful due to its intuitive and efficient or­gan­isa­tion and present­a­tion of data sets. In addition, companies can decide whether to use Oracle Database in local on-premises or cloud en­vir­on­ments.

Oracle Database, like most RDBMS, uses the stand­ard­ised pro­gram­ming language SQL (Struc­tured Query Language) to create database struc­tures, manage records, perform actions, or retrieve contained data. Oracle’s own pro­gram­ming language PL/SQL, in turn, is closely related to SQL and lets you add Oracle pro­gram­ming ex­ten­sions to SQL. To structure the databases, Oracle uses row and column tables in which data points are linked via at­trib­utes. This makes cross-table access efficient and time effective.

The ar­chi­tec­ture of Oracle database systems consists of a database for storing database files, one or more database instances for data man­age­ment, and one or more listener processes that connect database clients to database instances. Here, logical and physical data struc­tures are separated into Oracle databases. These include physical and logical storage struc­tures:

1. Physical storage struc­tures: Data files, control files (with database metadata), and red-log files (for doc­u­ment­ing changes).
2. Logical storage struc­tures: Data blocks and tables, extents (for grouping logical data blocks), segments (extent records) and ta­blespaces (logical segment con­tain­ers).

The clear structure of Oracle databases ensures that data is reliably managed with maximum security measures thanks to data and network en­cryp­tion, strict au­then­tic­a­tion, au­thor­isa­tion, and au­thor­isa­tion analyses. In addition, Oracle supports Java and retrieves Java pro­gram­ming with PL/SQL.

Oracle provides various developer and man­age­ment tools for the de­vel­op­ment and extension of Oracle databases:

• SQL*Plus: Available on all computer systems that use the Oracle client or server software. As a command line tool for database ad­min­is­tra­tion, it allows command entries, data queries, and changes or deletions of database files. SQL knowledge is mandatory for SQL*Plus.
• Oracle SQL Developer: A free Java program with graphical user interface that creates or edits database projects, manages SQL state­ments/scripts, performs database analyses, or generates or debugs PL/SQL pro­ced­ures.
• Oracle Data Modeler: A free tool aimed primarily at database designers. The Modeler can be used to design logical database models or entity-re­la­tion­ship models. The tool’s strengths include its intuitive operation (drag & drop), the mapping of complex database struc­tures, and the export of database struc­tures to the Oracle SQL Developer Tool.
• Oracle En­ter­prise Manager Database Control: A web-based man­age­ment tool for Oracle databases that provides a graphical user interface.
• Oracle En­ter­prise Manager Grid Control: A flexible ad­min­is­tra­tion tool for Oracle en­vir­on­ments that is web-based and provides a graphical user interface. It can be used for multiple databases, clusters, as well as standby systems.
• Oracle JDeveloper: An Oracle developer tool whose in­teg­rated de­vel­op­ment en­vir­on­ment with Oracle and Java helps in de­vel­op­ing database ap­plic­a­tions.

Currently, Oracle Database products can be divided into four main Oracle Editions, which are suitable for different areas of ap­plic­a­tion depending on the size of the company.

The Oracle Express Edition is a free Oracle Database, which is suitable for every client and provides a free database (e.g. for training or small ap­plic­a­tions). The Express Edition supports PHP, Java, XML, and .NET. Since it’s a free edition, the memory is limited to 4 GB and the RAM to 1 GB. In addition, only one CPU is available.

Large medium-sized companies tend to choose the Oracle Standard Edition. Its ad­vant­ages include intuitive in­stall­a­tion and con­fig­ur­a­tion, automated man­age­ment functions, an efficient and clear ad­min­is­tra­tion of large data sets, as well as a high com­pat­ib­il­ity with all common data types and ap­plic­a­tions.

Oracle’s En­ter­prise Edition is the deluxe version of Oracle Database and also reserves a top price among RDBMS. Since the En­ter­prise Edition hardly sets limits in terms of storage, extension, and data volume man­age­ment, it’s mainly suitable for large en­ter­prises that work with enormous volumes of data. Other ad­vant­ages include reliable security features against data loss, power failures, and software errors.

The ad­vant­ages and dis­ad­vant­ages of Oracle depend primarily on user re­quire­ments and needs as well as costs, technical com­pet­en­cies, and pro­gram­ming skills. A major advantage of Oracle Database is the optional Database-as-a-Service model. This enables re­la­tion­al databases to be stored and managed in the Oracle Cloud. This ensures optimal use of CPU, hardware and storage ca­pa­cit­ies, as well as the out­sourcing of ad­min­is­trat­ive database man­age­ment tasks. The highest security standards also ensure the greatest possible pro­tec­tion against data loss, cyber attacks, and security breaches.

Some of the benefits of Oracle Database include:

• High com­pat­ib­il­ity with all platforms and ap­plic­a­tions
• Support from all major software and hardware vendors
• Different editions from free to en­ter­prise level
• Widely used in the en­ter­prise IT sector
• Optional use of Oracle Cloud Databases for out­sourcing and auto­ma­tion of database man­age­ment
• Most popular re­la­tion­al database man­age­ment system
• Large developer community and high-quality Oracle support
• Robust security and privacy features (e.g., strong au­then­tic­a­tion and au­thor­isa­tion of access, en­cryp­tion of data and networks)

The ad­vant­ages of Oracle databases outweigh the dis­ad­vant­ages, but you should also be aware of its weak­nesses.

• Extensive SQL knowledge and ad­min­is­trat­ive ex­per­i­ence in database man­age­ment are a pre­requis­ite for the local on-premises version of Oracle
• Oracle licenses are expensive (Standard Edition approx. 12,000 GBP, En­ter­prise Edition approx. 30,000 GBP)
• High hardware re­quire­ments for local on-premises version

If Oracle Database isn’t an option for you, there are many other al­tern­at­ive database man­age­ment systems to choose from. SAP and IBM are among the best-known providers of database systems besides Oracle:

• SAP HANA
• IBM Db2
• Amazon Re­la­tion­al Database Service (RDS)
• Amazon Aurora
• Microsoft SQL
• MySQL
• SQLite
• Azure SQL Database

Ad­di­tion­ally, there are several free, open-source database man­age­ment systems:

• MariaDB
• NoSQL
• InfluxDB as GPL license
• InnoDB
• CouchDB
• MongoDB
• Post­gr­eSQL