Table Spaces in Oracle

What is a tablespace?

The Oracle database contains logical and physical structures.

The physical structure of a database refers to a set of operating system files that make up a database.

The logical structure of a database refers to a set of logical concepts that describe how the data is organized and the relationships between them.

　　a tablespace is an important component of the logical structure of a database database. table spaces can hold a variety of application objects, such as tables and indexes. Each table space consists of one or more data files.

Classification of table spaces

1. Permanent table space:

Data such as tables, views, procedures, and indexes are generally saved. SYSTEM,sysaux,USERS, TEMP the tablespace is installed by default.

2. Temporary table space:

It is used only to save data for short-term activities in the system, such as sorting data.

3. Undo Table Space:

To help fallback uncommitted transaction data, the submitted data is not recoverable here. It is generally not necessary to have the keys temporarily and undo the tablespace unless they are transferred to their

His disk in order to improve performance.

The role of Table spaces

1. Assigning different table spaces to different users, assigning different table spaces to different schema objects, facilitating the operation of user data and management of schema objects.

2. Different data files can be created on different disks, which facilitates the management of disk space, improves I/O performance, and facilitates the backup and recovery of databases.

OracleTable space automatically created by the system

The Oracle system automatically establishes multiple table spaces after the installation of an Oracle system and the creation of an Oracle instance.

1 . SYSTEM Table Space

The system tablespace is used to hold data from internal Oracle system tables and data dictionaries, such as table name, column name, user name and so on. It is not recommended that objects such as user-created tables, indexes, and so on be stored in the SYSTEM table space.

2. Sysaux Table Space

The sysaux table space is used to store objects for common sample users inside an Oracle system. Sysaux table spaces are generally not Store the user's data, automatically maintained by the Oracle system.

3. Undo Table Space

The tablespace that is used to hold the Undo information for the table space. When we add, delete, and modify data from a database table, theOracle system automatically uses the Undo table space to temporarily store the data before it is modified. When

After the modification has been completed and submitted, theOracle system can reserve some space for the undo table space as long as it needs to retain the length of the pre-modification data. Typically , after you create an Oracle instance, theOracle system automatically

Create an undo tablespace with the name "UNDOTBS1".

4. USERS Table Space

The users table space is a table space that Oracle recommends that users use to create various objects, such as creating tables, indexes, and so on. Oracle's basic sample user Scott's objects are stored in the

　　　　In the Users table space.

5. TEMP Table Space

The temp tablespace is a special table space that Oracle systems use to hold temporary data. For example, when sorting is required in an operation, theOracle system temporarily stores the sorted data in the tablespace. Sort at

The space occupied by the ordered data is released when the manager is finished , so it is called a temporary tablespace.

CREATE TABLE Space

　　　　CREATE tablespace tablespacename
DataFile
'filename' [SIZE integer [K | M]] [,...]
[Autoextend [OFF | On]];

Explain:

Tablespacename is the name of the tablespace you want to create.

FileName is the path and name where the data file is stored.

An integer is a number you enter yourself, specifying the size of the file, K is the size of the kilobytes, and M is the megabyte size.

... (ellipsis) is another data file that specifies the constituent table space. When you create a tablespace, you can specify multiple data files, with each data file separated by (comma).

Autoextend is used to start or disable the automatic extension of data files, on is the space used to automatically expand, off is not automatically extended. Set to off may present table space remaining capacity

is 0, so that data cannot be stored in the database.

Example: Below, create a tablespace named Tp_hr with two data files, auto scale size, and initial size of 30M (trillion).

CREATE tablespace tp_hr
DataFile
' d:\data\tp_hr01.dbf ' SIZE 30M,
' d:\data\tp_hr02.dbf ' SIZE 30M autoextend on;

View Table Spaces

SELECT file_name,tablespace_name,bytes,autoextensible
From Dba_data_files
WHERE tablespace_name= 'tp_hr'; --Query by table space name

resizing table spaces

Method One: Change the size of the data file

ALTER DATABASE datafile
'D:\DATA\tp_hr01.dbf'--Specify file path
RESIZE 80M; --Specify size

Method Two: Add data files to the Tablespace

ALTER tablespace tp_hr -- Specify the name of the tablespace
ADD datafile
'E:\DATA\tp_hr02. DBF' size 20M --Specify file path and size
Autoextend on; --Automatic expansion

Delete Table space

Mode 1:

DROP tablespace tp_hr; --delete the tablespace named tp_hr, but this syntax simply removes the tablespace and the "contact" of the database and does not delete the actual data file.

Mode 2:

DROP tablespace tp_hr including CONTENTS; --delete the tablespace named TP_HR and delete the data file as well.

(Note: It is a good idea to back up the database before deleting the tablespace.) )

Table Spaces in Oracle