Introduction to Oracle Database

Back to: Oracle DBA Tutorials

Introduction to Oracle Database:

In this article, I am going to give you a brief introduction to Oracle Database. I hope most of the things you know in this article. So, before starting the Oracle DBA Main concepts, let us just revise some of the basic things of the Oracle database. As part of this article, we are going to discuss the following pointers.

1. What is Data?
2. What is Database?
3. What is DBMS/ RDBMS?
4. What Is the Table?
5. What are SQL and PL/SQL?
6. Types of SQL statements
7. What is the Relational Database?
8. What is Normalization?
9. Problems without normalization.
10. Types of Hosting oracle database 12c/ 18c
11. History of Oracle Database
12. Editions of Oracle Database

What is Data?

Data is facts or figures. It is a piece of information. And usually, the data is unprocessed and unorganized.

What is Database?

It is the organized collection of information.

What is DBMS?

Because we have data in the database, we need a system to manage the data and we call this system DBMS. So, DBMS is the Database management system to store and retrieve and modify data in the database. And because Oracle is a relational database, then we have something called RDBMS which is a Relational Database Management System.

The purpose of Oracle Database is to store, organize, and retrieve data for your applications.

What is an Application?

Application is the interface between the users and the Database

What is the Table?

A table is the basic storage unit of an RDBMS. It is a logical unit, not physical. We will talk about logical and physical in detail in our upcoming articles. A table consists of rows and columns. For example, I have a table called Departments as shown in the below image. The following table contains two columns i.e. DEPARTMENT_ID and DEPARTMENT_NAME and we call the horizontal line as Row.

What is SQL?

To access the Oracle database, we need SQL. SQL stands for Structured Query Language and we use SQL to access the database. Please have a look at the following diagram for a better understanding. Here, I have an Oracle server and I have a user. In oracle, if the user wants to access the database, he should have a username and password. The user uses development tools like SQL Developer or SQL Plus, in order to write SQL statements. So, he will use the development tools to write the SELECT statement and the SELECT statement goes to the Oracle Server and Oracle Server will return the Result back.

Types of SQL Statement:

We mainly categorize the SQL Statement into four categories like Data Manipulation Language (DML), Data Definition Language (DDL), Data Control Language (DCL), and Transaction Control Language (TCL). Each category provides a set of commands to work with them. For a better understanding, please have a look at the following image.

Do some people ask why the SELECT statement is a powerful DML statement? Because we have something called SELECT FOR UPDATE.

What is PL/SQL?

SQL is basically used to access the database. We have something called PL/SQL. PL/SQL is the procedural extension to SQL with design features of programming languages. So, with PL/SQL, you can write, for loop, if statement, while loop. So, you can write statements in programming languages like Java, C#, etc.

Another important point to note is that Data Manipulation and Query Statements of SQL are included with procedural units (PL/SQL) of code. That means within PL/SQL, we write SQL Statements.

Note: In oracle, we called it PL/SQL and in Microsoft SQL Server, we called it T-SQL.

What is RDBMS?

Oracle is a Relational Database. So, what is a Relational Database, let us understand this. A relational database stores data in a set of simple relations. For example, we have table DEPT and table EMP. In table DEPT, we have two columns DEPTNO and DNAME. And in the EMP table, we have three columns i.e. EMPNO, DEPTNO, and ENAME. So, there is some relation between EMP and DEPT tables. In the EMP table, the DEPTNO is the foreign key which is referring to the DEPT table DEPTNO column.

If I ask you, EMPNO 7782 department is 10. Can you tell me the Department Name of Department 10? Simply, you will look at the DEPT table and you will look at the DEPTNO column, and from the DEPTNO 10, you will see that the DNAME is ACCOUNTS. So, you can say the Department Name is ACCOUNTS.

So, this is the relation between databases and we call this Normalization.

What is Normalization?

Normalization is a database design technique that organizes tables in a manner that reduces redundancy and dependency of data. It divides larger tables into smaller tables and links them using relationships. Now, someone may ask why I have to do Normalization, and if I did not do normalization then what would happen?

Please have a look at the below diagram. Here, I am showing two examples. One example is without normalization and another example is with normalization.

Without Normalization, we have added the DNAME column in the EMP table. Then, in this case, I have to repeat the Department Name in each entry. With this approach, we will get the following problems.

1. Extra Memory Space.
2. Difficult to handle and update the database. In this case, if I want to update one department name to another name then I need to update all the entries that working in this department. So, it is difficult to update.
3. Another problem is Data Inconsistency. Without Normalization, we have to repeat the department name and each and every entry. Suppose that I have a specific user entering information into the EMP table. He enters the EMPNO as 7782, DEPTNO as 10, ENAME as CLERK, and DNAME as accounts. But he enters DNAME in small letters. He enters another employee’s information working in the same department. But this time he enters the DNAME name in capital letters i.e. ACCOUNTS. So, I have data inconsistency. Because the Oracle database is case-sensitive. That means accounts and ACCOUNTS are two different data.

With Normalization we can overcome the above problems. With Normalization, we only store the Department Names in the DEPT table.

