Oracle tablespace (tablespaces)

 

We knowOarcleWhat databases actually store data is data files (Data Files),OarcleTablespace (TablespacesIs actually a logical concept. It does not exist physically.Data FilesThe table space is twisted together.

Tablespace attributes:

A database can contain multiple tablespaces. One tablespace can belong to only one database.

A tablespace contains multiple data files. A single data file can only belong to one tablespace.

Table space can be divided into finer logical storage units

 

OracleDatabase storage structure:

 

 

logically, a database ( database ) The following table spaces can be divided into multiple table spaces ( tablespace ); a tablespace can be divided into multiple segments ( segment ); A data table occupies one segment ( segment ), an index also occupies a segment ( segment ). One segment ( segment ) by Multiple Intervals ( extent , then, an interval is composed of a group of continuous data blocks ( data block . This continuous data block is logically continuous and may be dispersed on the physical disk.

From a physical point of view, a tablespace consists of multiple data files, which are actually files on the disk. These files areOracleDatabase Operating SystemBlock.

 

 

Segment (segment)Segment refers to the generic name that occupies the data file space or a set of space used by database objects. The segment can include table segment, index segment, rollback segment, temporary segment, and high-speed cache segment.

 

Extent(Range):Any continuous block allocated to an object (such as a table) is called an interval. An interval is also called an extension because when it runs out of the allocated interval, when a new record is inserted, it is necessary to allocate a new interval (that is, to expand some blocks). Once the interval is assigned to an object (table, index, and cluster ), this interval cannot be assigned to other objects.

View the tablespace:

SQL> select * from V $ tablespace; TS # name includ bigfil flashb encryp ---------- nickname ---------- ------ 0 system Yes No Yes 1 undotbs1 Yes No Yes 2 sysaux Yes No Yes 4 Users Yes No Yes 3 temp no yes
 

View the data files in each tablespace:

SQL> DESC dba_data_files; Name null? Type verification -------- ---------------------------- file_name varchar2 (513) file_id number tablespace_name varchar2 (30) bytes number blocks number status varchar2 (9) relative_fno number autoextensible varchar2 (3) maxbytes number maxblocks number increment_by number user_bytes number user_blocks number online_status varchar2 (7)

View detailed data files:

 
SQL> select file_name, tablespace_name from dba_data_files; file_name tablespace_name alias/ora10/product/oradata/ora10/users01.dbf users/ora10/product/oradata/ora10/sysaux/ora10/product/oradata/ora10/export undotbs1/ora10/product /oradata/ora10/system01.dbf System
 

Create a tablespace:

 
SQL>Create tablespace Paul datafile '/ora10/product/oradata/ora10/paul01.dbf' size 20 m;Tablespace created.
 

View the created tablespace:

[Ora10 @ localhost ora10] $ PWD/ora10/product/oradata/ora10 [ora10 @ localhost ora10] $ ls control01.ctl control03.ctl redo01.log redo03.log system01.dbf implements control02.ctlPaul01.dbfRedo02.log sysaux01.dbf temp01.dbf users01.dbf
 

Based on the interval (Extents)Management is divided into two types

 

Manage tablespaces in a dictionary(Dictionary-managed tablespaces)

In a tablespace, some intervals are occupied, and some are not occupied. The data is stored in the data dictionary. When you allocate or release the tablespace, the relevant tables in the data file are modified.

 

Local tablespace Management(Locally managed tablespace)

Local tablespace management does not store tablespaces in the data dictionary. tablespaces are managed by free zones. Use bitmap to manage zones freely. A range is a bit. If this bit is1Indicates that it has been occupied,0Indicates that it is not in use.

The dictionary management space indicates "centralized management", and the local management tablespace indicates "province, city, and autonomous region ".DatabasesIndicates China,TablespacesIndicates a province or municipality. The management of dictionaries is centrally distributed. Local management means that there is a high degree of autonomy, and the distribution of various resources does not need to be reported to the Central Government.

 

Undo tablespace

UndoType tablespace. When you modify a table or a record, it will save the information before modification, so as to ensure data rollback.UndoOnly includeUndoType object. It cannot contain any other objects and is only suitable for data file and interval management.

CreateUndoType tablespace:

SQL>Create undo tablespace undo1 datafile '/ora10/product/oradata/ora10/paul01.dbf' size 20 m;

 

Temporary tablespaces

A temporary tablespace is equivalent to a temporary garbage dump. Used for sorting operations. For example, if you want to query a large amount of data, but you cannot store such a large amount of data in the memory, a temporary tablespace will be created on the disk to store the data.OracleThe temporary tablespace is used for sorting and intermediate results are stored.

A global temporary tablespace can be shared by multiple users who need it. However, it can only store temporary data and cannot contain any permanent objects. We recommend that you use local management to create the tablespace.

Create temporary tablespace:

SQL>Create temporary tablespace temp datafile '/ora10/product/oradata/ora10/paul01.dbf' size 20 m extent management local uniform size 4 m;

Delete tablespace:

To delete a tablespace, run the drop tablespace 'tablespace name' command. However, you must note the following three options:

Including contents: deletes segments in a tablespace;

Including contents and datafiles: deletes segments and datafiles;

Cascade constraints: delete all integrity constraints related to the space.

Example:

DropTablespace FESCO 'table space name' ContentsAndDatafilesCascadeConstraints;