What is oracle tablespace

Oracle table space is a logical division of the database and the smallest unit for Oracle database recovery. It accommodates many database entities, such as tables, views, indexes, clusters, rollback segments, temporary segments, etc. An Oracle database can have one or more table spaces, and a table space corresponds to one or more physical database files.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

1. Concept

Oracle tablespaces (tablespaces) is a logical concept. Data files are what actually store data. An Oracle database can have one or more table spaces, and a table space corresponds to one or more physical database files.

Table space is the smallest unit for Oracle database recovery, housing many database entities, such as tables, views, indexes, clusters, rollback segments, temporary segments, etc.

1. Features of Oracle table space:

(1) Control database data disk allocation;

(2) Restrict users in the table space The amount of disk space that can be used;

(3) The table space has online, offline, readonly, and readwrite attributes.

2. Classification of table spaces:

• Permanent table space: some objects to be permanently stored in the database, such as: tables, views , Stored procedure

• Temporary table space: The process executed in the middle of the database operation. After the execution is completed, the stored content will be automatically released

• UNDO table space: used to save the old values ​​​​of transaction modified data, and can roll back the data

Segment (segment) refers to the space occupied by the data file Common name, or a collection of space used by database objects; segments can include table segments, index segments, rollback segments, temporary segments, cache segments, etc.

Extent (Interval/Extension), any continuous block allocated to an object (such as a table) is called an interval; the interval is also called an extension, because when it uses up the allocated interval, it will When new records are inserted, a new interval must be allocated (that is, some blocks are extended); once the interval is allocated to an object (table, index, and cluster), the interval cannot be allocated to other objects.

Structural diagram

datafile, segment, extend relationship diagram

2. Related views

select * from dba_data_files;   --describes database files  数据文件信息
select * from dba_temp_files;   --describes all temporary files (tempfiles) in the database 临时数据文件信息
select * from dba_free_space;   --describes the free extents in all tablespaces in the database 数据库中所有表空间中的空闲扩展区
select * from dba_segments;     --describes the storage allocated for all segments in the database 数据库中的所有段分配的存储

3. Related operations

1. Create table space

--语法：
create [temporary] tablespace tablespace_name tempfile|datafile ‘xx.dbf’ size xx;
--创建临时表空间时，加上temporary关键字;

2. Expand the table space. When a certain table space is used up, the insert operation can no longer be performed on the database table. At this time, we need to expand the table space. , you can expand the table space by adding datafile files.

select f.* from dba_data_files f where f.tablespace_name='MLT';--查看表空间信息

alter tablespace MLT --表空间名
     add datafile '/home/oracle/oradata/crm/mlt04.dbf'  --datafile文件路径
     size 100M --表空间大小
     autoextend on --自动扩展

3. Modify the status of the table space

alter tablespace tablespace_name online|offline;--表空间是脱机时不可用，默认是联机的

4. Delete table space

drop tablespace tablespace_name[including contents];
--including contents 表示删除表空间包括datafile数据文件，不加则不删除相关数据文件;
--删除数据文件时，不能删除表空间当中第一个数据文件，如果要删除就需要删除整个表空间。

5. Check table space usage

--查询表空间使用情况SELECT Upper(F.TABLESPACE_NAME)         "表空间名",
       D.TOT_GROOTTE_MB                 "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
       || '%'                           "使用比",
       F.TOTAL_BYTES                    "空闲空间(M)",
       F.MAX_BYTES                      "最大块(M)"FROM   (SELECT TABLESPACE_NAME,
               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
        FROM   SYS.DBA_FREE_SPACE
        GROUP  BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
        FROM   SYS.DBA_DATA_FILES DD
        GROUP  BY DD.TABLESPACE_NAME) DWHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME--查询表空间的空闲扩展区select tablespace_name, count(*) AS extends,round(sum(bytes) / 1024 / 1024, 2) AS 大小/MB,sum(blocks) AS blocks from dba_free_space group BY tablespace_name;--查询表空间的总容量select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;--查询表空间使用率SELECT total.tablespace_name,
       Round(total.MB, 2)           AS   总量/MB,
       Round(total.MB - free.MB, 2) AS  已使用/MB,
       Round(( 1 - free.MB / total.MB ) * 100, 2) || '%'                  AS  使用率FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_data_files
        GROUP  BY tablespace_name) totalWHERE  free.tablespace_name = total.tablespace_name;

Recommended tutorial: "Oracle Tutorial》

The above is the detailed content of What is oracle tablespace. For more information, please follow other related articles on the PHP Chinese website!