Note: In some cases, we need DE-Normalization for reporting purposes and for better performance.

Hosting Oracle 12c/18c:

We have two options to hast oracle 12c/18c. They are as follows:

1. On-Premises: The software installed in your own environment (i.e. on your own server).
2. Cloud: Oracle will host the database and all its components in the cloud.

Note: Now, some of the companies are using the cloud, and most of the companies using On-Premises to host the Oracle software. But believe me, in the near future most companies will move to the cloud.

History of Oracle Database:

• In 1977, Larry Ellison, Bob Miner, and Ed Oates started the consultancy Software Development Laboratories, which became Relational Software Inc (RSI) They have created the commercial version but it is not in sale till 1979.
• In 1979 they have launched the commercial version as version 2.
• In 1982 they have changed the company name from RSI to Oracle Systems Corporation.
• In 1983, the oracle version 3 was developed, the first relational database to run on mainframes, minicomputers, and PCs. The database was written in C, enabling the database to be ported to multiple platforms.
• In 1984 Oracle released version 4 which is an improved version of release 3 which supports Multi-version read consistency.
• In 1985 Oracle released version 4 which supports client/server computing and distributed database systems.
• In 1988 Version 6 brought the feature enhancements to disk I/O, row locking, scalability, backup, and recovery. It also released the first version of PL/SQL Programming language.
• The company took four years to launch their next version i.e. Oracle version 7 which was released in 1992. It also brought the most awaited features named stored procedures and triggers and updated the security by creating roles.
• In 1977 Oracle 8 was released as the object-relational database. This version also supports multiple data types. It also supports the partitioning of large tables.
• In 1999 Oracle released its updated version of 8 as 8i which is the first database to support internet protocols. It also supports web technologies such as Java and HTTP. (i indicates internet).
• In 2001 Oracle released 91 which introduced RAC which allows multiple instances to access the database. It also supports native XML.
• In 2003 introduced 10g that supports grid computing which coordinates a greater number of servers and storage to act as a single large computer. Letter g indicates grid computing. Grid infrastructure, Oracle ASM, and Flashback Database are some other features.
• In 2007 Oracle released the 11g version which supports Active Data Guard, Secure Files, Exadata, and many new features. Edition-Based Redefinition, Data Redaction, Hybrid Columnar Compression, Cluster File System, Golden Gate Replication, Database Appliance In 2013: Oracle released 12c. It is designed for cloud computing where the databases are hosted in the cloud, Exadata Cloud Service, Cloud at Customer.
• In 2013, Oracle released 12c which is designed to support the Cloud, Multitenant architecture
• In 2018: Oracle released 18c which contains features like Polymorphic Table Functions, Active Directory Integration, Transparent Application Continuity, Approximate Top-N Query Processing, PDB Snapshot Carousel, Online Merging of Partitions, and Sub partitions.
• In 2019 Oracle released its 19c and its features are Active Data Guard DML Redirection, Automatic Index Creation, Real-Time Statistics Maintenance, SQL Queries on Object Stores, In-Memory for IoT Data Streams, Hybrid Partitioned Tables, Automatic SQL Plan Management, SQL Quarantine, Zero-Downtime Grid Infrastructure Patching, Finer-Granularity Supplemental Logging, Automated PCB Relocation.

For more detailed information about Oracle Database History, please visit the below link.

https://en.wikipedia.org/wiki/Oracle_Database

Editions of Oracle Database:

The important point that you need to remember is the Oracle database may be very expensive or maybe very cheap (even free) depending on what we are using or buying. Both the 12c and 18c database versions support the same editions. Below are the Different Oracle Database Editions.

Oracle Database Personal Edition (PE):

Pros:

1. Single-user development
2. Includes all of the components that are included with Enterprise Edition

Cons:

1. No RAC
2. No Oracle Management Packs

Oracle Database Standard Edition 2 (SE2):

Pros:

1. Includes all the features necessary to develop workgroup and Web applications.
2. Supports RAC

Cons:

1. No Parallel Execution
2. No data Guard
3. No Management Pack
4. No Cloud Control
5. Only one pluggable database is allowed.

Oracle Database Enterprise Edition (EE):

This is the most powerful edition in all the editions of the database. This is widely used across most companies. This edition is very expensive.

Features: Oracle Database Enterprise Edition provides performance, availability, scalability, and security for developing applications such as high-volume online transaction processing (OLTP) applications, query-intensive data warehouses, and demanding Internet applications. Oracle Database Enterprise Edition contains all of the components of Oracle Database.

Oracle Database Express Edition (XE):

Features: Free but limited, use 1 CPU, 1 GB memory

Note: Before SE2 there are SE and SE1 editions. Both of the editions don’t support RAC.

These are the major editions of databases that are widely used. Apart from these database editions below is the source to get all the editions of the database. The document is provided by Oracle. It defines the features that are supported and not supported for a particular edition.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-AB354617-6614-487E-A022-7FC9A5A08472

In the next article, I am going to discuss Oracle Database vs Oracle Database Instance in detail. Here, in this article, I try to give a brief introduction to Oracle Database and I hope you enjoy this Introduction to Oracle Database article.