What if the temporary Oracle tablespace temp is full?

Recently, this problem occurs: The Oracle Table query on the Web page is normal some time ago, but the page cannot be displayed until recently. Print out the SQL statement and put it in PL/SQL devoloper for execution. The message "the temp segment cannot be extended through 8 (in tablespace XXX)" is displayed. There is also a page where records can be queried, but statistics cannot be collected!
The cause of the analysis may be that the tablespace in the temporary Oracle segment is insufficient, because Oracle always tries its best to allocate continuous space. The above phenomenon occurs when there is not enough allocable space or the allocation is not continuous.

Solution: Since Oracle uses the tablespace as a logical structure-unit, the physical structure of the tablespace is a data file, and the data file is physically created on the disk, all objects in the tablespace also exist on the disk. To add space to the tablespace, you must add data files. First, check the available space of the specified tablespace and use view SYS. dba_free_space. Each record in the view represents the shard size of the available space:

SQL> select file_id, block_id, blocks, bytes from SYS. dba_free_space where tablespace_name = 'xxx ';

The returned information can preliminarily determine the maximum block of the available space, check whether it is smaller than the size mentioned in the error message, and then check the default tablespace parameters:

SQL> select initial_extent, next_extent, min_extents, pct_increase from SYS. dba_tablespaces where tablespace_name = 'xxx ';

Use the following SQL command to modify the default storage value of the temporary tablespace:

SQL> alter tablespace name default storage (initial 64 K next 64 K );

Increasing the size of the default value may solve the problem. You can also modify the size of your temporary tablespace to solve the problem:

SQL> alter User Username temporary tablespace new_tablespace_name;

After the alter tablespace command is run, the added space can be used without exiting the database or taking the tablespace offline. However, once a data file is added, you cannot delete it. To delete it, you need to delete the tablespace.

If you do not like to use commands, you can also use the Oracle tool DBA studio to find the tablespace in the problematic database. The specific location is "Storage"-> "tablespace"-> "XXX ", right-click "general information" to check the usage of data files. Select "Storage" and modify it as needed. The problem may be solved by "initial size" and "next size, select "XXX" and Right-click and select "add data file ", modify the "file size" of "General Information" and the "incremental" size of "data files automatically expanded after" Storage "is full. The problem can also be solved!