Temporary Oracle tablespace

No index is created for certain fields in the query. In Oracle, if the table does not have an index, all data will be copied to the temporary tablespace.

No index is created for certain fields in the query. In Oracle, if the table does not have an index, all data will be copied to the temporary tablespace.

I. Temporary tablespace is mainly used in the following situations:

1. order by or group by (disc sort accounts for the main part );

2. index creation and re-creation;

3. Perform the distinct operation;

4. union & intersect & minus sort-merge joins;

5. Perform Analyze operations;

6. Some exceptions may also cause the TEMP surge.

The analysis of the phenomenon of temporary tablespace increase in Oracle may be due to the following reasons:

1. Do not set the upper limit for the temporary tablespace, but allow unlimited growth. However, if you set an upper limit, you may still encounter an error due to insufficient space. The temporary tablespace setting is too small, which affects performance. The temporary tablespace setting is too large, which also affects performance, as for how many cases need to be set, you need to perform a careful test.

2. During query, too many tables are used in table join queries. We know that during a table connection query, A discar product is generated based on the queried fields and the number of tables. The size of the discar product is the size of the temporary space required for a query, if too many fields are queried and the data is too large, a large temporary tablespace is consumed.

3. Some fields in the query are not indexed. In Oracle, if the table does not have an index, all the data will be copied to the temporary tablespace. If there is an index, the data of the index is generally copied to the temporary tablespace.

Based on the above analysis, the query statements and indexes are optimized to alleviate the problem, but further tests are required.

Summary:

1. SQL statements will affect disk consumption. Improper statements will cause disk spikes.

2. You need to carefully plan the query statements. do not define a query statement, especially in software that provides custom queries.

3. carefully plan the table index. If the temporary tablespace is temporary, the space is not released, but is marked as free after the sort ends. If the temporary tablespace is permanent, SMON is responsible for releasing it after the sort ends, they do not need to be manually released. View which users and SQL leads to TEMP growth in two important views: v $ sort_usage and v $ sort_segment.

By querying relevant information, we found that there are several solutions:

Ii. Temporary table types

Create an Oracle temporary table. There are two types of temporary tables:

Session-level temporary table

Transaction-level temporary table.

1) SESSION-level temporary tables because the data in this temporary table is related to your current SESSION. When your current SESSION does not exit, the data in the temporary table still exists, when you exit the current SESSION, all the data in the temporary table is lost, of course, if you log on to another SESSION at this time, you will not be able to see the data inserted into the temporary table in another SESSION. That is, the data inserted by two different sessions is irrelevant. When a SESSION exits, the data in the temporary table is truncated (truncate table, that is, data is cleared. Session-level temporary table creation method:

Create Global Temporary Table Table_Name (Col1 Type1, Col2 Type2. ..) On Commit Preserve Rows;

2) A transaction-level temporary table is a transaction-related temporary table. When a transaction is committed or rolled back, the data in the temporary table will be truncated by itself, the other content is consistent with the SESSION-level temporary table (including when the SESSION is exited, the transaction-level temporary table will be automatically truncated ). How to create a temporary transaction table:

Create Global Temporary Table Table_Name (Col1 Type1, Col2 Type2. ..) On Commit Delete Rows;