Basic Oracle tablespace, basic oracle tablespace

Basic Oracle tablespace, basic oracle tablespace

Database Storage Structure

Databases are mainly used to store and retrieve relevant information. Oracle databases include logical and physical structures.

A physical structure refers to a real storage unit consisting of a group of files, such as data files, log files, and control files.

Data File: The file used to store data. Tables, indexes, and data are stored in data files.

Log File: used to record changes to the database.

Control File: a binary file used to store Oracle instance information, data file information, and log file information. Because data files and log files are stored, you must access the control files when starting Oracle.

The logical structure is the conceptual structure of the index data. Table space, table, and row.

 

I. Concept of table space

Tablespace is a special Oracle invention used to store the Logical Disk Space of objects such as database tables and indexes. The physical table space consists of one or more data files, and the database logically consists of one or more tablespaces.

There are three types of tablespace In The Middle Of Oracle10g:

1. Permanent tablespace: data such as base tables, views, stored procedures, and indexes are generally maintained. SYSTEM, SYSAUX, USERS, and EXAMPLE tablespaces are installed by default.

2. Temporary tablespace: used to store data of short-term activities in the system, such as sorting data.

3. Pin table space to help roll back uncommitted transaction data. Committed data cannot be recovered here.

Generally, you do not need to create temporary and undo tablespaces unless you move them to another disk to improve performance.

Table space objective:

1. assign different tablespaces to different users and assign different tablespaces to different schema objects to facilitate user operations and manage schema objects.

2. You can create different data files on different disks to facilitate disk space management, I/O performance improvement, and data backup and recovery.

　　

 

Ii. built-in tablespace:

SYSTEM tablespace: used to store data in Oracle internal tables and data dictionaries, such as the table name, column name, and user name. We do not recommend that you store tables, indexes, and other objects created by users in the SYSTEM tablespace.

SYSAUS tablespace: used to store objects of common example users in the Oracle system. The SYSAUX tablespace generally does not store user data and is automatically maintained by the Oracle system.

Undo tablespace: The tablespace used to store the Undo information. When we add, modify, or delete the data in the database table, Oracle automatically uses the Undo tablespace to temporarily store the data before the modification. After the operation is submitted, Oracle can retain the data before modification as needed to release part of the tablespace. Generally, after an Oracle instance is created, Oracle automatically creates an UNDOTBS1 tablespace.

TEMP tablespace: A special tablespace used to store temporary data. For example, if you want to sort data, the data sorted by Oracle is temporarily stored in the tablespace. After sorting, the space occupied by the sorted data is released.

USERS tablespace: The Users tablespace is the tablespace recommended by Oracle. You can create various objects, such as tables and indexes, In this tablespace.

　　

In addition to the tablespace that comes with Oracle, you can create multiple tablespaces as needed to differentiate user data from system data. Generally, in an Oracle database, only one database instance is created, and multiple tablespaces are created to manage and save the database tables in multiple projects.

　　

3. Create a tablespace

Syntax:

Create tablespace tablespacename

DATAFILE 'filename' [size integer]

[AUTOEXTEND [OFF | ON]

Tablespancename: name of the created tablespace.

Filename: A table space consisting of multiple active data files. Multiple data files are separated by commas.

Size: Specifies the file size. The unit is K (bytes) or M (MB ).

AUTOEXTEND: used to enable or enable automatic scaling of alarm data files. It is automatically extended only after AUTOEXTEND is set to ON. If it is set to OFF, it cannot be expanded automatically. The storage space is insufficient.

Example:

-- CREATE a TABLESPACE. The physical file is shop_dev_data.dbf and the initial size is 100 M. to automatically increase the value of create tablespace shop_dev_datadatafile 'e: \ DB \ Shop \ shop_dev_data.dbf 'size 100 m autoextend on

 

Iv. Operate tablespaces

1. Change the tablespace size:

Alter database datafile 'data file path' resize <new size>

-- Set the physical file size of the tablespace to 50 malter database datafile 'e: \ DB \ Shop \ shop_dev_data.dbf' resize 50 m

 

2. Change the read/write status of the tablespace.

Alter tablespace <TABLESPACE Name> read write;

-- Set the TABLESPACE to the READ-ONLY status alter tablespace SHOP_DEV_DATA read only; -- set the tablespace to the read/WRITE status alter tablespace SHOP_DEV_DATA read write;

3. delete a tablespace

Drop tablespace tablespacename;

-- Delete the tablespace drop tablespace SHOP_DEV_DATA