In this lesson, I will give you a high-level overview of some basic database concepts and the architecture of the Oracle database, so that you have an idea of the things that are going on when you connect and work with a database.
This information is not included in many SQL courses, but I think it can be helpful to you, and I want to provide value, so here it is. I hope you enjoy it!
I’m going to try to keep it simple, because you, as a developer, don’t really need to know the fine details of the architecture.
Components Involved when interacting with an Oracle Database
When you interact with an Oracle database, in general, it typically involves the work of a database server and one or more client applications.
The database server manages the database and attends the requests from clients, and that is why it is called “a server” but it doesn’t really need to be a special server computer. As you noticed in the previous section, we can install the database on a desktop or laptop workstation.
So, an Oracle database server consists of a DATABASE, which is a set of files that store the data, and at least one database INSTANCE, which is composed of the memory structures and background processes needed to manage the database.
And on the other side of the interaction, we have the client applications, which are the ones that interpret and display the data.
When a client application tries to connect to the database, the server process that receives and handles the request is called “the listener”. This is a very important process because all of the connection requests from the clients are handled by the listener.
SQL Developer, which is the program we will use to write SQL code and interact with the database, is just a client application, so when we write and run a command, it sends it to the database server and when it gets the response, it displays it to us.
Most of the times, client applications will be running on a different machine, connecting to the database over the network, but they can run in the same computer as well, as in our case.
Storage Structures
The main task of a database is storing data, and when it comes to data storage we have to think of 2 different types of storage structures: PHYSICAL and LOGICAL.
The physical structures are the operating system files that store the data and database information, and there are a lot of files that are created and used by Oracle, but the most important ones include:
- The DATA FILES, which contain the database data itself
- The CONTROL FILE, which is a file that stores metadata about the database, such as the database name, the names and paths of the data files, and some other things.
- The REDO LOG FILES, which record all changes made to data, and are mainly used for recovery purposes, in case of an instance failure, for example.
And On the logical side, the structures used to store data are:
- DATA BLOCKS, which are the smallest units of data storage.
- EXTENTS, which are made up of a number of contiguous data blocks.
- SEGMENTS, which are sets of extents allocated for a user object, such as a table, or other similar structures.
- And finally, TABLESPACES, which are the logical containers for the segments, and each tablespace contains at least one data file, so, here is where the logical and physical structures meet each other.
So, the relationship between segments, extents, and blocks is like in this image.
Here is another image that shows the relationship between the storage structures. This is the image of a tablespace:
So, data blocks form extents, and extents form segments, but as you see in the image above, a segment can have extents stored in different data files, but each segment belongs to one and only one tablespace.
And the last topic I want to cover here is “Schema Objects”.
Schema Objects
As you remember from the basic database concepts lesson, schemas are structures where users store their tables.
So, a schema is a logical container for data structures, and these data structures are called “Schema Objects”, which include tables, views, indexes and some other kinds of structures.
Each user owns a single schema, which has the same name as the user, and each object of a particular type must have a unique name within the schema. But another object of the same type and name can exist without issues in another schema. For example, I can have a products table in the sales schema, and another products table, which is completely different from the first one, in another schema.
Okay!
I think these are the most basic things about the Oracle architecture that you need to know as a developer.
I hope it’s been helpful to you, and I will see you in the next lesson, where you will learn about some Oracle peculiarities that you need to know before starting to work with your database.