ORA-01652: Unable to extend symp segment by num in tablespace name
Cause: Oracle Temporary Section Table space is insufficient, because Oracle always tries to allocate continuous space, one but not enough can allocate space or allocation does not continuously appear.
Workaround: We know that because Oracle uses the table space as a logical structure-unit, the physical structure of the table space is data file, the data file is physically created on the disk, and all objects of the table space also exist on the disk, in order to give the watch space. Increase space, you must add data files. First look at the free space of the specified table space, use the view sys.dba_free_space, each record in the view represents the fragmentation of the available space:
SQL> SELECT FILE_ID, BLOCK_ID, Blocks, Bytes from Sys.dba_Free_Space Where TableSpace_name = '';
The returned information can initially determine the maximum block of the available space, see if it is less than the size mentioned in the error message, then check the default tablespace parameters:
SQL> SELECT INITIAL_EXTENT, Next_EXTENT, Min_EXTENTS, PCT_INCREASE, PCT_INCREASE from sys.dba_tablespaces where tablespace_name = name;
Modify the default storage value of the episode table space by the following SQL command:
SQL> ALTER TABLESPACE NAME DEFAULT Storage (Initial XXX NEXT YYY);
Appropriately increase the size of the default value is possible to solve the error problem, or you can solve this problem by modifying the user's temporary table space size:
SQL> ALTER USER Username Temporary TableSpace New_Tablespace_name;
Using the alter tablespace command, one but complete, the added space can be used, there is no need to exit the database or make the table space offline, but to note that once the data file is added, it can no longer delete it. To delete it, it is necessary to delete Table space.
An error example is as follows:
ORA-1652: Unable to extend symp segment by 207381 in TableSpace Tempspace
The corresponding English is as follows:
Cause: failed to allocate Extent for Temp Segment in TableSpace
Action: Use the alter tablespace address..
