ORACLE Temp Table Space What if I'm full?

oracle| temporary table
Recently encountered a problem: the previous Time page query Oracle table is normal, but the page has recently been unable to display. Print out the SQL put to pl/sql devoloper execution, the newspaper "cannot through 8 (in the table space XXX) Extended temp section", there is a page, you can query out the record, but not statistics!
The reason may be that there is not enough space in the Oracle Temporary segment table because Oracle always allocates contiguous space as much as possible, as long as there is not enough unallocated space or the distribution is discontinuous.

Workaround: Knowing that because Oracle takes tablespaces as logical structures-cells, and the physical structure of the table space is data files, data files are physically created on disk, and all the objects in the table space are on disk, additional data files must be added in order to add space to the tablespace. Look at the free space in the specified table space, using the view sys.dba_free_space, each record in the view represents the size of the free space fragment:

Sql>select file_id,block_id,blocks,bytes from Sys.dba_free_space where tablespace_name= ' XXX ';

The returned information can initially determine the maximum block of free space, see if it is smaller than the size mentioned in the error message, and then look at the default table space parameters:

Sql>select initial_extent,next_extent,min_extents,pct_increase from SYS. Dba_tablespaces WHERE tablespace_name= ' XXX ';

Modify the default storage value for the temporary segment table space by using the following SQL command:

Sql>alter tablespace name DEFAULT STORAGE (INITIAL 64K NEXT 64K);

Appropriately increasing the size of the default value may resolve the error problem, or you can fix the problem by modifying the user's temporary table space size:

Sql>alter USER username temporary tablespace new_tablespace_name;

Using the Alter TABLESPACE command, once completed, the increased space can be used without exiting the database or taking the tablespace offline, but note that once you have added a data file, you cannot delete it, and to delete it, you delete the tablespace.

Do not like the command of friends, you can also use Oracle Tools DBA STUDIO to find the problem library table space, specific location in the "Storage"-> "tablespace"-> "XXX", right block "general information", you can see, data file usage, select storage, can be appropriately modified, " Initial size ", and" next size ", the problem may be solved, then select" XXX "click the right mouse button, choose" Add Data File ", appropriate to modify the" General information "file size and storage of the" Data file is full automatic expansion "of the" incremental "size, the problem can also be resolved!