Oracle tablespace Design

Oracle tablespace Design

Tablespace design principles:

1. assign different tablespaces to tables and indexes.

2. assign different tablespaces to the formal and historical tables to improve data security.

3. assign a separate tablespace for the big data table.

4. Separate the read-only or read-dominated tables with tablespace.

5. Split tables with high-frequency updates into one group and allocate tablespace separately.

6. the extent size of the table (or index) stored in the same tablespace is better than that of a multiple, which facilitates reuse of space and reduction of fragments.

7. allocate different tablespaces for different types of data. This improves the database input and output performance and facilitates data backup and recovery management. Because our database administrator can back up data by tablespace when backing up or recovering data. For example, when designing a large distribution system background database, we can create tablespaces by province. Data Files related to Zhejiang Province are stored in the tablespace of Zhejiang Province. Business records in Beijing are recorded in the tablespace of Beijing. In this way, when the business data in Zhejiang Province is incorrect, the tablespace in Zhejiang province can be restored directly. Obviously, in this design, when data in a tablespace is wrong and needs to be restored, the impact on other tablespaces can be avoided.

 

 

Table space design considerations:

1. make proper use of the tablespace size. Different tablespace sizes are allocated based on the design intent and actual conditions.

2. make proper use of server space. You can configure different storage locations for different tablespaces.

 

 

Design:

1. Because the system configuration table mainly reads data, the table space is allocated separately: ts_sys_data.

2. assign a separate tablespace for the system configuration table index: ts_sys_index.

3. assign a separate table blank for the user operation table: ts_main_data. the user's default tablespace.

4. assign a separate tablespace for the index operation table: ts_main_index.

5. assign a separate tablespace for the large number table operated by the user: ts_main_big_data.

6. assign a separate tablespace for the user's operation history table: ts_his_data.

7. assign a separate tablespace for the user to operate the large data size History Table: ts_his_big_data.

8. assign a separate tablespace to the index of the historical table operated by the user: ts_his_index.

9. Create a temporary tablespace: ts_temp.

 

 

SQL statements for creating tablespaces:

Method 1,

Create tablespace ts_main_data

Datafile 'd: \ oracle \ oradata \ ts_main_data 'size 500 m

Extent management localautoallocate

/

Method 2,

Createtablespace ts_his_data

Datafile 'd: \ oracle \ oradata \ ts_his_data 'size 1000 m

Extent management local uniform size 2 m

/

Method 3,

Createtablespace project_main_data

Datafile

'E: \ oracle_dbdata \ project \ project_dat01 'size1990m reuse autoextend off,

'E: \ oracle_dbdata \ project \ project_dat02 'size1990m reuse autoextend off,

'E: \ oracle_dbdata \ project \ project_dat03 'size1990m reuse autoextend off,

'E: \ oracle_dbdata \ project \ project_dat04 'size1990m reuse autoextend off

Logging

Online

Permanent

Extent management local uniform size 2 m

/

 

Extent indicates the range. In Oracle databases, extentmanagement provides two methods: extent management local (local management); extentmanagement Dictionary (data dictionary management); Local by default. There are two ways to increase the size of each type:

Uniform: The default value is 1 MB. It is the default value in the temp tablespace, but cannot be applied in the Undo tablespace.

The local tablespace management greatly improves the management efficiency and database performance compared with the dictionary tablespace management. Its advantages are as follows:

1. Reduced recursive space management

The local tablespace is managed and allocated by yourself, rather than the tablespace managed by the system like a dictionary, the local tablespace maintains a bitmap in each data file of the tablespace to track the remaining space and usage of the block in the file. And make updates in a timely manner. This update only modifies the table space quota and does not perform any update operations on other data dictionary tables. Therefore, no rollback information is generated, which greatly reduces space management and improves management efficiency. At the same time, the local tablespace can be managed in a uniform size allocation mode (Uniform), which greatly reduces space management and improves database performance.

2. the system automatically manages the extents size or uses the unified extents size.

The local tablespace can be automatically allocated (autoallocate) or uniformly distributed (Uniform). The system determines the size of extents automatically, the unified size allocation (Uniform) is the extents size specified by the user. Both methods improve the space management efficiency.

3. Reduced competition between data dictionaries
Because the local tablespace is managed by maintaining a bitmap of each data file to track and update the space of the blocks in the file, this update only modifies the quota of the tablespace, other data dictionary tables are not involved, which greatly reduces competition between data dictionary tables and improves database performance.

4. No rollback information is generated.

In addition to updating the table space quota, the local tablespace space management does not modify any other data dictionary tables. Therefore, no rollback information is generated, which greatly improves the running speed of the database.

5. No need to merge adjacent spaces

Because the extents space management of local tablespace management will automatically track the adjacent residual space and be automatically managed by the system, so there is no need to merge adjacent residual space. At the same time, all extents for local tablespace management can have the same size, which reduces space fragmentation.

6. Reduced Space fragments

7. Better management of temporary tablespace

Autoallocate:
You can convert a tablespace from dictionary extent management to local extentmanagement
And back with the Oracle-supplied PL/SQL package dbms_space_admin. The system
Tablespace and any temporary tablespaces, however, cannot be converted fromlocal to
Older style dictionary managem
The two extent management methods can be converted to each other, using PL/SQL dbms_space_admin
However, system tablespace and temporary tablespace cannot be converted from local management to dictionary management.