Basic Oracle knowledge: tablespace Introduction

Tablespace is the logical unit of Oracle space management. Data Files in tablespace are stored in entities. tables that we are familiar with are stored in these files. Therefore, tablespace can be viewed as a data file group. In terms of management, user data and Oracle system objects are split in different tablespaces. If an Oracle database has multiple Ap systems and uses different tablespaces for cutting, management and backup can be achieved. However, tablespace is not only used for simple group classification. Oracle provides many functional parameters to set tablespace for space management and efficiency improvement.

It is worth noting that tablespace cannot span databases, data files in tablespace cannot span tablespace, and tables (segement) in data file can span data files, but not tablespace. Simply put, data in a table may be distributed in different data files in the same tablespace due to Oracle space allocation. Therefore, after a data file is created, it cannot be deleted at will, which will cause serious data damage.

System and non-system tablespace
When the database is just created, the system will create a system named system tablespace to store important user system data such as sys and system (ex: data dictionary and pre-storage program) if the preset tablespace is not specified when an oracle user is created, the user uses system tablespace as the preset tablespace. This will cause management confusion and serious performance problems, which must be paid special attention.

Tablespace type: permanent, undo, temporary
§ Permanent tablespace

Generally, permanent tablespace is created for the AP. The lifecycle of objects in the object will not disappear as the transaction or user session ends.

§

§ Undo tablespace

Undo tablespace is a special tablespace used by the system to replace the previous rollback segement mechanism, the main function is to provide the read consistency function and the rollback transaction function before the user modifies the data not commit. Because undo tablespace mainly replaces the rollback segement mechanism in the past, it cannot store segement of other types. Undo tablespace can only be local managed.

§

§ Temporary tablespace

Temporary tablespace is also a special tablespace used by the system. When users need to sort data, they sometimes use temporary tablespace. Therefore, the lifecycle of segement is very short, and the transaction or user's session may disappear. Each system must have a preset temporary tablespace (default temporary tablespace). If not, users who forget to specify which temporary tablespace to use will use system tablespace as temporary tablespace, this is terrible. The following lists the features of temporary tablespace.

1. Temporary tablespace is in nologging mode. Therefore, if the database is damaged, you do not need to restore temporary tablespace for recovery.

2. Temporary is best to use local managed tablespace

3. If the local managed mode is used, the uniform. size parameter is preferably the sort_area_size parameter, which is more efficient.

4. The default value of uniform. size is 1024 K, while that of sort_area_size is 512 K.

5. Temporary tablespace cannot use the autoallocate parameter of local management.

Extent space management of tablespace: Local managed and dictionary managed
The main difference between local managed and dictionary managed lies in the different spatial management methods. Local management allows each tablespace to use bitmaps to manage its own space, while dictionary management uses the data dictionary of system tablespace for space management. The biggest difference between the two is that local management greatly improves Oracle's space management (for example, the issue of robbing system tablespace resources when a new exten or extent... is generated. Therefore, Oracle has gone in the direction of local management since 8i. Therefore, we should try to use the local management method. Therefore, we will not introduce the dictionary management method much.

§ Local managed tablespace

1. Local managed uses bitmaps for space management.

2. Each bit in bitmaps represents a data block or a pile of adjacent data blocks (extent)

3. Starting from 10 Gb, system tablespace uses local managed by default --> Oracle recommends using local managed evidence.

4. If system tablespace is local managed, other tablespaces must be local managed.

5. If local managed or dictionary managed is not specified, local managed is used by default.

6. Using local managed can improve the efficiency because it reduces the competition for system tablespace performance.

7. local managed does not require loalescing. Because of the adjacent extent of different sizes, the bits that identify the use status of extent are also combined. Oracle can directly use these adjacent extent. It does not need to be seed before it can be used. This can also improve some efficiency.

§ Local managed extent space management: autoallocate and uniform

Autoallocate and uniform are two parameters used to set the extent size of local management. Autoallocate allows Oracle to Determine the extent size, while uniform forces the extent size in tablespace to be fixed. Generally, if you know the size of extent, you can use uniform. The advantage of using uniform is that each extent has the same size and does not cause Space fragmentation. However, if you cannot predict the size of extent, it would be better to use autoallocate, so that Oracle can decide the size of extent by itself, which can meet actual needs, thus saving space, however, this may cause Space fragmentation issues. With autoextent, the extent size used by Oracle is 64 K, 1 m, 8 m, and 64 m. According to the results in my system, 99.95% of extent uses 64 K, and only a small part uses 1 m extent, so the broken condition is not serious, using autoallocate is actually enough on my system. To learn more about the extent used in your tablespace, use the following syntax:

Select bytes, count (*) from dba_extents where tablespace_name = 'ur _ tablespace_name 'group by Bytes

§

§ Space management of segment in local management: auto and manual

In space management of segment in tablespace, you can set the parameters auto and manual. Manual manages data blocks in segment using pctused, freelists, and freelist groups which we are familiar with, while auto manages data blocks using bitmaps. If you use auto for management, you do not need to set the storage parameter settings set during create tablespace or create table in the past, because the data block management is already bitmaps, it is no longer a free list. If there is no special requirement, using auto will have better space utilization and efficiency improvement than using manual.

§

§ Create tablespace example in local managed

Create tablespace mydata
Datafile '/u1/oradata/mydb/mydata1.dbf' size 100 m autoextend on next 10 m maxsize unlimited,
'/U1/oradata/mydb/mydata2.dbf' size 100 m autoextend on next 10 m maxsize Unlimited
Extent managementlocal
Segment space managementauto;

§

§ Example of create tablespace in dictionary managed

Create tablespace mydata
Datafile '/u1/oradata/mydb/mydata1.dbf' size 100 m autoextend on next 10 m maxsize Unlimited
Extent managementdictionary
Default storage (
Initial 64 K
Next 64 K
Minextents 2
Maxextents Unlimited
Pctincrease 0 );

 

 

Source: http://dbua.javaeye.com/blog/424598