What is table space in oracle

In Oracle, table space is a container for Oracle data objects and data storage. It is a logical concept. Several operating system files can form a table space. Table spaces uniformly manage data files in the space. One data file can only belong to one table space; and a database space consists of several table spaces.

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

Oracle Table Space

In the database system, storage space is a more important resource. Proper use of space can not only save space, but also improve the system. efficiency and work performance.

Oracle can store massive amounts of data, and all data is stored in data files. The size of data files is limited by the operating system, and overly large data files have a great impact on data access performance. At the same time, Oracle is a cross-platform database. Oracle data can be easily transplanted on different platforms. So how can we provide large capacity in a unified access format? Oracle uses table spaces to solve this problem.

Table space is a container for Oracle data objects and data storage. It is just a logical concept. Several operating system files (the files may not be very large) can form a table space. Table spaces uniformly manage data files in the space, and one data file can only belong to one table space. A database space consists of several table spaces. As shown in the figure:

All data in Oracle (including system data) are all stored in table spaces. Common table spaces are:

• System table space:

  Stores system data. The system table space is created when the database is created. The table space name is SYSTEM. Stores important system data information such as data dictionaries, views, and database structures. If the SYSTEM space is insufficient during operation, the impact on the database will be greater. Although the space can be expanded through commands during system operation, it will still affect the performance of the database, so It is necessary to set the data file appropriately when creating the database.

• TMEP table space:

  Temporary table space is created when installing the database. The temporary table space can be increased through commands at runtime. The important role of the temporary table space is data sorting. For example, after the user executes a command such as Order by, the server needs to sort the selected data. If the data is large, the sorting area of ​​the memory may not be able to hold the data, so some intermediate sorting results need to be written to the temporary storage area of ​​the hard disk. in the table space.

• User table custom space:

  Users can create table spaces through the CREATE TABLESPACE command

Create tables Space

The syntax structure of Oracle's creation of table space is as follows:

create tablespace tab_name
datafile 'filename'
size n
[autoextend on next n1 maxsize m /of]
[permanent] 
[extent management local/dictionary];

Syntax analysis:

• create tablespace: keyword to create table space.

• tab_name: The name of the table space after creation.

• datafile: Specify the path of the data file as filename.

• size n: Specify the size of the data file.

• [autoextend on next n1 maxsize m /of ]: Indicates whether the table space is automatically expanded, on means automatic expansion, of means no expansion, when automatically extended, next n1 means The size of automatic expansion, max size m means that the data file can be expanded to a maximum size of m.

• [permanent]: Indicates the type of table space created. Permanent indicates permanent table space. If not filled in, the default permanent table space will be used.

• [extent management local/dictionary]: Indicates the table space management method, local indicates the local management mode, and dictionary indicates the data dictionary management mode. The default is local management mode.

Case 1, According to the table space creation syntax, create a table space student with a 100M size data file (student.dbf). The code is as follows:

create tablespace student
datafile 'E:\APP\ADMIN\ORADATA\ORCL\student.DBF'
size 100m
autoextend on next 10m maxsize 500m
permanent
extent management local;

Case analysis:

Create a student table space and specify the data file as "E:\APP\ADMIN\ORADATA\ORCL\student.DBF". The table space is automatically expanded every time. The extension size is 10M, and the maximum extension is 500M. A permanent table space is created to store the database objects and data of the student user. The management mode is local management.

We check the data dictionary dba_data_files and dba_tablespaces to query the created student table space. The query code is as follows:

select t.TABLESPACE_NAME, --表空间名
       t.FILE_NAME, --文件名
       t.AUTOEXTENSIBLE, --是否自动扩展
       t.BYTES / 1024 / 1024, --表空间初始大小
       t.MAXBYTES / 1024 / 1024, --表空间最大扩展到多少
       b.CONTENTS, --表空间类型
       b.EXTENT_MANAGEMENT --表空间管理模式
  from dba_data_files t, dba_tablespaces b
 where t.TABLESPACE_NAME = b.TABLESPACE_NAME

Recommended tutorial: "OracleTutorial》

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