ORA-01653 in a uniform-extent tablespace with 2.4 GB "free"

The tablespace is locally managed with UNIFORM SIZE 64M, so every new extent must be a single contiguous 64 MB chunk of free space. The 8192 bytes in the message is only the block allocation that triggered the recursive extent request; the extent request is what failed. Free space exists, but it is fragmented into 41 pieces whose largest piece is 60 MB - smaller than the required 64 MB - so there is no candidate extent. Autoextend is irrelevant because the datafile has already reached MAXSIZE 120G.

Proof queries:

SELECT tablespace_name, extent_management, allocation_type
FROM   dba_tablespaces WHERE tablespace_name = 'RPT_DATA';
-- LOCAL / UNIFORM

SELECT file_id, bytes/1024/1024 mb, maxbytes/1024/1024 max_mb, autoextensible
FROM   dba_data_files WHERE tablespace_name = 'RPT_DATA';
-- mb = max_mb, autoextensible = YES  -> cannot grow

SELECT bytes/1024/1024 mb
FROM   dba_free_space
WHERE  tablespace_name = 'RPT_DATA'
ORDER  BY 1 DESC;
-- 41 rows, largest 60

The decisive comparison is largest free extent versus uniform size. If the largest extent had been 64 MB or more, the same insert would have succeeded. Note that ASSM does not help here: ASSM manages free space inside segments, not extent allocation.

Tonight: add a new 4 GB datafile to RPT_DATA with autoextend off, e.g. ALTER TABLESPACE rpt_data ADD DATAFILE '/u03/oradata/FINAPP/rpt_data_02.dbf' SIZE 4G;. A new file contributes fresh, clean 64 MB extents immediately, so the 8-million-row load (roughly 1 GB of table plus its indexes) fits inside the remaining 3 GB of mount space. Size for headroom, not exactly for tonight's rows.

Permanent: you cannot change the allocation type of an existing locally managed tablespace, and DEFAULT STORAGE is ignored for LMTs, so this fragmentation is structural. Create RPT_DATA2 with EXTENT MANAGEMENT LOCAL AUTOALLOCATE on a different mount, then ALTER TABLE finapp.wk_report_line MOVE ONLINE TABLESPACE rpt_data2;, rebuild the indexes into an index tablespace, gather statistics and drop the old object.

Rejected options: ALTER TABLESPACE ... COALESCE only applies to dictionary-managed tablespaces; SHRINK SPACE reclaims only free space inside a segment's own high-water mark, which is not where the 2.4 GB sits; adding a file with a smaller NEXT does nothing because the uniform size governs every allocation.

ALTER TABLE ... MOVE ONLINE keeps DML flowing, but it is not free:

• Space: the old and new segments coexist during the move, so you need roughly the table's size again plus room for index rebuilds. That is exactly why the target tablespace must be on other storage, not the 3 GB mount.
• Indexes: the move assigns new ROWIDs, so every index on the table becomes UNUSABLE and must be rebuilt - time-consuming and redo-heavy, and queries using those indexes fail until it is done.
• Statistics: existing statistics and histograms are invalidated. Gather them with the same options immediately after the rebuild, otherwise plans for the load and the reports regress.
• Dependencies: check DBA_DEPENDENCIES and application code for anything storing ROWIDs or relying on the old object identity; materialized view logs and prebuilt MVs on the table need attention.

Schedule the move in a quiet period even though it is ONLINE, and verify with DBA_TABLES.STATUS and DBA_INDEXES.STATUS that nothing is left UNUSABLE.