Termination due to tablespace overflow

Summary

Symptom

ORA-01650: unable to extend rollback segment <rbs> in tablespace <tsp>
ORA-01651: unable to extend save undo segment for tablespace <tsp>
ORA-01652: unable to extend temp segment in tablespace <tsp>
ORA-01653: unable to extend table <tab> in tablespace <tsp>
ORA-01654: unable to extend index <ind> in tablespace <tsp>
ORA-01655: unable to extend cluster <clus> in tablespace <tsp>
ORA-01658: unable to create INITIAL extent for segment in tablespace.
          <tsp>
ORA-01659: unable to allocate MINEXTENTS beyond <ext> in tablespace
          <tsp>
ORA-01680: unable to extend LOB segment in tablespace <tsp>
ORA-01683: unable to extend index <ind> partition <part>
          in tablespace <tsp>
ORA-01688: unable to extend table <tab> partition <part>
          in tablespace <tsp>
ORA-01691: unable to extend lob segment <lob> in tablespace <tsp>
ORA-01692: unable to extend lob segment <lob> partition <part>
          in tablespace <tsp>
ORA-30036: unable to extend segment in undo tablespace <tsp>

Other terms

DBIF_RSQL_SQL_ERROR

Reason and Prerequisites

If a database object is growing so that the extents allocated so far are no longer sufficient, Oracle automatically allocates a new extent depending on the memory parameter NEXT_EXTENT of the segment (DMTS), the UNIFORM size (LMTS UNIFORM) or the system-specific size (LTMS AUTOALLOCATE). If the system cannot make available this amount of free space in the associated tablespace, the transaction concerned terminates with one of the above errors.

The error message gives details about the number of blocks requested. "unable to extend ... by 1000 in tablespace ..." means, for example, that 1000 blocks - in other words 1000 * 8K = 8M by default - are required.

Solution

You should check the following points, even if the problem can usually be solved by extending the tablespace:

• If the overflow in a DMTS is triggered by a very high value for NEXT_EXTENT, you can reduce the NEXT_EXTENT value instead of extending the tablespace.
  
  ALTER TABLE "<table_name>" STORAGE (NEXT <new_value>);
  ALTER INDEX "<table_name>" STORAGE (NEXT <new_value>);

• If the temporary tablespace (ORA-01652) overflows, circumstances other than insufficient tablespace are often also responsible for the problem. Therefore, check in particular whether there are complex SQL statements with a large number of sort, hash or bitmap operations (see Note 766349 on SQL optimization) and whether the prerequisites for good Oracle performance, such as correct parameterization or a current Patch Set, are fulfilled (Note 618868).

           If, in the case of users other than SYS and SYSTEM, an ORA-01652 occurs with regard to the SYSTEM tablespace, this can be caused by the fact that the SYSTEM tablespace was, by mistake, allocated to a user as temporary tablespace.

If statement SELECT USERNAME FROM DBA_USERS
WHERE TEMPORARY_TABLESPACE = 'SYSTEM' AND USERNAME NOT LIKE 'SYS%';

returns the SAP or OPS$ user, the temporary tablespace should be changed:

ALTER USER <username> TEMPORARY TABLESPACE <psaptemp>;

where <psaptemp> is the temporary tablespace used (such as PSAPTEMP).

• In the event of an overflow of the undo tablespace as of Oracle 10g with ORA-30036, you should first check whether a retention guarantee has been activated deliberately or accidentally for the undo tablespace:
  
  SELECT RETENTION FROM DBA_TABLESPACES
  WHERE TABLESPACE_NAME = 'PSAPUNDO';

           If this returns "GUARANTEE", the ORA-30036 was probably generated to prevent an ORA-01555 from occurring at a different point within the undo retention time (see Note 3807). Since this is usually something you want to avoid, you should deactivate the guarantee in this case:

ALTER TABLESPACE PSAPUNDO RETENTION NOGUARANTEE;

           Further actions such as expanding the tablespace are not required.

• If the ORA-30036 error occurs with Oracle 9i and if you are using ASSM tablespaces, refer to Note 912918.

• If you use Oracle version 10.2.0.2 or lower and AUM, refer to Note 1045320.

• In the case of a LOB segment-specific termination (ORA-01691, ORA-01692), refer to Note 500340 and check whether PCTVERSION is set unnecessarily high.

• To avoid tablespace overflows and manual extensions as far as possible, you could also consider activating AUTOEXTEND, so that Oracle automatically extends an existing data file if necessary (provided there is sufficient space in the file system):
  
  ALTER DATABASE DATAFILE '<path_and_name>' AUTOEXTEND ON;

• In other cases, it makes sense to add one data file to the affected tablespace. You can, for example, use the TSEXTEND function of the BRSPACE administration tool to do this.
  
  brspace -f tsextend

           For more details, see the SAP online documentation.