快速修复步骤：1. 检查表空间使用情况，执行SELECT TABLESPACE_NAME, ROUND((USED_SPACE/TOTAL_SPACE)*100,2) AS PERCENT_USED FROM (SELECT DFS.TABLESPACE_NAME, SUM(DFS.BYTES)/1024/1024 AS TOTAL_SPACE, SUM(DFS.USED_SPACE)/1024/1024 AS USED_SPACE FROM DBA_FREE_SPACE DFS, DBA_TABLESPACES DTS WHERE DFS.TABLESPACE_NAME = DTS.TABLESPACE_NAME GROUP BY DFS.TABLESPACE_NAME); 2. 如果空间不足，添加数据文件：ALTER TABLESPACE your_tablespace_name ADD DATAFILE '/path/to/newfile.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; 3. 远程处理：用SQL*Plus或PL/SQL Developer连接远程数据库，登录sysdba执行以上命令，重启相关索引或重建：ALTER INDEX index_name REBUILD; 4. 高效解决表空间不足：清理临时表空间，DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; 然后创建新临时表空间；或者移动大表到其他表空间。

CSDN博客原文摘录

ORA-01654: unable to extend index xxx by xxx in tablespace xxx 错误解决办法。现象：执行SQL语句时，报错：ORA-01654: unable to extend index SYS.I_JOB_JOBSTEP by 128 in tablespace SYSTEM。解决：1）查询表空间还有多少可用空间 SQL>select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_data_files; 2）增加表空间大小 alter database datafile '/u01/app/oracle/oradata/orcl/system01.dbf' autoextend on next 100m maxsize 2G; 或者直接增加数据文件大小：alter database datafile '/u01/app/oracle/oradata/orcl/system01.dbf' resize 2G; 3）重建索引：SQL> alter index index_name rebuild online; 注意：如果是系统表空间，建议不要往里面塞数据，最好建自己的表空间。

Oracle官方文档及论坛讨论

ORA-01654: 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: Gain free space in the tablespace by dropping/defragmenting/deleting unnecessary objects or add/change extents, or change the storage parameters of the index. For remote processing, connect via SQL*Net to the database instance and issue ALTER TABLESPACE commands. To resolve tablespace shortage efficiently: Use Automatic Segment Space Management (ASSM) tablespaces, monitor with Enterprise Manager, or script: SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='YOUR_TS' ORDER BY BYTES DESC; then ALTER TABLE MOVE to larger TS.

知乎用户经验分享

遇到ORA-01654，表空间满了。最简单方法：1. 查看哪个表空间满了：select * from dba_free_space where tablespace_name='USERS'; 2. 扩展数据文件：ALTER DATABASE DATAFILE '/opt/oracle/oradata/XE/USERS.dbf' AUTOEXTEND ON; 或者加新文件：ALTER TABLESPACE USERS ADD DATAFILE '/opt/oracle/oradata/XE/USERS02.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 1G; 远程方案：用Toad或DBeaver远程连，sys as sysdba登录执行。高效解决：定期用expdp/impdp导出大表，删除后重建；或用大文件表空间：CREATE BIGFILE TABLESPACE new_ts DATAFILE SIZE 32G;

博客园实战案例

今天碰到ORA-01654错误，索引无法扩展。远程服务器没法直接登录，用PL/SQL Developer远程连接，执行：先查select * from v$session_longops; 杀掉长事务，然后ALTER INDEX "IX_NAME" REBUILD ONLINE; 表空间不足时，ALTER TABLESPACE "TS_NAME" ADD DATAFILE 'newfile.dbf' SIZE 500M AUTOEXTEND ON; 高效方式：设置自动扩展，并监控警报日志，脚本定时检查空间使用率超过80%就自动加文件。

Stack Overflow英文翻译摘录

The error ORA-01654 means the tablespace doesn't have enough space to hold the index extent. Remote fix: Connect remotely with sqlplus user/pw@remotehost:port/service as sysdba. Then: 1. Find the tablespace: SELECT * FROM DBA_INDEXES WHERE INDEX_NAME='YOUR_INDEX'; 2. Add space: ALTER TABLESPACE tablespace_name ADD DATAFILE '/path.dbf' SIZE 100M AUTOEXTEND ON; 3. Rebuild if needed: ALTER INDEX index_name REBUILD; For shortage: Shrink unused space or move segments.

FAQ
Q: ORA-01654经常出现怎么预防？
A: 设置表空间AUTOEXTEND ON，并定期监控空间使用，删除无用对象。
Q: 远程无法sysdba登录怎么办？
A: 配置TNSNAMES.ORA，用sqlplus sys/pw@tnsname as sysdba。
Q: 表空间是SYSTEM怎么处理？
A: 不要用SYSTEM存用户数据，创建专用表空间并移动索引。
Q: 重建索引会锁表吗？
A: 用REBUILD ONLINE不会长时间锁表。
Q: 临时表空间不足类似错误怎么修？
A: 创建新TEMP表空间并设为默认：CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'temp2.dbf' SIZE 1G; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;