ORA-01653 during a 40 GB bulk load with a short window
APP_DATA is a locally managed, ASSM tablespace with a single datafile: 8 GB, AUTOEXTEND ON NEXT 128M MAXSIZE 8G, sitting on a filesystem with 190 MB free. A nightly bulk load inserts 40 GB into a new table, LOADER.STG_EVENTS, and fails after about six minutes with ORA-01653: unable to extend table LOADER.STG_EVENTS by 1024 in tablespace APP_DATA. dba_data_files shows bytes 8G and maxbytes 8G; dba_free_space shows nothing free in APP_DATA. A second mount holds tablespace IDX_DATA with 300 GB free. The job currently restarts from row zero every night. The batch window is four hours. The database is in ARCHIVELOG mode.
1. Explain the failure from the evidence, and say what would have happened if MAXSIZE had been set to 40G.
Read the worked answer
AUTOEXTEND tried to allocate the next 128 MB and could not: the datafile is already at its maxbytes ceiling of 8 GB. That is exactly the condition that raises ORA-01653 on the segment - the tablespace has no space it can hand out, and the fact that dba_free_space is empty confirms the extent request had nowhere to go.
Raising MAXSIZE alone would have moved the failure down a layer, not removed it: the filesystem has only 190 MB free, so the extension would have failed at the OS level while writing a datafile block. That surfaces as an I/O error (ORA-01114 / ORA-27063 class), which is worse than a clean space error - it can also take out anything else writing to that mount, including archived redo, and can leave the instance unable to archive.
So the correct diagnosis is two-part: datafile at MAXSIZE and the underlying mount effectively full. Any plan that only touches MAXSIZE without a mount with capacity is wrong.
2. Design the storage change that lets 40 GB land inside the window, and justify each choice.
Read the worked answer
Options, in order of preference given the evidence:
- Add a datafile for APP_DATA on the 300 GB mount:
ALTER TABLESPACE app_data ADD DATAFILE '/big/app_data_02.dbf' SIZE 48G AUTOEXTEND ON NEXT 512M MAXSIZE 60G;A tablespace can span filesystems, so the table keeps its name, indexes, and privileges, and no application change is needed. Size it for the whole load plus index and future growth, not incrementally - repeated 128 MB extensions cause file-header contention and slow the load. - Alternatively create a new BIGFILE tablespace on the large mount and create the staging table there, then move or exchange. Cleaner isolation, but more moving parts during a window.
Also fix the load mechanics: enable resumable before the statement so a transient space error suspends and resumes rather than rolling back 40 GB of work:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 10800 NAME 'STG_EVENTS_LOAD';
and add an AFTER SUSPEND trigger to alert. Use INSERT /*+ APPEND */ direct-path with NOLOGGING where acceptable - it bypasses undo for the target and cuts redo, but note NOLOGGING operations are not recoverable until a backup is taken, so schedule one.
3. The window is four hours and the load currently restarts from scratch. What changes make the job restartable?
Read the worked answer
Make progress durable and identifiable, so a failure costs minutes, not hours:
- Load into daily/range partitions of STG_EVENTS and drop the incomplete partition on failure instead of deleting rows (partition drop is DDL and near-instant; DELETE of 40 GB would be worse than the original failure).
- Record a high-water mark: every N million rows, commit and write the last loaded key to a control table; restart reads from that key. This is only safe if the load is restartable by key and the control table commit is atomic with the data commit.
- Resumable covers the space-error case specifically and avoids the rollback of the entire statement; partition-dropping covers everything else (session killed, timeout, bad row).
- Watch the window arithmetic: a 40 GB direct-path load plus index maintenance is not instant, so run once with instrumentation and confirm the four hours hold with headroom; if not, parallelize the load (
ALTER SESSION ENABLE PARALLEL DMLand a parallel hint) rather than hoping.
Finally, after the load completes, take a backup if anything was loaded NOLOGGING.