Oracle Table Space Fundamentals (1)

I. Overview of Oracle Table spaces

Tablespaces are a logical structure in an Oracle database. From the logical level, the table space contains segments, the objects in the database are organized in the form of segments, such as table segments, index segments, and so on, from a physical level, the table space is composed of files, the data in the database is actually stored in the file. Tablespace is not a unique concept in Oracle database, such as the concept of table space in DB2.

Second, the local management of the table space

1. Extended (extent) management in the Local management table space

Using the extent management local clause to display the declaration of creating a locally managed tablespace, this is a default option for new persistent tablespace (table space based on data dictionary management is obsolete). You can also specify how the extension is managed, using the autoallocate clause to indicate that the extension will be automatically allocated by the database, using the uniform clause to indicate that the extension will follow a uniform size allocation.

Using the Autoallocate option is a good choice if objects within a tablespace are a variety of requirements for extended dimensions. In addition, because the autoallocate option simplifies the management of space, it is bound to cause a certain amount of space waste. If the allocation and recycling of control space is not so important to you, then Autoallocate will be a good choice. The benefits of autoallocate exceed the losses it causes. The Uniform option clearly indicates how many extensions are allocated per request for space, and this option is a good choice if the objects in the tablespace are fixed every time a space request is made. In the actual use process, the choice should be based on the specific circumstances of the objects in the table space.

Create a partition management creation statement for the local management table space as follows:

# Auto Assign extension create tablespace lmt_tbs datafile '/u02/oracle/data/lmt_tbs.dbf ' size 50mextent management local autoallocate;# Unified distribution Extension Create tablespace uni_tbs datafile '/u02/oracle/data/uni_tbs.dbf ' size 50mextent management local uniform size 128k ;

2. Managing segments (segment) in the Local management table space

In a locally managed table space, Oracle uses two methods to manage segment space: Automatic and Manual. Manual segment space management uses a queue called "freelist" to manage the space within the segment, which is used by the pctused and pctfree two parameters to control the "into row" and "dequeue" of the space, and the difference is that the automatic segment space management uses the bitmap management method, in the automatic segment space management, You only need to set the Pctfree parameter. Automatic segment space management is a more effective way of space management. Automatic segment space management is the default for new local management-based table spaces.

The Create statement for automatic segment space management is as follows:

Create tablespace lmt_tbs datafile '/u02/oracle/data/lmt_tbs.dbf ' size 50mextent management local Autoallocatesegment Space management Auto;

Three, large file table space

A small file table space can hold up to 1024 data files, and in Oracle you can create large file table spaces so that the table space used by the Oracle database can be made up of a single large file rather than several small data files.

1. Advantages of using large file table space

• Using large file table spaces can significantly enhance the storage capabilities of your Oracle database. Because each database uses up to 64K data files, the maximum number of table spaces in the database when using large file tablespace is 1024 times times the size of the small file table space.
• Using large file table spaces in a very large database reduces the number of data files and therefore simplifies the management of data files. Due to the decrease in data files, the information about data files in the SGA and the capacity of control files are also reduced.
• Database management is simplified because the data files are transparent to the user.

2. Issues to consider when using large file table spaces

• Large file table spaces should be used in conjunction with automated storage management or other logical volume management tools that can support dynamically expanding logical volumes, or support striping (data across disk distribution) or RAID.
• You should avoid using large file table spaces on systems that do not support striping, as this will not benefit parallel execution and Rman parallel backups
• When the disk group that the tablespace is using may not have enough space, and the only way to extend the tablespace is to avoid using large file table spaces when adding data files to another disk group.
• It is not recommended to use large file tablespace on platforms that do not support large files, which limits the capacity of the tablespace (tablespace). Refer to the relevant operating system documentation for the maximum file size it supports.
• If you use large file table spaces instead of traditional tablespace, database open, checkpoints, and DBWR process performance will be improved, but increasing the data file capacity may increase the time of backup and recovery.

3. Create a statement with a large file table space

CREATE bigfile tablespace bigtbs datafile '/u02/oracle/data/bigtbs01.dbf ' SIZE  1024GB;

Oracle Table Space Fundamentals (1)