Temporary Tablespace (Temporary Tablespace)

Temporary Tablespace

Temporary Tablespace is used to store instantaneous data, which can be viewed only during session connection. Permanentobject cannot be created in temporarytablespace.

Space utilization principle:

It will not be monitored or recycled by smon. Instead, SEP (Sort Extent Pool) is used to record idle extent.

When you create a database in OracleDatabase, the default Temporary Tablespace is automatically created, and the type of the Temporary Tablespace is Locally Managed Temporary Tablespace with Tempfile.

Temporary File

Temporary tablespace for local management has temp file, which is designed to store hash data and sort data. Temp file also stores the data of the result set when the memory size exceeds the memory size.

When creating a TemporaryFile, it is like a sparse file and does not occupy disk space. The temporary segment will be created and the disk block will be allocated only when it is used for the first time.

Temp file differs from permanent data file in the following ways:

1. Persistent database objects, such as common tables, are not stored in temp file.

2. temp file is usually in nologging status.

3. The temp file cannot be placed in the read-only state (it cannot be written, and the temp file will be meaningless ).

4. You cannot use alter database to create temp file.

5. The temp file size cannot be guaranteed, just like sparse files in UNIX systems. For more information about sparse data, see Appendix 1.

6. The view for querying temp files is also different from that for common data files, in dba_temp_files and v $ tempfile.

Temporary Segment

During the query, oracle db usually requires a temporary workspace. Generally, sorting, hashing, and merging bitmaps all use temporary segments. When creating an index, oracle db also places the index segment in the temporary segemnt, and converts the index into a persistent index segment after the index is created.

If the operation can be completed in the memory, oracle db will not use a temporary segment. In any case, if memory is unavailable, the database automatically allocates a temporary segment on the storage disk.

Changes to the temporary segement are not recorded in the online redo log, except for space management operations on the temporary segment.

Temporary table

The data in the temporary table must exist only in transactions or sessions (depending on the definition information ). Data is private in sessions. That is to say, other sessions can only view the data changed in their sessions.

When creating a temporary table, you only need to specify the lifecycle of the temporary table. The transaction or session is valid, but you do not need to specify its storage parameters to create a temporary table, in fact, only the definition information of the temporary table is created. Only when data is inserted for the first time in the session, the user's temporary tablespace is not configured with temporary segment (one segment can be used by multiple users, only allocate the respective extent ).

Temporary tables usually only have definition information, and data is dynamically stored. It can be stored in the same temporary tablespace or different. For example, if user 1 defaults temporary tablespace to temp 1 and user 2 to temp 2, u1 stores temporary data to temp1 and u2 stores temporary table data to temp2.

Although the storage of temporary table data is dynamic, because the definition information of temporary tables is static, you can also create indexes on temporary tables. The storage of data is similar to that of temporary tables, assign a temporary segemnt when using it for the first time. Of course, you can also create views and triggers on temporary tables.

When do I need to use Temporary Tablespace?

When a temporary table is used, multiple users share a temporarysegment, hashing, bitmap merge, and then sort the table, when the memory space required for the operation is greater than sort_area_size, the temporary tablespace is used as the temporary storage for sorting. The following operations are performed to sort the table space, that is to say, temporary tablespace operations may be used.

-Index creation.

When creating an index, the server process sorts the index values before building the tree (this may be performed in the temporary tablespace ). After sorting is complete, the final index is built using the temporary segment in indextablespace. Once the index construction is complete, the segment type is changed to index. (If index creation fails, smon is required to clear the temporary space in these index tablespaces)

-Order by or group by clses of SELECTstatements.

-DISTINCT values of SELECT statements.

Sort To remove duplicate values

-UNION, INTERSECTor MINUS operations.

Because duplicate values need to be eliminated, sorting is required (unionall is not included here)

-Sort-Merge joins.

If no index is available, an equivalent connection needs to perform the full table description and sorting operations respectively. Then the sorted rowsource will be merged to retrieve the matched rows in the two rowsources.

-Analyze command execution.

Sorting is required for analyzing and collecting statistics. The statistics to be collected by the optimizer are listed in appendix 2.

-Others

For example, create a primary key and createtable as select.

1 2 Next Page