oracle Oracle中如何知道临时表空间的使用情况
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15629792/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to know the usage of temporary tablespace in Oracle
提问by tousinn
I am trying to compare two tables which are very large in my system(Oracle 10g). The way I used to compare is the "MINUS" operation. Because of the large size of tables, I want to know the usage of the temporary tablespace on the real time.
我正在尝试比较我的系统（Oracle 10g）中非常大的两个表。我过去比较的方式是“MINUS”操作。因为表比较大，想实时了解临时表空间的使用情况。
I googled someways on how to get the usage of the tempory tablespace. But I am not sure which one is right.Here are the three ways:
我以某种方式搜索了如何获取临时表空间的使用情况。但我不确定哪个是正确的。以下是三种方法：
1.select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
1.从V$TEMP_SPACE_HEADER中选择TABLESPACE_NAME、BYTES_USED、BYTES_FREE；
2.select BYTES_USED,BYTES_CACHED from V$TEMP_EXTEND_POOL
2.从V$TEMP_EXTEND_POOL中选择BYTES_USED,BYTES_CACHED
What is the difference of BYTES_USED and BYTES_CACHED
3.select USED_EXTENDS, USED_BLOCKS v$sort_segment
3.选择USED_EXTENDS,USED_BLOCKS v$sort_segment
the three ways really confused me a lot and I don't know what is the difference.
这三种方式真的让我很困惑，我不知道有什么区别。
采纳答案by David Aldridge
Look at the dynamic perfomance views v$sql_workarea and v$sql_workarea_active -- they will tell you not only how much space is being used by the query, but how much of it is attributable to different phases in the execution plan, what sort of sort area it is (hash join etc) and how it is being used (one-pass etc). It'll be a much more effective method of performance tuning.
查看动态性能视图 v$sql_workarea 和 v$sql_workarea_active——它们不仅会告诉您查询使用了多少空间，还会告诉您其中有多少可归因于执行计划中的不同阶段，什么样的排序区域（哈希连接等）以及它的使用方式（单次传递等）。这将是一种更有效的性能调优方法。
回答by Srini Karthikeyan
V$SORT_SEGMENTview can be used to get used/free extents, used/free blocks information for TEMPORARY tablespaces.
V$SORT_SEGMENT视图可用于获取 TEMPORARY 表空间的已用/空闲区、已用/空闲块信息。
V$TEMP_SPACE_HEADERand V$TEMP_EXTEND_POOLviews are almost the same which provides used bytes information. However, V$TEMP_EXTEND_POOL is reliable because former is updated only when DB is restarted or tablespace is recreated.
V$TEMP_SPACE_HEADER和V$TEMP_EXTEND_POOL视图几乎相同，提供了使用的字节信息。但是，V$TEMP_EXTEND_POOL 是可靠的，因为前者仅在重新启动 DB 或重新创建表空间时更新。
Note:From Oracle 11g, DBA_TEMP_FREE_SPACE view can be used to get TEMPORARY tablespace information.
注意：从 Oracle 11g 开始，DBA_TEMP_FREE_SPACE 视图可用于获取 TEMPORARY 表空间信息。
