How to query temporary table space in oracle

Method: 1. Use "select username,temporary_tablespace from dba_users" to view the temporary table space currently used by the user; 2. Use "select * from dba_temp_files" to view the temporary table space files.

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

How to query temporary table space in oracle

Temporary table space is used to manage database sorting operations and to store temporary objects such as temporary tables and intermediate sorting results. When SORT is needed in ORACLE , and when the size of sort_area_size in PGA is not enough, the data will be put into the temporary table space for sorting. Some operations in the database: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION ALL, INTERSECT, MINUS, SORT-MERGE JOINS, HASH JOIN, etc. may use temporary table spaces. When the operation is completed, the system will automatically clean up the temporary objects in the temporary table space and automatically release the temporary segments. The release here is only marked as free and can be reused. In fact, the actual disk space occupied is not actually released. This is why the temporary table space sometimes keeps growing.

Temporary table space stores large-scale sorting operations (small-scale sorting operations will be completed directly in RAM, large-scale sorting requires disk sorting Disk Sort) and intermediate results of hash operations. It is different from permanent table space The difference is that it is composed of temporary data files (temporary files) rather than permanent data files (datafiles). The temporary tablespace does not store objects of permanent type, so it does not and does not need to be backed up. In addition, operations on temporary data files do not generate redo logs, but undo logs are generated.

When creating a temporary table space or adding a temporary data file to a temporary table space, even if the temporary data file is large, the addition process is quite fast. This is because ORACLE's temporary data file is a special type of data file: Sparse File. When the temporary table space file is created, it only writes to the header and last block information. last block of the file). Its space is deferred. This is why you create a temporary table space or add data files to a temporary table space quickly.

In addition, the temporary tablespace is in NOLOGGING mode and does not save permanent type objects, so even if the database is damaged, there is no need to restore the Temporary Tablespace during Recovery.

--查看用户当前使用的临时表空间
select username,temporary_tablespace from dba_users;
--查看数据库临时表空间文件
select * from dba_temp_files;
--查看数据库临时表空间使用情况
select * from dba_temp_free_space;

Recommended tutorial: "Oracle Video Tutorial"

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