Detailed analysis of Oracle tablespace in seven steps

In Oracle, I like Oracle tablespace very much. In my work, I also like to summarize the experience and lessons on Oracle tablespace. Let's talk about this in detail. Oracle tablespace is the basic method for data management. All user objects must be stored in the tablespace, that is, the user has the right to use the space before creating user objects. Otherwise, you do not need to create objects because you want to create objects, such as tables and indexes, and there is no place to store them. Oracle will prompt that there is no storage quota. Therefore, before creating an object, you must first allocate a bucket. To allocate storage space, create a tablespace:

An example of creating an Oracle tablespace is as follows:

 
 

  
  
CREATE TABLESPACE  "SAMPLE"  
  
  
LOGGING  
  
  
DATAFILE 
  
  
'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M  
  
  
EXTENT
  
  
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT
  
  
AUTO   
 
 

The preceding statement consists of the following parts:

1. create tablespace "SAMPLE" to CREATE a TABLESPACE named "SAMPLE. To name a table space, follow the naming rules of Oracle.

There are three types of tablespaces that can be created in Oracle:
(1) TEMPORARY: temporary tablespace for storing TEMPORARY data. The syntax for creating a temporary tablespace is as follows: create temporary tablespace "SAMPLE "......
(2) UNDO: Restore the tablespace. Used to store redo log files. The syntax for creating a restored tablespace is as follows: create undotablespace "SAMPLE "......
(3) User TABLESPACE: The most important and used to store user data table spaces can be directly written as: create tablespace "SAMPLE" TEMPORARY and undo tablespace are special tablespaces managed by Oracle. Only used to store system-related data.

Second: LOGGING
There are two options: NOLOGGING and LOGGING. NOLOGGING: when creating a tablespace, no redo logs are created. LOGGING is the opposite of NOLOGGING, that is, the redo log is generated when the tablespace is created. When using NOLOGGING, the advantage is that logs are not generated during creation, so that the tablespace can be created quickly but cannot be logged. After data is lost, it cannot be recovered. However, when creating a tablespace, there is no data. Generally, after creating a tablespace and importing data, you need to back up the data. Therefore, you do not need to create a tablespace. Therefore, when creating a tablespace, select NOLOGGING to accelerate the creation of the tablespace.

Third, DATAFILE is used to specify the location and size of the data file.
For example, DATAFILE 'd: \ Oracle \ ORADATA \ ORA92 \ LUNTAN. Ora 'size 5 MB indicates that the file is stored in 'd: \ Oracle \ ORADATA \ ORA92 \ LUNTAN. Ora, the file size is 5 MB. If multiple files exist, separate them with commas:

 
 

  
  
DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M,  
  
  
'D:\ORACLE\ORADATA\ORA92\dd.ora' SIZE 5M  
 
 

However, the size of each file must be specified. The Unit is subject to the specified unit, for example, 5 M or 500 K. Specific files can be stored on different media, such as disk arrays, based on different needs to reduce IO competition. When a file name is specified, it must be an absolute address and cannot be a relative address.

Fourth: extentmanagement local Storage Area Management Method
Before Oracle 8i, there are two options: one is to manage the DICTIONARY and the other is LOCAL, starting from 9I, only Local Management is supported. Because LOCAL management has many advantages. DICTIONARY management: each storage unit in the data file is used as a record. Therefore, when performing the DM operation, A large number of Delete and Update operations on the management table will be generated. When doing a large amount of data management, it will produce a lot of DM operations, strictly affecting performance. At the same time, operations on table data for a long time will produce a lot of disk fragments, this is why disk sorting is required. LOCAL: You can manage disks in binary mode with high efficiency. At the same time, the system can automatically track and record the situation of near-idle space to avoid merging idle areas.

Fifth: SEGMENT SPACE MANAGEMENT
Disk extension management method:
◆ Segment space management: the time zone of this option is automatically determined by the system. Since Oracle can determine the optimal size of each region, the partition size is variable.
◆ Uniform segment space management: Specifies the partition size. You can also use the default value (1 MB ).

Sixth: section space management:
◆ AUTO: it can only be used in locally managed tablespaces. When you use LOCAL to manage Oracle tablespaces, after the free space in the data block increases or decreases, its new status will be reflected in the in-place diagram. Bitmap makes Oracle's management of free space more automated and provides better performance for management of free space. However, it cannot be automatically managed for tables with LOB fields.
◆ MANUAL: no longer needed, mainly for backward compatibility.

7. Specify the block size. You can specify the size of the tablespace data block.

1. Detailed analysis of Oracle tablespace in seven steps
2. Let's discuss how to create an Oracle tablespace.
3. Professional Oracle tablespace reconstruction technical guidance
4. Step 6: Understand the Oracle tablespace status
5. Oracle tablespace recovery makes you no longer worry about database errors