如何本地复现ORA-01652: TEMP_STD_TBL表空间临时段扩展失败报错

ORA-01652是临时表空间空间不足触发的异常，你之前调整的是永久表空间USERS的用户配额，和临时表空间无关，因此无法触发目标报错。复现步骤如下：

前置说明 #

Oracle中排序查询、哈希关联、临时表存储、索引创建排序等操作都会占用临时段，当临时段需要的空间超过当前可用的临时表空间时，就会抛出ORA-01652错误。

具体操作步骤 #

• 第一步：确认要复现的目标临时表空间
  执行以下命令查询数据库默认临时表空间：
  SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
  若你报错中提到的SOME_TABLESPACE是自定义临时表空间，后续操作替换为对应名称即可。
• 第二步：限制用户可用的临时表空间容量
  根据Oracle版本选择对应操作：
  • Oracle 12c及以上版本（支持用户临时表空间配额限制）：
    直接给用户设置极小的临时表空间配额：
    ALTER USER MY_USER QUOTA 1M ON TEMP;
  • Oracle 11g及更早版本（不支持用户临时表空间配额限制）：
    先创建一个极小的、禁止自动扩展的临时表空间，再将用户的临时表空间切换为该表空间：

    CREATE SMALLFILE TEMPORARY TABLESPACE TEMP_SMALL TEMPFILE 'temp_small01.dbf' SIZE 1M AUTOEXTEND OFF;
    ALTER USER MY_USER TEMPORARY TABLESPACE TEMP_SMALL;
    

• 第三步：执行消耗临时空间的操作触发报错
  切换到MY_USER用户，执行大资源消耗操作即可触发目标报错，示例操作二选一：
  1. 对大数据量表执行全字段排序：
     SELECT * FROM 数据量大于1M的表 ORDER BY 1,2,3,4,5,6,7,8,9,10;
  2. 往临时表插入大量数据：

  CREATE GLOBAL TEMPORARY TABLE TMP_TEST (ID NUMBER, CONTENT VARCHAR2(4000)) ON COMMIT PRESERVE ROWS;
  BEGIN
    FOR I IN 1..100000 LOOP
      INSERT INTO TMP_TEST VALUES(I, LPAD('X',3900,'X'));
    END LOOP;
    COMMIT;
  END;
  /
  

测试完成后恢复权限：12c+版本执行ALTER USER MY_USER QUOTA UNLIMITED ON TEMP;即可；11g及更早版本执行ALTER USER MY_USER TEMPORARY TABLESPACE TEMP;后删除TEMP_SMALL表空间即可。

内容的提问来源于stack exchange，提问作者Dinu Nicolae