Logical Structure of the database

As mentioned earlier, the database server consists of two parts: instance and database.

Where the database is used to store data, and the instance is used to access the data in the database.
The instance includes a set of memory structures and background processes, and the structure of the database needs to be understood from two aspects of logical structure and physical structure .
The logical structure of a database refers to the logical organization of data, which is the mechanism used to manage data within Oracle.
The physical structure of a database is a structure perceived from the user's perspective, and a mechanism for storing and managing data in the operating system.
Logically, a database contains several table spaces, and different types of data are stored in different table spaces, such as system data, user data, temporary data, and rollback data, respectively, in separate table spaces.
A table space contains several segments, and data in the same tablespace can be further stored in different segments depending on the type, such as data segments, index segments, temporary segments, and so on.

A segment can also contain several extents, which are the basic unit for Oracle to allocate storage space and reclaim storage space.
Zones are composed of contiguous blocks of data that are the basic unit of Oracle's read-write database.

table spaces, segments, extents, and data blocks make up the logical structure of the database .
Logical structure refers to the organization of data, and from the physical form, the data is stored in the form of data files on disk.
In the operating system, the user can really feel that the database exists in the data file.
The data file is stored on disk and requires a number of operating system blocks.

Logical structure and physical structure are not irrelevant, but closely related.
The data in the tablespace is stored in a data file, so a tablespace corresponds to one or more data files.
Data blocks are the basic unit of data stored in Oracle, a block of data that corresponds to several operating system blocks, the data is ultimately stored in the operating system block, and Oracle's access to the block of data is ultimately translated into Access to the operating system block.
The relationship between the logical structure of the database and the physical structure is as follows:

Table Space

Table space is the logical organization of data in a database, and a database is logically composed of multiple table spaces.

Table spaces are used to organize different types of data together, just as employees in a company are organized according to different departments.
The data in a tablespace is physically stored in a data file, and one table space corresponds to one or more data files, just as a department corresponds to several offices.
When storage space in a tablespace is tense, you can add data files to a table space , and a data file can belong to only one table space .
If the tablespace contains only one data file, all data is stored in the data file.

If the tablespace corresponds to more than one data file, the data is divided into parts, which are stored in these data files.
The relationship between the tablespace and the data file.

The benefits of introducing table spaces into a database include the following:
• Separating system data from user data helps protect important data.

• You can limit the user's use of disk storage space.
• Separate temporary data from user data to reduce fragmentation of user data stores and improve database performance.
• Ability to store different types of data on separate disks to reduce disk read-write conflicts.

You can store frequently accessed data on relatively fast disks, improving the performance of your database as a whole.
• Individual table spaces can be individually set to online or offline, allowing a single table space to be taken offline and backed up or restored in the event that the database is functioning properly.
There are five types of table spaces in a database, the system tablespace, the Sysaux table space, the undo tablespace, the temporary tablespace, and the normal table space.
The first four table spaces are essential, and you need to create them when you create the database, and the normal tablespace is created as needed.

1. system table Space

The system tablespace is a required table space in the database.

When you create a database, the system table space is automatically created.
In the system table space, information about the database is stored, such as the data dictionary, the definition of the database object, the code of the PL/SQL Store program, the system rollback segment, and so on.

2. Sysaux table Space

The Sysaux table space is also a required tablespace in the database, which is created automatically when the database is created .
The Sysaux table space is a secondary tablespace to the system table space, and data that was previously stored in the system table space is now stored in the Sysaux table space, reducing the burden on the system table space.
In addition, many of the data that previously required a separate tablespace can now be stored in the Sysaux table space, reducing the number of table spaces that need to be maintained.

3. Undo Table Space

The Undo table space is used to store rollback data .

Rollback data is data that is modified by transactions, for example, assuming that the user executes the statement "DELETE from EMP WHERE empno=7902".
The data accessed by the Delete command is the rollback data.
This row of data is stored in the Undo table space when the transaction has not yet been committed.

Assuming another user executes the statement "SELECT * from emp WHERE empno=7902", then he will get the row of data returned from the Undo table space .
If the transaction is rolled back, the data in the undo Tablespace is written back to the original storage space as if the original DML operation was not executed.
This shows that theundo table space is designed to roll back the transaction .

In previous versions of the database, rollback data could only be stored in a rollback segment.
The rollback field is in a table space, or in some particular table space.

The management of rollback segments is complex and requires the database administrator to manually execute cumbersome commands.
The current version of the database uses the Undo table space to manage rollback data.
Only rollback segments can be stored in the Undo table space, not other types of segments, such as data segments, index segments, and so on.
The advantage of using the undo tablespace is that the rollback data is automatically managed , reducing the burden on the database administrator.

Temporary table space

Temporary table spaces are used to hold temporary data that is generated when a user accesses a database.

For example, when the user executes the statement "SELECT * from emp ORDER by Empno;" , the data in the table is sorted and the sort results are produced.
The sort operation is typically done in the PGA's sort area.

If the sorting area is not large enough to hold the data, the temporary table space is used.
Only temporary segments can be created in a temporary tablespace.

Temporary segments are not permanent, and when the user first performs a sort operation in the database, the temporary segment is automatically generated, and the space of the temporary segment is freed when the database is closed.
It is for this reason that the temporary tablespace does not allow the creation of persistent database objects , such as tables, indexes, and so on.

You can create more than one temporary table space in a database.

