In this video, we will explain the concpets of Oracle physical and logical storage layout and specify the way Oracle uses storage interanlly using blocks, extens, segments and tablespaces
- [Instructor] Let's start our Oracle Database storage discussion by talking about the Oracle Datafiles. Lucky for us, they're also the most complex type of Oracle file we need to learn about. So be sure to have a nice cup of coffee on hand, as we are about to go deep down the rabbit hole on how Oracle manages storage. The Oracle Database Datafiles actually contain all of the user, or application, data in our database. Tables, rows, they are stored inside datafiles. Indexes, yep, inside our datafiles.
Database metadata, such as the users that have been created and their permissions, or views, and stored procedures? Yep, all database data, as well as metadata, is stored inside the datafiles. That is why the datafiles are such an important component for Oracle database storage. You lose them, you lose your database. Each Oracle database will contain several datafiles. Some are created automatically when we install Oracle, and some are created by us, or users, to store application data.
Database files have physical properties, and you can actually view them when you look inside the directory where you configured your Oracle database to store its data. However, inside these datafiles, things get a bit more complex. Oracle has its own internal hierarchy of space allocation within the datafiles. Let's take a look. Inside a datafile, the most fundamental unit of Oracle storage is called an Oracle Data Block.
Datafiles, at their lowest level, are made from sequential collections of Oracle database blocks. Oracle Data Blocks are usually eight kilobytes in size, which means that a single Oracle Data Block corresponds to 8000 bytes of physical space on your disk. While 8K is the default size for the Oracle Data Block, it can be configured. A single Oracle Data Block contains one or more rows, usually more. When we run our queries, the database retrieves Data Blocks from the data files, and returns the relevant results to the end user.
The next level of logical database storage is called an Extent. An Extent is a set of continuous Oracle Data Blocks. The reason that the term Extent exists in the Oracle Database terminology is that it is much more efficient, when allocating space inside a data file, to allocate it in relatively large chunks. So instead of allocating space one Database Block at a time, Oracle allocates complete Extents, which is just a collection of multiple Oracle Data Blocks grouped together.
The next level of logical storage above an Extent is called a Segment. Are you with me so far? A Segment is a set of Extents, one or more Extents, allocated for certain logical structures inside the database. Logical structures, yes, tables, indexes, temporary tables, these are all logical structures. A table, in our database, can be thought of as one segment of space. An index, also, one segment of space.
Every logical object in the Oracle database that requires space, and can increase in size over time, is known as a Segment internally in Oracle. You can also think about it in another way. When you create a table, you also create what is known as the table segment. That is just the way Oracle handles storage allocation internally, inside its own datafiles. It is important for you to be familiar with these terms, as Oracle can sometime alert you to issues or problem with a specific table segment, which means the storage allocated for that table.
Next in our list of logical Oracle storage concepts, we have Tablespaces, and these are a special bunch. Unlike Segments, Extents, and Blocks, which are mostly hidden from database users during normal database operations, Tablespaces are something you should be intimately familiar with. When it comes to storage of data, each Oracle database is divided into several Tablespaces, some created automatically when you create your database, and some are user-generated.
Tablespaces are logical storage groups that can be used to store logical database constructs, such as tables and indexes. When you create a table in Oracle, you specify in which Tablespace you want to store that table. You must create the Tablespaces in advance. Only then, after you have created the Tablespaces, you can use them when creating tables. Let's look at a quick example. Here we create an EMP_DATA tablespace.
This is just a name we made up. We can call it whatever we want. In addition to the tablespace name, we also specify which datafiles this tablesapce should use for storage. Yep, you cannot create a tablespace without specifying which datafiles it should use. The datafiles will be created automatically as part of the tablespace creation. We can specify one or more datafiles. Note that we actually specify the full path of the datafiles, including the directory, for the data in our example.
This means that once the tablespace has been created, it will also create two datafiles for us, named emp_data01.dbf and emp_data02.dbf. Both datafiles will be placed under the oradata folder. By the way, dbf is a common file suffix used for Oracle datafiles, it stands for database file. We also specify the size of the data files.
In this case, each file would be sized at 100 megabytes. We can even configure the datafiles to automatically extend if space runs out, how convenient. Now with our tablespaces ready, we are free to create tables inside that tablespace. When we create tables, we place them inside one tablespace. In our example, we have created an employees table, and would like for it to be stored inside the emp_data tablespace.
This means that the table segment will be stored in the datafiles of the emp_data tablespace. Basically, as users write new data to the employees tables, Oracle blocks will be written to both Datafiles which are part of the tablespace in a round robin fashion. A single tablespace can hold segment for multiple tables and indexes, and we can create multiple tablespaces inside one database, each with its own dedicated data files.
So to recap, at the lowest level of logical storage allocation, we have Oracle Blocks. These Oracle Blocks store our rows. One level above, we have Extents, which is a collection of multiple Oracle Blocks. When Oracle needs to allocate space inside datafiles, it allocates it via Extents, which is a more efficient way to allocate storage compared to individual blocks. A Segment is a collection of Extents belonging to a specific table or index in our database.
Table or index Segments are stored inside Tablespaces, which is the logical unit of storage we use to specify where a table or index should be created. Every table or index in our database can only be stored in one single tablespace. A database will have many tablespaces. We can create as many as we want. We can group tables that have affinity together inside specific tablespaces. Tablespaces themselves are stored as datafiles, which is the only physical manifestation of the Oracle database storage model in our file system.
It means that we can actually see data files inside our storage directories, but we cannot see Tablespaces, Segments, Extents, or Blocks, as these are logical storage constructs used internally by Oracle.
After completing this course, you'll have fundamentals required for installation, configuration, and administration of an Oracle 12c database.
- Database instance and storage
- Instance memory pools
- Instance background processes
- Client connections
- Database storage file types
- Control files and backup files
- Multitenant databases
- Starting and stopping the database
- Installing Oracle 12c software
- Using the developer tools
- Database management