Oracle Table Space

We know that the Oarcle database really holds the data file (data files), the oarcle tablespace (tablespaces) is actually a logical concept, and he does not physically exist, so a set of data files will be a table space to twist together.

Table Space Properties:

A database can contain more than one table space, and a table space can belong to a single database

A table space contains multiple data files, and one data file can belong to only one table space

Table This space can be divided into finer logical storage units

Storage structure of Oracle database:

From a logical point of view, a database can be divided into more than one table space (tablespace), and a table space can be divided into multiple segments (segment), a data table to occupy a segment (segment), and an index of one segment (segment). A segment (segment) consists of multiple intervals (extent), and an interval consists of a contiguous set of data blocks. This contiguous block of data is logically contiguous and may be dispersed on the physical disk.

So from a physical point of view, a tablespace consists of multiple data files, and the data files are real files on the disk. These files are made up of the block of Oracle database operating system.

Segment (segment) : A segment is a generic term that occupies data file space, or a collection of space used by database objects, and segments can have a table segment, an index segment, a rollback segment, a temporary segment, a cache segment, and so on.

Extent (interval): any contiguous block assigned to an object (such as a table) is called an interval, and the interval is also called an extension, because when it runs out of the allocated interval, new records must be inserted to allocate a new interval (that is, to extend some blocks), and once the interval is assigned to an object (table, index, and cluster), The interval is no longer assignable to other objects.

To view a table space:

Sql> select * from V$tablespace;    ts#        NAME       includ   bigfil   flashb    encryp-------------------------------------------------------------- --------------------------------   0        SYSTEM        Yes       NO       Yes   1        UNDOTBS1      Yes       No       Yes   2        sysaux        Yes       no       Yes      4        USERS         Yes        no       Yes   3        TEMP          No        no       YES

See what data files are in each table space:

　　sql> desc dba_data_files;    Name Null?                                          Type-----------------------------------------------------------------------------file_name                                    VARCHAR2 (513) file_id number Tablespace_name                                             VARCHAR2 (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) 

To view a detailed data file:

Sql> select File_name,tablespace_name from Dba_data_files; file_name                                            Tablespace_name--------------------------------------------------------------------------------- -------------------------------/ora10/product/oradata/ora10/users01.dbf                        users/ora10/product/oradata/ora10/ SYSAUX01.DBF                        sysaux/ora10/product/oradata/ora10/undotbs01.dbf                        UNDOTBS1   /ora10/product/oradata/ ORA10/SYSTEM01.DBF                        SYSTEM

Create a table space:

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

To view the table spaces we created:

[Email protected] ora10]$ PWD/ORA10/PRODUCT/ORADATA/ORA10 [[email protected] ora10]$ ls control01.ctl  control03.c TL  redo01.log  redo03.log    system01.dbf  undotbs01.dbf control02.ctl  paul01.dbf     redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

　

The tablespace is divided into two types based on the management of the interval (extents)

Dictionary Management table Space (dictionary-managed tablespaces)

In the table space, some intervals are occupied, some are not occupied, and the data is placed in the data dictionary. When you allocate or release the table space, the related tables in the data file are modified.

Local Management table Space (locally managed tablespace)

The Local management table space is not a table space that is stored in a data dictionary, and is managed by a free zone. Use bitmaps to manage the interval freely. An interval to a bit, if this bit is 1 indicates already occupied, 0 means not occupied.

The dictionary management space represents "centralized governance", the local Management table space represents "provinces and municipalities", a databases represents China, tablespaces represents a province or a municipality. The Unification of dictionary management is centrally allocated. While local administration represents a high degree of autonomy, the allocation of resources from various sources is not reported to the central authorities.

　

Undo tablespace

Undo type of table space, when you modify a table or a record, it will save the information before the modification, so that the data can be rolled back. Undo contains only objects of type undo and cannot contain any other objects, only suitable for data file and interval management.

To create a table space of type undo:

Sql>create undo tablespace undo1 DataFile '/ora10/product/oradata/ora10/paul01.dbf ' size 20m;

　

Temporary tablespaces

Temporary table space, which is equivalent to a temporary dump. For sorting operations, such as you want to do a large amount of data query, but in memory can not store such a large amount of data, and then create a temporary table space on the disk to store the data. Oracle uses this temporal tablespace for sorting and storing intermediate results.

A global temporary tablespace that can be shared by multiple users and who needs to use it. However, it can only hold temporary data and cannot contain any permanent objects. It is recommended that you create this table space in local management mode.

To create a temporary tablespace:

Sql>create Temporary tablespace temp datafile '/ora10/product/oradata/ora10/paul01.dbf ' size 20m extent management lo Cal Uniform size 4m;

　　

To delete a table space:

Delete table space, use command drop tablespace ' tablespace name ' but there are 3 options to note:

Including CONTENTS: means to delete the segments in the tablespace;

including CONTENTS and datafiles: means delete segments and datafiles;

CASCADE CONSTRAINTS: Deletes all the integrity constraints associated with the space.

Cases:

DROP tablespace FESCO ' table space name ' CONTENTS and datafiles CASCADE CONSTRAINTS;

Oracle Table Space