How to know the usage of temporary tablespace in Oracle

oracle temporary tablespace
tousinn picture tousinn · Mar 26, 2013 · Viewed 14.6k times · Source

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.

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;

2.select BYTES_USED,BYTES_CACHED from V$TEMP_EXTEND_POOL

   What is the difference of BYTES_USED and BYTES_CACHED

3.select USED_EXTENDS, USED_BLOCKS v$sort_segment

the three ways really confused me a lot and I don't know what is the difference.

Answer

David Aldridge picture David Aldridge · Mar 26, 2013

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More