Without a temporary tablespace, users may need to use the system table space to store temporary data when performing operations such as sorting.
If temporary data is stored frequently in the system table space, a large amount of storage fragmentation is generated, reducing the performance of the database.
When creating a user using the Create User command, you can specify a temporary tablespace for that user through the temporary tablespace clause.
After the user has created it, you can also specify a temporary tablespace for it by using the ALTER USER command.
The temporary data that is generated when the user accesses the database is stored in the specified temporary table space.

Normal table Space

The normal tablespace is a table space that the user really cares about and can create more than one normal table space in the database.

The normal table space is used to hold the user's data.

Paragraph

A tablespace separates data by type logically , such as organizing user data and system data in a single table space.
in the same table space, there may be different types of database objects , such as tables, indexes.

Oracle organizes the data in different database objects together as segments.
A table space contains multiple segments, but a segment can belong to only one table space.
When a user creates a database object in the database, a segment is automatically created in the Tablespace to store the object's data.
For example, by default, a table corresponds to a table segment and an index segment for one index.

The allocation of storage space in a segment is in the area.
Contains a number of extents in a segment.

You need to specify the initial number of extents when assigning segments.
As the data in the segment increases, the data server expands the segment to allocate the required extents for the segment, and when the data in the segment is deleted, the free area can be reclaimed.
There are four main types of segments that can be created in a table space, which are data segments, index segments, temporary segments, and rollback segments.
Store different database objects in each segment.
Data segments are used to hold data in a table, and by default one table corresponds to a table segment.

you can store data in only one table in a table segment .
When a user creates a table in a tablespace, the database server automatically creates a segment in the table space for the table with the same name as the table.
The index segment is used to store the data in the index, and when the user creates an index for a table, the data server automatically creates an index segment for the index that is the same as the index, and that the index segments are one by one corresponding to each other.
When you create a PRIMARY KEY constraint or a uniqueness constraint on a table, the corresponding index segment is also generated.
Temporary segments are used to hold temporary data, and when users perform operations such as sorting, a large amount of temporary data is generated that is stored in temporary segments.
Of course, temporary data is preferentially stored in the PGA's sort area, which can increase the speed of sorting .
If the sorting area is not large enough to hold the temporary data, the temporary segment is used.

Temporary segments are not required, and if no dedicated temporary segments are created, the user's sort operation uses the temporary segments in the system table space.
Because the system table space stores important systems data, the frequent use of systematic tablespace yields a lot of fragmentation, which reduces the performance of the database, so Oracle recommends creating a dedicated temporary segment as much as possible and storing it in a dedicated temporary table space.

The rollback segment is used to store rollback data.

When a user executes a DML statement, the database server stores the modified data in the table segment, and the pre-modified data is stored in the rollback segment as the rollback data.
When the user rolls back the transaction, the database server re-writes the data in the rollback segment to the table segment, and the changes made by the firm are canceled .
When a user commits a transaction, the data in the rollback segment becomes invalid, and the user cannot roll back the transaction .

Multiple rollback segments can be created in the database.

When a user performs a DML operation, the database server automatically assigns a rollback segment to the current transaction, and the user can also specify a rollback segment from the command.
When you create a database, a system rollback segment is automatically created in the systems table space, and the systematic rollback segment is used to maintain internal oracle transactions.
The database administrator can create additional rollback segments from the command.
It is important to note that Oracle 11g provides two ways to manage rollback data, one of which is manual management, which side using rollback segments to maintain transactions.
Another side note, called Automatic Management, uses dedicated undo tablespace management to roll back data.
Because the management of the rollback segment is too complex, Oracle recommends that you use automatic management, which may be removed from manual management in later versions of Oracle.

Area

Area is the smallest unit of Oracle's allocated storage space, one segment consists of several contiguous blocks of data, and the size of the extents is an integer multiple of the block size .
When you create a database object, the database server allocates several extents to the object to store the object's data.
Database objects occupy at least one zone, and as data increases, the database server will continuously allocate the required extents for that object, which may or may not be equal in size .

Data block

The data block is the smallest unit of storage in Oracle and the smallest logical unit of data read and written by the database server.

When a database server allocates space for a segment or reclaims storage space, it is in a zone, and when it reads and writes data, it is in chunks.
The data in the database is finally stored on the hard disk, so the data block must be closely related to the block in the operating system.
A block of data consists of a number of operating system blocks, which are an integer multiple of the operating system block.

The database server reads and writes data in chunks, which ultimately translates to read and write to the operating system block.
In Oracle 11g, there are two forms of data blocks, one is a standard block and the other is a non-standard block.

The size of the standard block is specified by the initialization parameter db_block_size, and all standard blocks are of the same size.

The size of non-standard blocks can be in a variety of situations, such as 2KB, 4KB, 8KB, 16KB, 32KB (but not the same size as the standard block), and so on.
A series of initialization parameter db_nk_cache_size can be used in the database, where n is 2, 4, 8, 16, 32, and so on.
These initialization parameter families are used to specify the database cache size for non-standard blocks.
If a non-standard block is defined in the database, the appropriate database cache must be defined for it in the SGA.
Regardless of the data block, its size cannot be modified after the database is created.
When the database server reads and writes data, the data in the data block is first called into the database cache of the SGA, and buffers must be defined for each size of data block in the cache, and the buffer size is the same as the data block.
When a user accesses a database, the contents of the data block are read and written to the same size buffer.

Logical Structure of the database