Oracle logical database structure Overview (tablespace, segments, data expansion, and data blocks)

 

Oracle logical database structure Overview

Database space allocation units:Data Block, extent, and segment).

 

Specific concepts can be seen: http://hi.baidu.com/feixianxxx/blog/item/8d5429028d05dc091d95838a.html

 

I want to write a fewNotes:

 

1. N operating system blocks. Is continuous. Because data expansion is a continuous set of data blocks, it is also continuous and is located in a data file.The expansion of segments is based on data expansion. However, because data expansion is distributed as needed, segments cannot be continuous. It is not necessarily in a data file, but in a tablespace..

2. Each time Oracle acquires data, it always accesses an integer (Oracle) data block, instead of accessing data according to the size of the operating system block. Therefore, reasonable data block size control has a great impact on disk I/O.

 

3. Pay attention to row link and row migration:When a row of data is inserted, a data block cannot be accommodated. In this case, Oracle stores this row of data in a chain within the segment. Row chaining usually occurs when a large amount of data is inserted ). The latter originally stores data rows in a data block. Because of the update operation, the length increases, and the available space of the data block cannot accommodate the increasing data rows. In this case, Oracle migrates this row of data (migrate) to a new data block. Oracle saves a pointer to the new data block in the original location of the migrated data row. The rowid of the migrated data row remains unchanged.

 

4.In the newly allocated data block)The space used for insert data is equal to the total capacity of the data block minus the data block header (Block overhead) and then the reserved space (pctfree ). The existing data in the update data block can use all available spaces in the data block.

 

5. For tablespaces that use local management, the allocated data extended (extent) capacity can be user-defined fixed values or variable values automatically determined by the system.For uniform data expansion, you can set the data expansion capacity or use the default size (1 MB ). You must ensure that each data expansion capacity can contain at least five data blocks ). The temporary tablespace (temporary tablespace) for local management (locally managed) can only be used when allocating data extensions. For autoallocate data expansion, Oracle determines the optimal capacity for new data expansion. The minimum capacity is 64 KB. For permanent tablespace, the preceding parameters are default values.

 

6. How to allocate data extensions:First, select a data file that belongs to the tablespace, and then search for the bitmap of the data file to find the continuous data block ). If this data block does not have enough continuous available space, Oracle queries other data files.

 

7. as long as the user has not removed (drop) or cleared (truncate) Table (nonclustered table) or index, any data block in the corresponding data segment) will not be recycled.Of course, it can be reused.

Why did I delete the data in a table but its space is not released? See Oracle's high water level line http://tylgl.javaeye.com/blog/319585

 

8. operations involving temporary segments:

Create Index

Select... order

Select distinct...

Select... group

Select... union

Select... intersect

Select... minus

 

9.Oracle assigns temporary segments for queries and temporary tables.

Allocate temporary segments for queries: Oracle can create temporary segments (temporary segment) for users in the user's temporary tablespace (temporary tablespace ). Oracle is responsible for removing (drop) Temporary segments when the statement ends. assign temporary segments to temporary tables and temporary indexes: when the first insert operation is performed, Oracle assigns temporary segments to the temporary tables and their indexes to create the root page of the index, create all lob segments at the same time ). When a transaction or session ends, Oracle is responsible for removing the segment used by the temporary table that belongs to the transaction or session.

 

10. segment management involves an automatic revocation management.

Purpose:

Rollback an active transaction

Recover a closed transaction

Read consistency)

A logical error occurred while restoring the statement)

We recommend that you use the automatic undo Management (automatic undo Management) mode in Oracle, but there is a problem to note: a problematic transaction (ill-behaved transaction) it may consume a large amount of Undo space, or even paralyze the entire system. Solution: In resource manager, you can set the undo_pool parameter to control the management of large transactions in a more direct way. The management of the retention period of the information to be deleted is of great significance to the flashback operation.

 

 

Let's take a look.TablespaceIt is closely linked to the physical files of the database and is also the most important logical structure.

 

 

A tablespace is a container that contains segments. Each segment belongs to only one tablespace. A tablespace may have multiple segments.

The tablespace itself can have one or more related data files. A partition of a given segment in a tablespace is completely contained in a data file. However, segments can have segments from multiple different data files.

 

 

Databases, tablespaces, and datafiles are closely related, but they have the following important differences:

1. An Oracle database is composed of one or more logical storage units called tablespaces. These tablespaces are used together to store database data.

2. Each tablespace in the Oracle database is composed of one or more physical files called data files, which are managed by the operating system of oracle.

3. The data in the database is actually stored in the data files that constitute the tablespace.

 

For more information about tablespaces, see: http://hi.baidu.com/feixianxxx/blog/item/8d5429028d05dc091d95838a.html

 

The following table space points are provided:

1. bigfile tablespace can be used only for the locally managed (locally managed) tablespace (tablespace) of automatic segment space management (automatic segment space management ).

However, there are two exceptions: Undo tablespace and temporary tablespace managed locally, even if the segment is manually managed ), you can also use large file tablespaces.

 

2. advantages of using large file tablespaces:

A. significantly enhanced the storage capability of Oracle databases. When large file tablespaces are used, the maximum number of tablespaces in the database is 1024 times that of small file tablespaces. When an Oracle database uses large file tablespaces, when the maximum data block capacity is used (32 K), the total capacity can reach 8 EB.

B. Using large file tablespaces in super-large databases reduces the number of data files and simplifies the management of data files.

C. Database Management is simplified because data files are transparent to users.

 

3. It is not recommended to use large file tablespaces on platforms that do not support large files. This will limit the capacity of tablespaces.If you use large file tablespaces to replace traditional tablespaces, the performance of the database open, checkpoints, and dbwr processes will be improved. However, increasing the data file capacity may increase the backup and recovery time.

 

4. If the system tablespace (tablespace) in the database is locally managed (locally managed), you cannot create a dictionary managed tablespace in the database.

5. In the automatic undo management mode, each Oracle instance has only one undo tablespace.

6. You can use multiple tablespaces to complete the following tasks:

Control disk space allocation in the database

Set the space usage quota for database users (quota)

Control Data availability by placing a tablespace in the online/offline status)

Back up and restore a part of the database

Enables data storage across multiple devices to improve system performance

 

 

7. Local tablespace management has the following advantages over data dictionary-managed tablespaces:

A. It is easy to find continuous available space [index data blocks here], avoiding coalesce free extents ).

B. Avoid recursive space management operations.

 

8. The user can only bring a tablespace online to the database where it is created, because only the system tablespace of the database stores the necessary data dictionary information related to the tablespace.An offline table space cannot be opened or edited by tools other than oracle. Therefore, the offline tablespace cannot be moved to other databases.

9. One SQL statement can use multiple temporary tablespaces (temporary tablespace) for sorting at the same time.