Oracle database comes with table space

Requirements: We need to organize the table space created by the site user and its storage data, and standardize management. When organizing the table space created by the user, it is necessary to exclude the table spaces that non-user creates, all of which need to be created by the user, and those that are brought by Oracle.

The native test builds a new library and finds its own table space as follows:

Here is a brief description of each table space (some of the content is from the network):

1. Sysaux Table Space

The Sysaux table space is introduced in Oracle Database 10g as a secondary tablespace for system table spaces. Previously, some database components that use a separate tablespace or system tablespace are now created in the Sysaux table space. Sysaux Tablespace holds some other Metadata components, such as Oem,streams, are stored in the Sysaux table space by default. By separating these components and functions, the load on the system table space is reduced. Repeatedly creating some related objects and components prevents fragmentation of the system tablespace.

2. system table Space

The system tablespace is created automatically when Oracle creates a database, each Oracle database has a system tablespace, and the system table space is always kept in online mode because it contains the basic information required to run the database, such as: Data dictionary, online Help mechanism, All fallback segments, temporary segments and bootstrap segments, all user database entities, tables for other Oracle software PRODUCT requirements, and so on.

3. Temp Table Space

Temporary tablespaces are used to manage database sorting operations and temporary objects such as temporary tables, intermediate sort results, and so on, when the sort is needed in Oracle, and when the sort_area_size size in the PGA is insufficient, the data is placed in a temporary table space for sorting. Like some operations in the database: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER by, GROUP by, UNION all, INTERSECT, minus, Sort-merge JOINS, HASH JOIN The temporary table space may be used. When the operation is complete, the temporary object in the temporary tablespace is automatically cleaned up and the temporary segment is automatically freed. The release here is simply marked as free, reusable, and the actual disk space consumed is not actually released. This is also why temporary table spaces can sometimes grow. Staging table space stores large-scale sorting operations (small-scale sorting operations are done directly in RAM, large-scale sorting requires disk sort), and intermediate results for hashing operations. It differs from the permanent table space in that it consists of a temporary data file (temporary files), Instead of a permanent data file (datafiles). Temporary tablespace does not store persistent types of objects, so it does not and does not need to be backed up. Additionally, the operation of the temporary data file does not produce a redo log, but an undo log is generated.

4. Undo Table Space

The Undo table space is a unique concept for Oracle. The undo segment is automatically assigned in the undo Tablespace to hold the undo information for the DML statements in the transaction, that is, to save the value of the data before it is modified. In rollback, the instance is restored (rolled forward), and the undo information is used when constructing a consistent read CR block. Because of the introduction of undo, when Oracle's SELECT statement implements consistent reads, no locks are required. The Undo tablespace and other table spaces have many similarities: The Undo data block is also read to the buffer cache, and the redo log is generated when modified, and the data is written back to the disk on the Undo table space. So after the crash, the buffer cache of the undo block will be restored.

5. Users table Space

The creation user must specify a tablespace for it, and if there is no explicit specified default tablespace, it is specified as the Users table space, and all information for this user is placed in the people table space.

Oracle database comes with table space