oracle -3233,ORA-3233表空间相关问题处理

II. 可能的解决方案：

(1)、手工合并相邻的空闲区。

ALTER TABLESPACE COALESCE;

(2)、将一个或多个数据文件/临时文件修改为使用AUTOEXTEND。

ALTER DATABASE DATAFILE|TEMPFILE '' AUTOEXTEND ON MAXSIZE

注意：强烈建议明确MAXSIZE参数，防止数据文件/临时文件消耗卷上的所有可用空间。

(3)、添加数据文件/临时文件。

ALTER TABLESPACE ADD DATAFILE|TEMPFILE '' SIZE ;

(4)、如果段是字典管理表空间，可以降低“next_extent”和/或“pct_increase”的大小。

对于非临时段和非分区段：

ALTER STORAGE ( next pctincrease );

对于非临时段和分区段：

ALTER TABLE MODIFY PARTITION STORAGE ( next pctincrease );

对于临时段：

ALTER TABLESPACE DEFAULT STORAGE (initial next pctincrease );

(5)、重改数据文件/临时文件的大小。

ALTER DATABASE DATAFILE|TEMPFILE '' RESIZE ;

(6)、消除表空间的碎片。

附录：和此类解决方法相关的报错：

ORA-1650: unable to extend rollback segment %s by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for a rollback segment in the tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1651: unable to extend save undo segment by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for saving undo entries for the indicated offline tablespace.

Action: Check the storage parameters for the SYSTEM tablespace. The tablespace needs to be brought back online so the undo can be applied.

ORA-1652: unable to extend temp segment by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1653: unable to extend table %s.%s by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1654: unable to extend index %s.%s by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for an index segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1655: unable to extend cluster %s.%s by %s for tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for a cluster segment in tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1658: unable to create INITIAL extent for segment in tablespace %s

Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL

ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s

Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE

ORA-1683: unable to extend index %s.%s partition %s by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for index segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1688: unable to extend table %s.%s partition %s by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for table segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1691: unable to extend lob segment %s.%s by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1692: unable to extend lob segment %s.%s partition %s by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3233: unable to extend table %s.%s subpartition %s by %s in tablespace %s

Cause: Failed to allocate an extent for table subpartition segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3234: unable to extend index %s.%s subpartition %s by %s in tablespace %s

Cause: Failed to allocate an extent for index subpartition segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3238: unable to extend LOB segment %s.%s subpartition %s by %s in tablespace %s

Cause: An attempt was made to allocate an extent for LOB subpartition segment in tablespace, but the extent could not be allocated because there is not enough space in the tablespace indicated.

Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

总结：

针对上面案例中的错误，总体讲是空间不足导致的，之所以使用第二个SQL可以，原因可能就是这种参数值设置下的满足可以空闲空间连续块的容量，上面采用的是减小extent分配大小的方式，另外上面提到的扩大文件、修改参数值、消除碎片化等方法都可以尝试使用。